Unicode/Utf8 Data in mediumtext rows

Unicode/Utf8 Data in mediumtext rows

Postby StefanLechner » Tue 25 Oct 2005 15:02

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
 
Posts: 2
Joined: Tue 25 Oct 2005 14:07

Postby StefanLechner » Wed 26 Oct 2005 13:09

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.
StefanLechner
 
Posts: 2
Joined: Tue 25 Oct 2005 14:07

Postby Ikar » Mon 31 Oct 2005 15:08

You are right, and you found correct way to avoid the problem.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56


Return to MySQL Data Access Components