XE3 mysql utf8 problem

[chuchusteve]

Hi,

I just upgraded from Delphi7 to XE3. I was having trouble in doing insert/update SQL to my mysql server.

In delphi7, if I want to save a utf8 field in mysql DB I will do this :

ws : widestring;
sql : string;

ws := '環保及資源管'; // which was some Chinese character
sql := 'insert into DB_123 values (''' + utf8encode(ws) + ''')';
.....
Then do a mysqlexec (sql)...... Everything was fine in Delphi7, the value in the widestring was correctly stored in UTF8 in mysql database server.

The problem come when I moved to XE3, where string is now unicodestring in UTF16, so the same code will be :

sql : string;
sql := 'insert into DB_123 values (''環保及資源管'')';
then do a Zconnection1.ExecuteDirect (sql);

The call was successful, but the piece of data was stored as ???? in the mysql database.

I have set the following in Zconnection1 :
Zconnection1.controlcodepage := cCP_utf16;
zconnection1.clientcodepage := utf8;

Still I got ??? in my DB server.

So what's wrong ? Please help !

[chuchusteve]

Forgot to mention that my Zeos version was 7.0.3 stable

[chuchusteve]

I finally get the answer !

Reason is that mysql use Latin1 to store data character_set_database = latin1

So in Delphi7 :
ws := '環保及資源管'; // which was some Chinese character
sql := 'insert into DB_123 values (''' + utf8encode(ws) + ''')';

the widestring will first encoded as utf8 and send via the mysql driver to the server. As the server need to store data in Latin1 format, so it will double encode the insert sql using Latin1 charset.

Here is another example :

"學生團契" will converted as E5ADB8E7949FE59C98E5A591 in utf8
but Mysql Latin-1 store it as C3A5C2ADC2B8C3A7E2809DC5B8C3A5C593CB9CC3A5C2A5E28098

I can confirm that using :

select hex(fieldname) from table;

will show an encoded string much longer than the original utf8 encoded string.

However, when I use XE3 + Zeos7.0.3 everything will be in utf8, so I need also to change the mysql's my.cnf to utf8. Below is my my.cnf :

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
default-character-set=utf8
init_connect='SET NAMES utf8'


[client]
default-character-set=utf8

After that everything work like charm !

Below is a link that explains the whole situation that I think others may found it useful :

http://mysql.rjweb.org/doc.php/charcoll ... _addressed

[EgonHugeist]

chuchusteve,

This is a solution which is NOT stable in all cases.

The issues you're talking about are because of the compiler uprade you did.

Since Delphi2009 the String is type UnicodeString.
This line is the trouble maker:
sql := 'insert into DB_123 values (''' + utf8encode(ws) + ''')'; //<<- Remove the UTF8Encode() part
For D7-D2007 you wont have problems. After D2009 this is an implizit String-Type-Cast and the 'WS' contained String will be converted twice. I propose you should check compiler warnings? Just use some compiler directives if you want to keep backward compatibility..