Delphi 2009 Unicode problem

[trob]

Hello,

I have a program in Delphi 2009, the datas are in MySQL by Zeos7. (Yes, i know it is in alpha state.)

In D2007 everything was fine, but in D2009 there is a problem with character coding. I get the datas from MySQL by TZReadOnlyQuery, and write them by TZQuery's SQL commands. The loading is fine, but the writing is not. There are the double Unicode characters in the SQL command (as i see in the log file of MySQL), but somehow the MySQL take the two characters into Unicode once again, so in the DB there are four characters per every originally typed hungarian accentuated letter, double "unicoded".

I think there is a simple setting somewhere, or i should write something in the SQL command, but i don't know what is the solution...
All of the fields, tables and the DB are set to UTF8.

[mdaems]

I think there is a simple setting somewhere, or i should write something in the SQL command, but i don't know what is the solution...

trob,

I'm afraid it isn't that simple. This issue needs some heavy work by a D2009 debugger. And i'm not a candidate as I'm not (and not planning to be) a D2009 user. This is why I'm afraid the D2009 part will never become Beta.

Mark

[trob]

Oh, why don't you plan to be a D2009 user? What is wrong with it?

[law]

Try this simple code in D2009:

Code: Select all

var s:string;
    p:PAnsiChar;
begin
  s:='ŐőŰű';
  p:=PAnsiChar(UTF8String(s));
  showmessage(p);
end;

IMO the problem is that Zeos typecasts string type (multibyte UnicodeString in D2009) to PAnsiChar (byte-sized type) for calling the mysql API. For example:

Code: Select all

function TZMySQLEmulatedPreparedStatement.GetEscapeString(const Value: string): string;
var
  BufferLen: Integer;
  Buffer: PAnsiChar;
begin
  BufferLen := Length(Value) * 2 + 1;
  GetMem(Buffer, BufferLen);
  if FHandle = nil then
  {$IFDEF DELPHI12_UP}
    BufferLen := FPlainDriver.GetEscapeString(Buffer, PAnsiChar(UTF8String(Value)), Length(Value))
  else
    BufferLen := FPlainDriver.GetRealEscapeString(FHandle, Buffer, PAnsiChar(UTF8String(Value)), Length(Value));   
  {$ELSE}

I installed D2009 today, so I havn't got enough knowledge to solve this. Maybe Marco Cantu's Delphi 2009 Handbook can help.
Maybe I'm wrong and the problem is elsewhere, because I cannot use Zeos7 with D2009 when mysql character set is Latin1, or UTF8.

[skydvrz]

in D2009 there is a problem with character coding.

I had similar problems with porting my application from D2007 to D2009 using ZeosLib 7.0 Alpha.

My old D2007 code used field definitions something like this:

Code: Select all

        quEventAgencies:                         TZQuery;
        quEventAgenciesagency:                   TStringField;
        quEventAgenciesaddr1:                    TStringField;
        quEventAgenciesaddr2:                    TStringField;
        quEventAgenciescity:                     TStringField;
        quEventAgenciesstate:                    TStringField;
        quEventAgencieszip:                      TStringField;

I had to do a search and replace of all my query string fields and replace them with TWideStringField fields. Now everything works fine.

If you are careful, you can open your datamodule DFM in the IDE and replace all the string query fields with TWideStringField. Make a backup first - this is pretty dangerous to your project's health.

Do the same thing in the PAS file.

Recompile and it should stop corrupting the SQL strings and data.

Doing it with the editor is much easier than hand tweaking zillions of fields with the field editor!

If you are not using explicit fields (using the component field editor) then you don't have to switch to widestring, since there isn't anything to fix :-)

HTH!

Kevin G. McCoy

[trob]

Thanks, but i have done this already. I had to, because the program did not get anything from the DB. So now all querys that read datas from the DB work fine, just the writing have this coding problem.

[trob]

I get the solution from the MySQL help...

After connecting:

Code: Select all

ZQueryExec.SQL.Text:='SET character_set_client = utf8';
ZQueryExec.ExecSQL;
ZQueryExec.SQL.Text:='SET character_set_connection = utf8';
ZQueryExec.ExecSQL;
ZQueryExec.SQL.Text:='SET character_set_results = Latin2';
ZQueryExec.ExecSQL;

And everyting works fine. Until i don't need to read from DB other characters like Latin2. But the writing and storing is in UTF8, like it should be.

[icyman]

Dear trob..

In my test.. There is no more need any action to set code page, if you set in TZConnection properties like as TZConnection.Properties.Add('codepage=euckr');..

As I tested with TZSQLMonitor Componet in Zeos, purely there is some problem in Zeos lib.

In SQLTrace log file, when try to update the field, Zeos execute SQL sentence as above.

2009-06-29 17:36:18 cat: Execute, proto: mysql-5, msg: UPDATE CodeTest3.OrderItem SET OrderItem= SPECIAL_CHARACTER WHERE SN=1

In Zeos 6.6.3 D2007 case, readable SPECIAL_CHARACTER is posted,

but, in Zeos7 D2009 case, SPECIAL_CHARACTER is corrupted.

I think it may have some problem in conversion from general string to WideString.