Is TSQLQuery.AppleUpdate works with UTF8 string field content?

I fetched a web page and extracted part of its content and appended it into TSQLQuery record's string field.

The web page content is Chinese in UTF-8 encoding. I 'writeln' the content before appending it and it appeared normal in the console.

The database is MYSQL 5.5 on ubuntu Intel 64-bit.

However, when I use MYSQL command to

    select * from MYTABLE;

the content appeared as garbage.

I already specified the CHARSET=utf8 in the Create Table SQL.

If I directly issue INSERT statement via MYSQL command prompt, it worked without garbage.

CREATE TABLE IF NOT EXISTS `PROVIDERS` (
`Key` int(10) unsigned NOT NULL auto_increment,
`CName` varchar(50) default '',
`Name` varchar(50) default '',

`Tel` varchar(50) default '',
`Address` varchar(200) default '',
`WebSite` varchar(80) default '',
`Email` varchar(50) default '',

`ListKey` int(10) signed default 0,
`PageNo` int(10) signed default 0,

PRIMARY KEY (`Key`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

I suspect the problem is in TSQLQuery's generating the Insert statement which did not take the string as UTF-8.

How do I confirm this suspicion? and how to solve it?

Thanks in advance.

Dennis

Did you set the TSQLConnection.Charset property to UTF8 as well ?

Michael.

I set it to utf8 (UTF-8 will raise exception) but still appears as garbage.

I wrote a test UI program use TDBGrid to edit the text with chinese and then refresh it.
The chinese text, after refreshing, appears as ???

Dennis

I even tried TSQLScript but it also failed and the chinese values appear as ??? in the updated record.
I suspect that TSQLConnection (or only when it works with MYSQL) failed to handle multi-byte charset.
I tried to step into the ApplyUpdates but the debugger did not go into the procedure. Don't know why.

Dennis

Dennis Poon wrote / napísal(a):

So when you first time read data from MySQL then they are displayed correctly.
And only after editing, Posting, ApplyingUpdates and reading back are character data displayed incorrectly ?

MySQL Connection only takes string data as are stored in TField (or TParam) and forwards them to underlying mysql client library. Can you prepare any simple test application (with CREATE TABLE, fill data etc.) which will show problem ?

-Laco.

Dennis Poon wrote / napísal(a):

... And
Can you please after connection is established check values of these system variables:
character_set_client , character_set_connection, character_set_database by
select @@character_set_client , @@character_set_connection, @@character_set_database;
-LACO.

Thanks in advance for your help. Attached is the zipped project files with the Create Table, Insert SQL statements.

The table PROVIDERS1 has 1 row with the 'name' field containing chinese characters in utf8.
It can display Chinese characters normally as seen in this screen print. before update
When clicked the button "Test Edit", the program will

procedure TForm1.BtnTestEditClick(Sender: TObject);
var s, s2, s3 : String;
begin
   with   SQLQuery1 do begin
      if not active then
        open;
      First;
      s := FieldbyName('Name').AsString;
      Edit;
      s2 := s + '.';
      FieldbyName('Name').AsString := s2;
      Post;
      ApplyUpdates;
      Close;
      Open;
      S3 := FieldbyName('Name').AsString;
      Memo1.lines.add('Expected='+S2);
      Memo1.lines.add('After update='+S3);
   End;
end;

In the memo1, it displays the expected value and the actual value after updated. Pls see the attached screen print.

test utf8

I tried, the results are:

@@character_set_client : utf8
@@character_set_connection : latin1
@@character_set_database : latin1

Dennis

Dennis Poon wrote / napísal(a):

Dennis, do you received my reply?:

IMO @@character_set_connection is wrong and must be utf8. I will try fix it, but first can you try as workaround this:
- after connection is established can you use
MySQLConnection1.ExecuteDirect('SET NAMES ''utf8''');
- and then check again @@character_set_client,
@@character_set_connection (IMO both must be utf8 ... if they will be, then your program should work as expected ... can you test it again?)

-Laco.

I tried MYSQLConnection1.ExecuteDirect('SET NAMES ''utf8'';'); and recheck
@@character_set_client : utf8
@@character_set_connection : utf8
@@character_set_database : latin1

So, it corrected one variable.

I re-ran my test but still ?????? after applyUpdates.

Dennis

Dennis Poon wrote / napísal(a):

This is okay now

I re-ran my test but still ?????? after applyUpdates.

When I use your test program I receve same result as you describe.
But when I add into procedure TForm1.FormCreate(Sender: TObject);
begin
  MySQL55Connection1.ExecuteDirect('SET NAMES ''utf8''');  // <-- ADDED
  SQLQuery1.Open;
end;

It works for me as expected ... so now I am confused with your results ... can you try again please your test application with added line ... (it will require patch for mysqlconnection in all cases, but before want have confirmed, that it helps)

-Laco.

Laco,

My apology. My last attempt did not recreate a fresh database table for testing so it started with the ruined table values and thus did not give the correct result.
Your Set Names command did the trick.
Thanks a lot.

By the way, why isn't a semicolon required at the end of SET NAMES 'utf8' command? I tried both with and without semicolon and neither raise an error.

Dennis

Dennis Poon wrote / napísal(a):

no problem :-)

I am happy, that mystery is solved and it works now. I will patch it in mysqlconnection so extra SET NAMES will not be required in the future.

By the way, why isn't a semicolon required at the end of SET NAMES 'utf8' command? I tried both with and without semicolon and neither raise an error.

Seems, that MySQL is tolerant to this.
MySQL supports MULTI_STATEMENTS, which enables client to pass multiple statements separated by ";" in one string.
So may be, that MySQL interprets ";" as delimiter between multiple statements on one batch.
If you use only one sql statement it is always better do not use terminating ";" IMHO

-Laco.

The strange thing is, when we use MYSQL's interactive command line client, a semi colon is always required for EACH SQL Statement.

Dennis

I don't know MySQL, but e.g. with Oracle and its SQL*Plus CLI this is necessary so that the CLI knows that input of the SQL command is finished on that line (rather than continuing on the following one). This is not necessary when calling the API, because you cannot pass unfinished commands there.

Tomas

That is because the mysql client program needs to know when to send the command.

The server does not need the semicolon, the client does. As soon as it encounters the semicolon, it will send all you have entered to the server.

Michael.