[patch_done] Delphi 2010 + MySQL + UTF8

[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

[mdaems]

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

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.

[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.

[mdaems]

Done. SVN rev 850.

Mark

[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

[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]

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]

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]

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]

I have already tried codepage=utf8. still not working. Many chinese characters turn into '?'.

[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]

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.

[EgonHugeist]

Hey kelvinyip look at http://zeos.firmos.at/viewtopic.php?p=13640#13640

[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]

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

thanks in advance..