ZQuery, MySQL, Lazarus, Windows and UTF-8.

[Ionic]

Hello all,

I was writing an application recently, using Lazarus and ZeosLib for connecting to a MySQL database. During this, I have been fetching some entries via a ZQuery component and writing them to a ListBox. it worked all fine and I was quite statisfied with the result.

I have to admit, though, that my favorite and only working environment is GNU/Linux, featuring x86 CPUs. This application, however, has to run on both Windows and Linux x86.

So I fired up my Windows VM, updated Lazarus to the latest version, loaded my Project, started it and was shocked. Instead of outputting all elements from the Database to the ListBox element, sometimes there were completely blank entries. Now look at this, though:

Code: Select all

Column[i] := ZQuery.FieldByName ('Bez. Inv. Nr. ' + IntToStr (To_Number)).AsString + ' (' + ZQuery.FieldByName ('Kurzf. Inv. Nr. ' + IntToStr (To_Number)).AsString + ')';

Can this code provide completely blank rows? Of course not, they should at least contain " ()".
Thus I thought it might be a good idea to use

Code: Select all

ShowMessage ('Bez. Inv. Nr. ' + IntToStr (To_Number) + '=''' + ZQuery.FieldByName ('Bez. Inv. Nr. ' + IntToStr (To_Number)).AsString + '''.');

to have a look at the entries as they are to be inserted in the ListBox, but without any luck. Still, some entries still were completely blank, not even "Bez. Inv. Nr. X" showed up in the Message Dialog.

Frustrated, I had a look at the output of both the Linux and Windows application and tried to figure out, which lines actually differed. As you might have seen on the info box on the left of this text, I am from Germany. The German language is insofar different from English, that it includes some characters which are not part of the Standard ASCII 7 bit charset, like "ä", "ö", "ü" and their uppercase complements, as well as "ß". Whilst ASCII 7 bit chars require only 1 Byte in UTF-8 as well, those "special chars" are composed out of two bytes - and this might become a problem.

In fact, the only difference between the "blank strings" and the other ones were, that they included those special chars. I was not yet able to ShowMessage () those strings yet, but I got an idea: maybe DebugLN () would work? This function does "only" output strings to the console, but it fair enough to give it a try. As Vincent S. also revealed later, DebugLN () does only accept ANSI strings for input, but not UTF-8 encoded strings. Thus, I gave it a try, and really, all those missing/blank strings were eventually popping up in the console.

Thus I searched in the Lazarus Wiki for some information about using UTF-8, especially converting ASCII to UTF-8, and finally found a working function: AnsiToUTF8 (). Now, after replacing all occurrences of ZQuery.FieldByName (...) with AnsiToUTF8 (ZQuery.FieldByName (...)), all problems were finally gone.

So, I want to advise all Lazarus and Windows users to always use AnsiToUTF8.

Also, I do think that this is a bug in ZeosDBO itself which should be fixed anytime soon, at least for the next release.

Best regards,


Mihai

[mdaems]

Thanks for the information, Mihai.

I agree this is a known problem in Zeoslib 6.6. This problems is handled by the Heidisql team already (mysql only, but doing the other drivers in a similar way should be straightforward). They patched the zeoslib code in their svn tree already. The problem, however, is that I don't know enough in this area to do the changes myself. (Dutch is an easy language )

So, would you be a candidate to do the effort?

Mark