Unicode text (memo) field

GuzunNicolae · Post by **GuzunNicolae** » Wed 17 Jan 2007 15:03

Hello

I want to ask how to store a text (memo) field in Unicode?

If I define a varchar(255) field it works with Unicode chars very well, but if I declare that field as text and use a TntDBMemo to display it, I get ????? instead of Unicode data.

Now I am doing this manually by UTF8Decode and UTF8Encode, but I wanted an automatic approach.

Can you please help me with this? Is there a bug? I doubt no one noticed it.

Thanks a lot

Antaeus · Post by **Antaeus** » Thu 18 Jan 2007 10:37

This is a known problem. The reason of this problem is that Delphi versions prior to 2006 do not support TWideMemo fields. We plan to add support for this field type to MyDAC in the future.
To work with Unicode BLOBs you should modify a tnt control so that it retrieves data from a field casting it to a TBlob object. You should work with the TBlob object via the AsWideString method. Below is an example that demonstrates such behaviour. This code is posted "As is". We do not guarantee that this code is fully working.

Code: Select all

function GetAsWideString(Field: TField): WideString;
var
  WideField: IWideStringField;
  Blob: TBlob;
begin
  if (Field is TMemoField) and (Field.DataSet is TCustomDADataSet) and not 
Field.IsNull then begin
    Blob := TCustomDADataSet(Field.DataSet).GetBlob(Field.FieldName);
    if Blob.IsUnicode then
      Result := Blob.AsWideString
    else
      Result := Blob.AsString;
  end;

  if Field.GetInterface(IWideStringField, WideField) then
    Result := WideField.AsWideString
  else if (Field is TWideStringField{TNT-ALLOW TWideStringField}) then begin
    if Field.IsNull then
      // This fixes a bug in TWideStringField.GetAsWideString which does not 
handle Null at all.
      Result := ''
    else
      Result := TWideStringField{TNT-ALLOW TWideStringField}(Field).Value
  end else
    Result := Field.AsString{TNT-ALLOW AsString};
end;

Code: Select all

procedure SetAsWideString(Field: TField; const Value: WideString);
var
  WideField: IWideStringField;
  Blob: TBlob;
begin
  if (Field is TMemoField) and (Field.DataSet is TCustomDADataSet) then 
begin
    Blob := TCustomDADataSet(Field.DataSet).GetBlob(Field.FieldName);
    if Blob.IsUnicode then
      Blob.AsWideString := Value
    else
      Blob.AsString := Value;
  end;

  if Field.GetInterface(IWideStringField, WideField) then
    WideField.AsWideString := Value
  else if (Field is TWideStringField{TNT-ALLOW TWideStringField}) then
    TWideStringField{TNT-ALLOW TWideStringField}(Field).Value := Value
  else
    Field.AsString{TNT-ALLOW AsString} := Value;
end;

GuzunNicolae · Post by **GuzunNicolae** » Thu 18 Jan 2007 14:11

Thanks for the reply
I will try the code soon.

As about my other question posted earlier it was not the same. This one is just about MEMO fields while that one is in general about MyDAC behaviour when I set UseUnicode=True.

The question was how MyDAC stores data in MySQL when UseUnicode=True?
This question appeared when I wanted to enter UTF8 data directly into MySQL to be read from Delphi using MyDAC.

Even if I enter some data with UseUnicode=True then I read them with UseUnicode=False I see '????', while I was supposing that I will see the UTF8 data.

Please clarify to me what is going on behind UseUnicode=True.

Antaeus · Post by **Antaeus** » Thu 18 Jan 2007 15:03

If the UseUnicode option is set to True, "SET NAMES utf8" command is executed by the TMyConnection object to make MySQL server work with utf8 encoding within the current session. MyDAC receives string values in the utf8 encoding (1, 2 or 3 bytes per symbol) and converts them into Delphi WideString values (2 bytes per symbol). When sending string values to the server, MyDAC converts them vice versa: from WideString into utf8.
As a rule there should not be any problems working with Unicode strings if the UseUnicode option is True and controls with Unicode support (like tnt) are used.
If you have any questions left, please describe them more detailed.

GuzunNicolae · Post by **GuzunNicolae** » Thu 18 Jan 2007 15:53

About code:

After some time of playing with it I made the program compile. I put this code in TntDB.pas unit. Added some units in uses list DBAccess and MemData, otherwise it wont compile.

But I couldn't make it work.

As I understood here the saving is done to the database

Code: Select all

  if Field.GetInterface(IWideStringField, WideField) then
    WideField.AsWideString := Value
  else if (Field is TWideStringField{TNT-ALLOW TWideStringField}) then
    TWideStringField{TNT-ALLOW TWideStringField}(Field).Value := Value
  else
    Field.AsString{TNT-ALLOW AsString} := Value;

But my field is not as TWideStringField so this code is never executed

Code: Select all

if (Field is TWideStringField{TNT-ALLOW TWideStringField}) then
    TWideStringField{TNT-ALLOW TWideStringField}(Field).Value := Value

instead this one is executed and as a result I loose Unicode

Code: Select all

Field.AsString{TNT-ALLOW AsString} := Value;

Antaeus · Post by **Antaeus** » Fri 19 Jan 2007 11:18

Do you have any problems working with String fields, or the problem is only in Memo fields?

GuzunNicolae · Post by **GuzunNicolae** » Fri 19 Jan 2007 13:43

I have problem working with Memo fields.

Antaeus · Post by **Antaeus** » Fri 19 Jan 2007 15:15

This code is pretty old. It was taken from Tnt 2.1.11. It is possible that now it does not look exactly like the one I have posted. So from this code you should pick out the code to read/write the Memo values from/to the specified field via TBlob. Then integrate this code into GetAsWideString function and SetAsWideString procedure from the TntDB unit.
If you want, we will send you this unit completely.

Bernhard Geyer · Post by **Bernhard Geyer** » Mon 22 Jan 2007 08:28

Perhaps it would work if you apply my fix for adodb.pas (Delphi 6):

DataTypeValues: array[TDataType] of TOleEnum = (
adEmpty, adVarChar, adSmallint, adInteger, adUnsignedSmallint,
adBoolean, adDouble, adDouble, adCurrency, adDate, adDate,
adDate, adBinary, adVarBinary, adInteger, adLongVarBinary,
adLongVarChar, adLongVarBinary, adLongVarBinary, adLongVarBinary,
adLongVarBinary, adLongVarBinary, adEmpty, adChar, adVarWChar, adBigInt,
adEmpty, adEmpty, adEmpty, adEmpty, adEmpty, adEmpty, adVariant,
adIUnknown, adIDispatch, adGuid, adEmpty, adEmpty
);

function FieldTypeToADOType(const FieldType: TFieldType): DataTypeEnum;

ftString: Result := adVarChar;
ftWideString: Result := adVarWChar;

GuzunNicolae · Post by **GuzunNicolae** » Mon 22 Jan 2007 09:50

Antaeus wrote:This code is pretty old. It was taken from Tnt 2.1.11. It is possible that now it does not look exactly like the one I have posted. So from this code you should pick out the code to read/write the Memo values from/to the specified field via TBlob. Then integrate this code into GetAsWideString function and SetAsWideString procedure from the TntDB unit.
If you want, we will send you this unit completely.

Yes, I would like to see a working unit.

Antaeus · Post by **Antaeus** » Mon 22 Jan 2007 13:17

The file has been sent to the address specified in your profile.

GuzunNicolae · Post by **GuzunNicolae** » Mon 22 Jan 2007 15:35

I have not received anything

Antaeus · Post by **Antaeus** » Tue 23 Jan 2007 07:18

Please specify what address should I use to send you the file.

GuzunNicolae · Post by **GuzunNicolae** » Tue 23 Jan 2007 08:33

Sorry, I have received your email but it was put to spam folder.

I replaced my file with yours and it does not work. The same problem as in mine code example.

This is how I do:
I have a TntDBGrid on the form, a TntDBMemo which points to a text field and a DBNavigator. When I write sth in TntDBMemo and press apply I get '???????'

Connection UseUnicode is set to True and the underneath table is UTF8.

Antaeus · Post by **Antaeus** » Tue 23 Jan 2007 10:26

Try to determine where is the reason of the problem. Change the folowing code

Code: Select all

function GetAsWideString(Field: TField): WideString; 
....
    if Blob.IsUnicode then
      Result := Blob.AsWideString

procedure SetAsWideString(Field: TField; const Value: WideString);
....
    if Blob.IsUnicode then
      Blob.AsWideString := Value

so that each processed Unicode BLOB value is stored to file. Something like the following:

Code: Select all

function GetAsWideString(Field: TField): WideString; 
....
    if Blob.IsUnicode then begin
      Result := Blob.AsWideString;
      Blob.SaveToFile('d:\get_blob.txt');
    end

procedure SetAsWideString(Field: TField; const Value: WideString);
....
    if Blob.IsUnicode then begin
      Blob.AsWideString := Value; 
      Blob.SaveToFile('d:\set_blob.txt');
    end

Open an empty DataSet, insert a new record, post and refresh it. Then check contents of both files in a Unicode text viewer.