Nonpharma diacritics on Sqlite database are broken

[zdavatz]

Report by https://github.com/hx-paco

Nonpharma_db SQLite database is configured for UTF8, but the files downloaded to populate the database are using ANSI which makes diacritics looks like '�'.

For example:

BIOLIGO Selenium L�s pr�paration comptoir 250 ml

Maybe it's necessary to convert the description string to utf-8 before filling the database? Using something like this maybe? (sorry my c++ is very rusty :P )

#include <string>
#include <codecvt>

std::string to_utf8(std::string str, std::locale loc = std::locale{}) {
    using wcvt = std::wstring_convert<std::codecvt_utf8<char32_t>, char32_t>;
    std::u32string wstr(str.size(), U'\0');
    std::use_facet<std::ctype<char32_t>>(loc).widen(str.data(), str.data() + str.size(), &wstr[0]);
    return wcvt{}.to_bytes(wstr.data(),wstr.data() + wstr.size());
}

Vielen Dank & Gruss
See: zdavatz/oddb2xml#100

[zdavatz]

Also bei mir sehen die Umlaute gut aus:

[hx-paco]

Thanks for moving the issue, and sorry for the inconveniences!

Yes, for me also the file is correct and diacritics are showing perfectly fine. But nonpharma_db, the sqlite database created in src/nonpharma/transfer.cpp, somehow inserts the data breaking the diacritics (possible character encoding scheme conflict).

Also, when I download the db from http://pillbox.oddb.org/nonpharma.db.zip I see the trade_item_description_de column with diacritics broken. Maybe we are reading the database in the wrong way?

-- Update --

This is interesting, I have just queried the database searching for 'ü' and it found some coincidences (some diacritics are correct...),

But if you search for '�' you will find the 'umlaut' issue:

select * from nonpharma_db where trade_item_description_de like '%�%';

Vielen Dank & Gruss

[zdavatz]

Auf welchem OS arbeitest Du, welche Version? Wie ist local Dein encoding eingestellt? Ich habe das Gefühl, dass bei Dir lokal da Encoding nicht UTF-8 ist.

Bei mir kriege ich:

[hx-paco]

Your feeling is probably right :).

But still I even configure DataGrip to use UTF-8 and the issue persists... it's weird only some of the umlaut are broken...

[zdavatz]

switch your windows to UTF-8.

[hx-paco]

Done, but sadly it didn't work either:

Isn't it weird that only some of the diacritics are broken? :\

[zdavatz]

1. From when is your Database?
2. Did you try on a different System?
3. Did you reboot your System?
4. Can you test in a Virtual Environment on your System, like Setup up a Linux VM?

[hx-paco]

1 - Last version of http://pillbox.oddb.org/nonpharma.db.zip (28-Jan-2025 05:01)

2, 4 - Good idea, I am going to try on wsl on this machine and I will try on a vm on a Linux I am going to build for this test.

3 - yes, I rebooted the system after changing the system encoding.

I'll keep this issue updated while I test it on Linux.

Thanks for your help!

[hx-paco]

Confirmed also on a debian also configured with UTF-8. It's interesting that this is happening only in some cases while diacritics are still correct in others which probably means that the issue is in some specific process for this specific cases, isn't it?

I was paranoid about the tool (maybe something was wrong about the configuration) but nope... also confirmed using the sqlite CLI:

[zdavatz]

1. download the latest DB. http://pillbox.oddb.org/nonpharma.db.zip
2. Also please share some GTIN, where you think it is wrong.

[hx-paco]

Thanks for your support, an example is 7611333012344

select * from nonpharma_db
where gtin = '7611333012344'

[zdavatz]

What happens if you set:

LANGUAGE=de_CH.UTF-8
LANG=de_CH.UTF-8

[zdavatz]

you are also unzipping the wrong file. Delete all nonpharma files, download again and try again. You did not unzip zip.1.

[hx-paco]

Good catch! You are right, deleted all of them and repeated the process, but same issue persists:

I am going to try to change locale to 'de_CH' now:

Just my luck :( , it didn't work either. Maybe these medicaments are coming from a different source, which is already "broken"?

Reviewing the nonpharma.xlsx file generated during the build, I found an example of another diacritic broken, just in case it helps in the finding of this issue:

GTIN des Artikels	GLN	Zielmarkt	GPC Brick	Artikelbezeichnung / Sprache - de
07611819718708	0705038000007	756	10005844	Stössel für USS-SmallStature/Pädiatrie-Stäbe Ø 5.0 mm

select *
from nonpharma_db
where gtin in ('7611333012344', '07611819718708')