Unicode/Utf8 Data in mediumtext rows

[StefanLechner]

Hi all,
I tried to store unicode data in a MySql Database. As long as I'm using varchar columns the unicode support works fine.
But when I try to store unicode data in a mediumtext columns the content is garbled.

Code: Select all

  qryUpdateText.SQL.Add('UPDATE');
  qryUpdateText.SQL.Add('  ST_SYSTEXTE');
  qryUpdateText.SQL.Add('SET');
  qryUpdateText.SQL.Add('  ST_TEXT = :ST_TEXT');
  qryUpdateText.SQL.Add('WHERE');
  qryUpdateText.SQL.Add('  ST_SPR = :ST_SPR AND' );
  qryUpdateText.SQL.Add('  SF_ID  = :SF_ID');

  qryUpdateText.ParamByName('SF_ID').AsInteger      := iSfId;
  qryUpdateText.ParamByName('ST_SPR').AsString      := sSpr;
  qryUpdateText.ParamByName('ST_TEXT').DataType     := ftWideString;
  qryUpdateText.ParamByName('ST_TEXT').AsWideString := sText;
  try
    qryUpdateText.Execute;

MySql Version 4.1.14
Delphi 7
MyDac 4.00.1.5 (trial)

Table definition

Code: Select all

CREATE TABLE `st_systexte` (
  `SF_ID` decimal(10,0) NOT NULL default '0',
  `ST_SPR` decimal(10,0) NOT NULL default '0',
  `ST_TEXT` text,
  UNIQUE KEY `IDXU_STPRIM` (`SF_ID`,`ST_SPR`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

I tested with text and mediumtext as well as several ways to store the unicode data (value, SetBlobData, asVariant)

useUnicode on Connection is set to True.

EDIT:

I found that the text is saved correctly, but I can't read it as WideString from the database.

MySqlAdmin export:

Code: Select all

INSERT INTO `st_systexte` (`SF_ID`,`ST_SPR`,`ST_TEXT`) VALUES 
 ('4','1','vom %V_ABSCHLUSS_DATUM%'),
 ('4','2','δφασδφα\r\n\r\nαδσφασδφ');

Any hint is appreciated

[StefanLechner]

Since Borland's TMemoField in Delphi 7 does not support unicode at all, I found no way to get the unicode text through Query.FieldByName or Query.Fields[x].

The type mapping for Oracle CLOB/NCLOB and MySql TEXT/MDIUMTEXT... is TMemoField.

The only way I found to read WideStrings correctly form the database is

Code: Select all

:
if Query.GetBlob('ST_TEXT').IsUnicode then begin 
  sText :=  Query.GetBlob('ST_TEXT').AsWideString;
end;

This works with ODAC as well as with MyDAC.

[Ikar]

You are right, and you found correct way to avoid the problem.