MyQuery.SQL.text property and Chinese characters

ewong · Post by **ewong** » Mon 31 Jan 2011 06:32

Hi,

I have a FoxPro table that has a field that may contain Chinese
characters. I'm copying this table to MySQL using the MyDAC
components (specifically, TMyQuery).

I create a SQL string from the fields and field values of each row in
the FoxPro table and then run MyQuery.ExecSQL;.

For some reasons, it gives me a syntax error near an empty field.
I don't understand this, but realized that the field with the Chinese
characters is next to it. Unfortunately, I'm not clear as to what
format the Chinese characters is stored as.

Does MyQuery.SQL.text support Chinese characters?
Here's the actual SQL text:

insert into article (article,artno,rid,jtype,stype,design,mould,batch,unit,photoid,acctno,price,ucost,mcost,lcost,addcost,desc1,desc2,premark,ldate,user,photo,qmethod,req1,req2,req3,req4,pdesc1,pdesc2,pastdata,sphoto,txmattno,txdate,othref1,mk,uprice_hk,uprice_us,ovdate,kingsing,bom,hqty,uqty,cqty,stonewt,size,ws,factor,artno2,whs,remark,sent,`group`,artset,arttype,artproj,artss,artsp,artclf,tz,ct,ph_height,ph_width,pict_height,pict_width,product,factor2) VALUES ("006723","PSX","","PP","Z","03740","S","0007","PC","","",0,0,0,0,0,"DX","","",'2006/9/15',"Y321","PPZ03740S","","","","","","鑽石寶蓋","","","PPZ","",'1899/12/30',"","",0,0,'1899/12/30',"","",0,0,0,"","","",0,"","","",0,"","","","","","","","","",0,0,0,0,0,0)

And the error is :

#42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PPZ","",'1899/12/30',"","",0,,'1899/12/30',"","",0,0,0,"","","",0,"","","" at line 1'.

Using MyDAC v6.00.0.3, and MySQL Dev tools 4.50.308.0.

Edmund

ewong · Post by **ewong** » Mon 31 Jan 2011 06:41

Looking through the log that I dumped for every sql command, it seems
it isn't the Chinese characters that's screwing up the command. Previous
entries also have Chinese characters so it's something with the makeup
of that particular line. If it was the first entry, sure it could be; but it's
the 10150'th entry.

If anyone can spot the error, I'd appreciate it.

Edmund

ewong · Post by **ewong** » Mon 31 Jan 2011 08:07

An addendum.

Using dBForge Studio's "New SQL" and running it against the database,
I can insert the same row to the table. Whereas programatically
inserting that line produces an error.

Edmund

ewong · Post by **ewong** » Mon 31 Jan 2011 09:10

I think I found out what the problem was.

I have a MySQL table with field of char(31), and the value that is being
copied to is 鑽石寶蓋, then the SQL command fails.

I have not found the solution to this. Will do some testing.

Thanks

Edmund

ewong · Post by **ewong** » Mon 31 Jan 2011 10:12

Sorry for talking to myself; but I've done some tests and noticed that
if the sqlstring has this character ('蓋') in it, it chokes. If I remove that
character from the sql string, the insert sql command works.

Unfortunately, I can't do that since it's part of data that needs to
be preserved. Can someone point out how I can preserve this
character, but still have the insert command work?

Thanks

Edmund

Justmade · Post by **Justmade** » Tue 01 Feb 2011 03:00

what encoding are you using? uft8 or big5
also delphi verion?

Please keep the encoding of your server database and TMyConnection same. If you use utf8, you have also to set TMyConnection.Options.UseUnicode to true.

AndreyZ · Post by **AndreyZ** » Tue 01 Feb 2011 12:24

Hello,

We have similar questions. Please specify the following:
- the exact version of your IDE;
- the charsets of the char(31) field and from the TMyConnection.Options.Charset property.

Dimon · Post by **Dimon** » Thu 10 Feb 2011 09:21

Was the problem solved? If any questions come up, please contact us.