how to make SQL Text as Unicode String

[maxchenjun]

I hava a utf-8 table. And it works with read and write.
Buf if i want to search a record with utf-8 character. It seems not work.
I used:

Code: Select all

var q: WideString;
q:= 'contains some utf-8 character;
myTable.SQL.Text:=  'select * from table where keyword="'+q+'";';

I cannot get the correct record... Any ideas? Thanks in advance.

I used
MyDAC 5.20 trial version
Delphhi 7
Mysql 5.0

[GuzunNicolae]

Very easy. You just have to use parameters.
So your above code should be written as follows:

Code: Select all

var q: WideString;
q:= 'contains some utf-8 character;
myTable.SQL.Text:=  'select * from table where keyword=:key';
myTable.ParamByName('key').AsWideString := q;

But the q should not be UTF8. It should be WideString (q := UTF8Decode(q)) and make sure your Connection UseUnicode property is set to True

[maxchenjun]

Thanks GuzunNicolae. It doesn't work in my situation.
But can someone tell me how to define a parameter with a query. I haven't found any mydac help about this. Thanks in advance!

[GuzunNicolae]

Thanks GuzunNicolae. It doesn't work in my situation.

What do you mean it does not work in your situation??
It should work in all situations, if everything is set properly, ofcourse.

But can someone tell me how to define a parameter with a query. I haven't found any mydac help about this. Thanks in advance!

As I have described above.

Code: Select all

myTable.SQL.Text:=  'select * from table where keyword=:key';

':key' is a parameter here. That you refer to it with ParamByName. Look at the code in my previous post.
This is not MyDAC specific, this is Delphi's DB specific. So better search for this in Delphi's help not MyDAC.

[Antaeus]

You can also try something like this:

Code: Select all

var
  utf8str: UTF8String;
begin
  // connection setup
  MyQuery1.Connection.Options.Charset := 'utf8';

  // write
  MyQuery1.SQL.Text := 'INSERT INTO emp (ename) VALUES (:param);';
  utf8str := ;
  MyQuery1.ParamByName('param').AsString := utf8str;
  MyQuery1.Execute;

  // read
  MyQuery1.SQL.Text := 'SELECT * FROM emp';
  MyQuery1.Open;
  MyQuery1.Last;
  ShowMessage(UTF8Decode(MyQuery1.FieldByName('ename').AsString));
end;