Lazarus + Zeos 7 + MySql
lazarus / 16-Databases

----------------------------------------------------------------------
Blestan · 2010-09-28 08:38

Hi All!
I Have a very odd problem with zeos+mysql+lazarus:
the table in contains correct cyrillic char data ( it's visible in
phpmyadmin, sqlyog etc... even in lazarus db grid after

SQLQuery:=TZReadOnlyQuery.Create(Self);
with SQLQuery do
begin ;
Connection:=TDG_MySQL;
SQL.TEXT := 'SET CHARACTER SET `utf8`';
ExecSQL;
end;
SQLQuery.Free;

in the AfterConnect event of TZConnection )

everithing looks perfect till i try to modify a row ...
the text in the dbigrid is ok - displays the cyrillic chars
but after a post and a refresh this row goes to "????"
and it's ??? in all possible viewers like phpmyadmin and sqlyog...
it's seems like a weird double utf8encoding just before posting real
data to db but i'm not sure....
any ideas where to look and patch zeos code?

----------------------------------------------------------------------
Lacak2 · 2010-09-29 12:10

Hard to say. I do not use Zeos, but looking at sources of Zeos, there
is used "SET CHARACTER SET" in conjuction with "codepage", which can
be set in connection params (search for ClientCodePage). So if you set
in Params:
codepage=UTF8
then Zeos automatically will execute "SET CHARACTER SET UTF8"

But this does not solve your problem, which occurs after inserting,
updating.
May be, that problem is related to binding parameter values of
prepared statements and some implicit conversion is performed
(may be, that ANSI strings are expected and not UTF8 encoded)

----------------------------------------------------------------------
bobo · 2010-09-30 02:00

I  always do a

    ZEOSConnection.Properties.Text :=
      'character_set_client=utf8'#13#10+
      'character_set_connection=utf8'#13#10+
      'character_set_database=utf8'#13#10+
      'character_set_results=utf8'#13#10+
      'character_set_server=utf8'#13#10+
      'character_set_system=utf8'#13#10+
      'collation_connection=utf8_general_ci'#13#10+
      'collation_database=utf8_general_ci'#13#10+
      'collation_server=utf8_general_ci'#13#10+
      'Codepage=utf8';

before opening the first connection. Nothing else is needed and
everything is shown/read/updated properly.

----------------------------------------------------------------------
Blestan · 2010-09-30 14:13

thanks for the advices ... the params did not work ... but i found
what finally worked fine for both read and writes :
executing this in AfterConnect event handler
SQLQuery:=TZReadOnlyQuery.Create(Self);
  with SQLQuery do
   begin ;
     Connection:=Sender As TZConnection;
     SQL.TEXT :=  'SET CHARACTER SET utf8';
     ExecSQL;
     SQL.TEXT :=  'SET NAMES utf8';
     ExecSQL;
     SQL.TEXT :=  'SET character_set_client = utf8';
     ExecSQL;
     SQL.TEXT :=  'SET character_set_results = utf8';
     ExecSQL;
     SQL.TEXT :=  'SET collation_connection = utf8_general_ci';
     ExecSQL;
   end;

 SQLQuery.Free;
end;