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:
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:
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:
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:
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?