reading utf8 from database

[weitemeyer]

Hi,

I'm using Delphi 7 on WIn 2k with MySQL 4.1 and MyDAC 4.30.0.11.
In the database I've tables with utf8 character set and russian characters in it.
I want to read and write these texts with my Delphi 7 application. I learned that I have to use TNT-Components and WideStrings.
I also read that I have to set MyConnection.options.useUnicode to true and that I will get my results in TWideStringFields.
So I built up a little test with
- a TNTEdit
- a simple query which should return some kyrillic words
If I'm accessing the field as a simple TStringField I get only `??????`. If I try to access the field as a TWideStringField I only get an empty string.
Any ideas? Thanks in advance

Here's the code

Code: Select all

procedure TMainEditWin.Button3Click(Sender: TObject);
var test:string;
    wtest:widestring;
    query:TStringlist;
begin
  MainForm.MyConnection.options.useUnicode := true;
  query := TStringlist.create;
  query.add( 'SELECT shortDescr FROM Style where id=325108' );
  MyQuery.sql := query;
  MyQuery..open;
  wtest := TWideStringField( MyQuery.Fields[0] ).Value;
  { result: '' }
  wtest := TStringField( MyQuery.Fields[0] ).value;
  { result: '????? ?????' }
  MyQuery.Close;
  edTest.text := wtest;
end;

[Antaeus]

I can only reproduce such behaviour with the disabled UseUnicode option. With UseUnicode = True everything works fine.
It likely that you change UseUnicode for another connection object. Try to replace this line:

Code: Select all

  MainForm.MyConnection.options.useUnicode := true; 

with this one:

Code: Select all

  MyQuery.MyConnection.options.useUnicode := true;

If the problem still persists, send a complete small sample to mydac*crlab*com to demonstrate it. Also include the script to create and fill table.

[weitemeyer]

Thanks for your reply but that was not the solution.
I saw in the meantime that it works with "varchar()" fields but not with "text" fields.
In this case the result is not a "TWideStringField" but a "TMemoField". How do I get a WideString from a TMemoField.
Thanks in advance

[Antaeus]

Try this code:

Code: Select all

uses
   MemData;

...

var
  Blob: TBlob;
  ws: WideString;
begin
  Blob := MyQuery1.GetBlob('fText');
  if Blob.IsUnicode then
    ws := Blob.AsWideString;
...

[weitemeyer]

Thank you very much. It works!

[weitemeyer]

Hi,

reading from the database works pretty good now. But I've problems writing the data back to the database. I'm using a pretty simple wrapper for TMyCommand:

Code: Select all

// Execute for update and delete, returns number of concerned rows
function execute( s:string):integer;
var
  command:TStringlist;
  saveCursor:TCursor;
begin
  result := 0;
  saveCursor := screen.cursor;
  screen.cursor := crHourGlass;
  try
    try
      command := TStringlist.create;
      command.add( s );
      MyCommand.sql := command;
      MyCommand.Execute;
      result := MyCommand.RowsAffected;
      commit;
    except
      on E: Exception do begin
        rollback;
        screen.cursor := saveCursor;
        Fehler(E.Message + ' in Anweisung: ' + s );
      end;
    end;
  finally
    screen.cursor := saveCursor;
  end;
end;

The problem is, that everything in TMyCommand ( and in the wrapper as well ) is based on String, TStrings and TStringList.
Is there any possibility to pass a command as a widestring? And if not what else can I do to execute SQL-commands with unicode chars on the db?
Thanks in advance.

[Antaeus]

You can post Unicode data to database using parameters. It may look like this:

Code: Select all

  MyCommand1.SQL.Text := 'INSERT INTO ATable(IntField, StrField, WStrField) VALUES(1, ''any string value'', :WStrParam)';
  MyCommand1.ParamByName('WStrParam').AsWideString := 'any wide string value';
  MyCommand1.Execute;

[weitemeyer]

Once more thanks a lot!