[patch_done] Delphi 2010 + MySQL + UTF8

The alpha/beta tester's forum for ZeosLib 7.0.x series

Report problems concerning our Delphi 2009+ version and new Zeoslib 7.0 features here.

This is a forum that will be removed once the 7.X version goes into stable!!

Moderators: gto, EgonHugeist, olehs

User avatar
aperger
Expert Boarder
Expert Boarder
Posts: 129
Joined: 24.08.2005, 08:24
Location: Veszprém
Contact:

Post by aperger »

Hi,

I will to take a look into the test site. But I could not give you any deadline. If I could solve any problem I will send you the PATCH and I will post my results here.

Regards,

Attila
User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Post by mdaems »

Found it. I hope you don't mind I post the error here :P

Code: Select all

      if ( // UTF8
        	(PMYSQL_FIELD(FieldHandle)^.charsetnr = 33) or
          (PMYSQL_FIELD(FieldHandle)^.charsetnr = 83) or
        	(PMYSQL_FIELD(FieldHandle)^.charsetnr>=192) or
          (PMYSQL_FIELD(FieldHandle)^.charsetnr<=210) )(*  the end is not fix ??? *) then
        Result := stUnicodeString
      else
      if ( // UCS2
        	(PMYSQL_FIELD(FieldHandle)^.charsetnr = 35) or
          (PMYSQL_FIELD(FieldHandle)^.charsetnr = 90) or
        	(PMYSQL_FIELD(FieldHandle)^.charsetnr>=128) or
          (PMYSQL_FIELD(FieldHandle)^.charsetnr<=146) )(*  the end is not fix ??? *) then
        Result := stUnicodeString
Should probably be

Code: Select all

      if ( // UTF8
        	(PMYSQL_FIELD(FieldHandle)^.charsetnr = 33) or
          (PMYSQL_FIELD(FieldHandle)^.charsetnr = 83) or
        	((PMYSQL_FIELD(FieldHandle)^.charsetnr>=192) and
          (PMYSQL_FIELD(FieldHandle)^.charsetnr<=210)) )(*  the end is not fix ??? *) then
        Result := stUnicodeString
      else
      if ( // UCS2
        	(PMYSQL_FIELD(FieldHandle)^.charsetnr = 35) or
          (PMYSQL_FIELD(FieldHandle)^.charsetnr = 90) or
        	((PMYSQL_FIELD(FieldHandle)^.charsetnr>=128) and
          (PMYSQL_FIELD(FieldHandle)^.charsetnr<=146)) )(*  the end is not fix ??? *) then
        Result := stUnicodeString
At least it solves the test suite errors. Does this change stll solve your problem?
Then I'll commit as soon as you confirm.

Mark

BTW : happy birthday. You're a few months ahead of me. Turning 36 myself in a few months.
Image
User avatar
aperger
Expert Boarder
Expert Boarder
Posts: 129
Joined: 24.08.2005, 08:24
Location: Veszprém
Contact:

Post by aperger »

I don't mimd if you post my erros here... :-) This is the place of our "work"/discussion.

Your fix is correct !!! It was my fault... -->> '(...)' is needed!!! I should commit this changes.

Attila

ps.: Thanks for your congratulation.
User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Post by mdaems »

Done. SVN rev 850.

Mark
Image
Adaptec
Fresh Boarder
Fresh Boarder
Posts: 1
Joined: 08.03.2011, 07:55

Post by Adaptec »

Hi, I found next problem with UTF8 and ftMemo (defined as Text in database).

I must fix code to this:

Code: Select all

  Query.SQL.Text := 'SELECT name, description FROM crm_product WHERE id = "027511D7-AA9F-4DE1-A479-305D3E2B85C9"';
  Query.Open;
  Memo1.Text :=  Query.Fields.Fields[0].AsString; //name is varchar(255)
  Memo1.Text := UTF8Decode( Query.Fields.Fields[1].AsString); //description is text
  Query.Close;
For text field I must use UTF8Decode to get right chars from it. Same result if I use TDBMemo/or TDBEdit for showing data. For fields witch varchar all works ok (in this example "name").


MyConfig: Delphi XE + Latest SVN + MySQL 5.0.x
Connection properties:
codepage=UTF8
client_encoding=UTF8
User avatar
aperger
Expert Boarder
Expert Boarder
Posts: 129
Joined: 24.08.2005, 08:24
Location: Veszprém
Contact:

Post by aperger »

Hi Adaptec,

I think your problem is coming from unit 'ZDbcMySqlUtils', after line 222:

Code: Select all

    FIELD_TYPE_TINY_BLOB, FIELD_TYPE_MEDIUM_BLOB,
    FIELD_TYPE_LONG_BLOB, FIELD_TYPE_BLOB:
      if (FieldFlags and BINARY_FLAG) = 0 then
        Result := stAsciiStream
      else
        Result := stBinaryStream;
The type of the field. The type value may be one of the MYSQL_TYPE_ symbols shown in the following table.
Type Value Type Description
MYSQL_TYPE_TINY TINYINT field
MYSQL_TYPE_SHORT SMALLINT field
MYSQL_TYPE_LONG INTEGER field
MYSQL_TYPE_INT24 MEDIUMINT field
MYSQL_TYPE_LONGLONG BIGINT field
MYSQL_TYPE_DECIMAL DECIMAL or NUMERIC field
MYSQL_TYPE_NEWDECIMAL Precision math DECIMAL or NUMERIC field (MySQL 5.0.3 and up)
MYSQL_TYPE_FLOAT FLOAT field
MYSQL_TYPE_DOUBLE DOUBLE or REAL field
MYSQL_TYPE_BIT BIT field (MySQL 5.0.3 and up)
MYSQL_TYPE_TIMESTAMP TIMESTAMP field
MYSQL_TYPE_DATE DATE field
MYSQL_TYPE_TIME TIME field
MYSQL_TYPE_DATETIME DATETIME field
MYSQL_TYPE_YEAR YEAR field
MYSQL_TYPE_STRING CHAR or BINARY field
MYSQL_TYPE_VAR_STRING VARCHAR or VARBINARY field
MYSQL_TYPE_BLOB BLOB or TEXT field (use max_length to determine the maximum length)
MYSQL_TYPE_SET SET field
MYSQL_TYPE_ENUM ENUM field
MYSQL_TYPE_GEOMETRY Spatial field
MYSQL_TYPE_NULL NULL-type field


... so MYSQL_TYPE_BLOB ->> can be a BLOB or TEXT
This cause the problem. It is not handled in the code.

I did some changes (unit 'ZDbcMySqlUtils', line 235) to show correct values for VARCHAR:

Code: Select all

    FIELD_TYPE_VARCHAR,
    FIELD_TYPE_VAR_STRING,
    FIELD_TYPE_STRING:
    	if (PMYSQL_FIELD(FieldHandle)^.charsetnr = 63) then
      	Result := stString // ?? stBytes // BINARY from CHAR, VARBINARY from VARCHAR, BLOB from TEXT
      else
      if ( // UTF8
        	(PMYSQL_FIELD(FieldHandle)^.charsetnr = 33) or
          (PMYSQL_FIELD(FieldHandle)^.charsetnr = 83) or
        	((PMYSQL_FIELD(FieldHandle)^.charsetnr>=192) and
          (PMYSQL_FIELD(FieldHandle)^.charsetnr<=210)) )(*  the end is not fix ??? *) then
        Result := stUnicodeString
      else
      if ( // UCS2
        	(PMYSQL_FIELD(FieldHandle)^.charsetnr = 35) or
          (PMYSQL_FIELD(FieldHandle)^.charsetnr = 90) or
        	((PMYSQL_FIELD(FieldHandle)^.charsetnr>=128) and
          (PMYSQL_FIELD(FieldHandle)^.charsetnr<=146)) )(*  the end is not fix ??? *) then
        Result := stUnicodeString
      else
        Result := stString;
You should do something similar... Sorry I have nor time to fix and test it.
Guru2D
Fresh Boarder
Fresh Boarder
Posts: 1
Joined: 08.03.2011, 20:55

Post by Guru2D »

Hi again (I'm adaptec, but forum blocked me for SPAM ?!, ...)

My fix for my UTF8 problem is.
In ZDbcMySqlResultSet in GetBlob function (start line 781):

Code: Select all

      Stream := TStringStream.Create( UTF8ToUnicodeString (  GetString(ColumnIndex)) );
      Result := TZAbstractBlob.CreateWithStream(Stream)
and for update fix functions: ExecuteUpdate, Execute, ExecuteQuery in ZDbcMySqlStatement from this:

Code: Select all

  {$IFDEF DELPHI12_UP}
  if FPlainDriver.ExecQuery(FHandle, PAnsiChar(AnsiString(SQL))) = 0 then
  {$ELSE}
to this:

Code: Select all

  {$IFDEF DELPHI12_UP}
  if FPlainDriver.ExecQuery(FHandle, PAnsiChar(UTF8Encode(SQL))) = 0 then
  {$ELSE}
now it's working for me for my setup:
Delphi XE + MySQL 5.0.x with connection properties:
codepage=UTF8
client_encoding=UTF8
kelvinyip
Fresh Boarder
Fresh Boarder
Posts: 9
Joined: 18.03.2011, 14:28

Post by kelvinyip »

I have already download latest version from svn://zeos.firmos.at/zeos/trunk.
Now, I still find zeosdbo seems cannot update Mysql when using some chinese characters when using UTF-8. All the chinese characters can be
read from database.

I am using Delphi 2010 with the following properties:
character_set_client=utf8
character_set_connection=utf8
character_set_database=utf8
character_set_results=utf8
character_set_server=utf8
character_set_system=utf8
collation_connection=utf8_unicode_ci
collation_database=utf8_unicode_ci
collation_server=utf8_unicode_ci
Codepage=utf8
client_encoding=UTF8

Is there anybody have the same problem ?
Lidael
Fresh Boarder
Fresh Boarder
Posts: 6
Joined: 18.03.2011, 16:21

Post by Lidael »

Unless i'm misreading the code it's codepage not Codepage.
And most of the other properties are ignored by Zeos SVN, only compress=true is recognized.
kelvinyip
Fresh Boarder
Fresh Boarder
Posts: 9
Joined: 18.03.2011, 14:28

Post by kelvinyip »

I have already tried codepage=utf8. still not working. Many chinese characters turn into '?'.
kelvinyip
Fresh Boarder
Fresh Boarder
Posts: 9
Joined: 18.03.2011, 14:28

Post by kelvinyip »

Now, I followed adaptec's idea to trace. I find that some chinese characters cannot be decoded successfully.
For example:
Value1:=PAnsiChar(UTF8Encode(Text1));
Value2:=UTF8Decode(Value1);
The resulting Value2 is not equal to Text1.

For example,
The character "我是" is not able to convert successfully.
The character "你是" is able to convert successfully.
kelvinyip
Fresh Boarder
Fresh Boarder
Posts: 9
Joined: 18.03.2011, 14:28

Post by kelvinyip »

I have the following findings now.
The following will result in corrupted string.
PAnsiChar(UTF8Encode('我是'));
However, the result of following is ok.
PAnsiChar(AnsiString('我是'));

I guess PAnsiChar is not capable to store some chars in East Asian Language.

Now I tried to modified the source src/plain/ZPlainMySqlConstants.pas and change the argument of function Tmysql_query (from PAnsiChar to PChar)

I also modified src\dbc\ZDbcMysqlStatement.pas and change all UTF8Encode to AnsiString.

Last, I modified src\plain\ZPlainMysqlDriver.pas and change the ExecQuery function to:

Code: Select all

function TZMySQLBaseDriver.ExecQuery(Handle: PZMySQLConnect;
  const Query: PAnsiChar): Integer;
var Query2: PChar;
begin
  Query2:=PChar(UTF8Encode(Query));
  Result := MYSQL_API.mysql_query(Handle, Query2);
end;
The problem of update chinese seems to be fixed. Sorry, my code is dirty and just want to show my finding for further debug.

Thanks all those contribute to zeosdbo.
User avatar
EgonHugeist
Zeos Project Manager
Zeos Project Manager
Posts: 1936
Joined: 31.03.2011, 22:38

Post by EgonHugeist »

Best regards, Michael

You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image
User avatar
EgonHugeist
Zeos Project Manager
Zeos Project Manager
Posts: 1936
Joined: 31.03.2011, 22:38

A MySQL Delphi12UP-Patch...

Post by EgonHugeist »

http://zeos.firmos.at/viewtopic.php?p=13694#13694

This patch was not Implemented on Rev943. The Results of Chinese & Latin Letters where the same like in older Versions..
only visible for members..
jeremicm
Senior Boarder
Senior Boarder
Posts: 61
Joined: 18.10.2006, 17:07
Contact:

Post by jeremicm »

maybe this is a stupid question, but how to add this patch files to delphi components?

thanks in advance..
Locked