Recent

Author Topic: [SOLVED] No UTF8 characters after ApplyUpdates  (Read 2248 times)

RRybak

  • Newbie
  • Posts: 2
[SOLVED] No UTF8 characters after ApplyUpdates
« on: November 05, 2014, 11:18:23 am »
Hello,

Maybe it's stupid, but I'm fighting a problem a second day now. Short story:
There is a cp1250 project and database. The project itself cannot handle UTF (VCL controls do not support special characters), so I need to create a "convert/translate" table. This table looks like this:
Code: [Select]
CREATE TABLE `utftranslate` (
  `XKEY` INT(11) NOT NULL auto_increment,
  `XTSTAMP` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ORGTEXT` varchar(200) DEFAULT NULL,
  `NEWTEXT` varchar(200) DEFAULT NULL,
  PRIMARY KEY  (`XKEY`),
  KEY `i1` (`ORGTEXT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

If I insert anything UTF8 from inside a MySQL Workbench it works. My program can find "wrong typed" word, find it's UTF8 alternative, and pass it to printed reports.  For example old budvardžiu to proper būdvardžių  (notice the accents).
Now I need a program to make such "dictionary". I've found Lazarus, made a simple project. A grid, MySQL50Connection, SQLQuery, SQLTransaction, DBNavigator, two DBTextEdit's.

MySQL50Connection:  charset set to UTF8
Params to:
Port=3309
CharSet=utf8
ServerCharSet=utf8
Names=utf8

(a bit overkill, but typed every possible solutions found on google)

SQLQuery - no FieldDefs, FieldDefs as Strings, FieldDefs as WideStrings - no difference.

After connecting (button event), additional code is executed:
Code: [Select]
   MySQL50Connection1.ExecuteDirect('SET NAMES "utf8"');
   MySQL50Connection1.ExecuteDirect('SET CHARACTER SET `utf8`');   

Now. On grid and DBEdits both fields:  ORGTEXT and NEWTEXT  are displayed as expeted - in UTF8, with all accents etc.
But AfterPost:
Code: [Select]
procedure TForm1.SQLQuery1AfterPost(DataSet: TDataSet);
begin
  SQLQuery1.ApplyUpdates;
  SQLTransaction1.Commit;
  SQLQuery1.Open;   

In database, on grid, and DB aware controls there are question marks instead of special characters, like: b?dvardži?.

Question marks are visible in app, and in MySQL Workbench. So:
1. if inserting UTF directly - everything is displayed OK (both workbench and lazarus app)
2. Posting (cached post as I assume). On grid and controls - text is displayed OK, but no change in database
3. Executing ApplyUpdated forces write to DB, and special characters are replaced by ? (question marks).

More. I've tried a direct query sequence:
Code: [Select]
     
 MySQL50Connection1.ExecuteDirect('SET NAMES "utf8"');
 MySQL50Connection1.ExecuteDirect('SET CHARACTER SET `utf8`');
 MySQL50Connection1.ExecuteDirect('UPDATE utftranslate SET newtext="būdvardžių99" WHERE xkey=8');
but with no luck - also question marks go to DB

What am I doing wrong or missing?  %)
« Last Edit: November 05, 2014, 03:29:12 pm by RRybak »

RRybak

  • Newbie
  • Posts: 2
Re: No UTF8 characters after ApplyUpdates
« Reply #1 on: November 05, 2014, 03:28:56 pm »
OK, I think I've found a solution, after hitting this thread: http://stackoverflow.com/questions/1566602/is-set-character-set-utf8-necessary

The solution is to reverse the order of initial commands:

First:
Code: [Select]
SET  CHARACTER SET
Then:
Code: [Select]
SET NAMES
I don't know why, but it works. Most of the tutorials give the other order (wrong). Mainly, I don't get it, why setting Charset in LCL Control is not sufficient. But it works the other way, so I'm leaving it here for future curio and reference.

LacaK

  • Hero Member
  • *****
  • Posts: 577
Re: No UTF8 characters after ApplyUpdates
« Reply #2 on: December 26, 2014, 09:54:20 am »
Mainly, I don't get it, why setting Charset in LCL Control is not sufficient.
There was applied patch for MySQL - 9 months ago, so in new release of FPC there will be sufficient to set CharSet property on MySQLConnection component.