List:General Discussion« Previous MessageNext Message »
From:Andreas Iwanowski Date:February 10 2011 2:52am
Subject:RE: BLOB data gets encoded as utf8!
View as plain text  
Thank you for your reply, Janusz.

I appreciate your help.

I have tried making that call before the INSERT statement, but to no avail.
The table collation is set to "utf8 - default collation", and all columns are set to
"Table default".
I am thinking that this problem might be due to me sending the BLOB data in a unicode
string to the server, i.e:

INSERT INTO TableName (Rawfield) VALUES ('%s'); - whereas the %s string is a unicode
string

That string is encoded by escaping mysql characters according to
http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html (0x00, 0x22, 0x27, 0x5C).
Then each byte (and each escaped byte) is followed by a 0x00 before being sent to the
server, just so that I can send it as a unicode string via ODBC.

I.e. the binary data FA 5C 93 A4 would be expanded into FA 00 5C 00 5C 00 93 00 A4 00

Otherwise I can't send the data in a unicode INSERT statement via ODBC from C++ / MFC.

Do you think that could be the issue? If so, do you have a suggestion on how to do it
better?

Thank you again for your help!

Sincerely,
   Andreas Iwanowski

-----Original Message-----
From: Janusz Paƛkiewicz [mailto:admin@stripped] 
Sent: Wednesday, February 09, 2011 7:45 AM
To: Andreas Iwanowski
Subject: BLOB data gets encoded as utf8! (Anyone?)

Before inserting BLOB data use:

 

mysql_query("SET CHARACTER SET latin1");




after that you can set it back to:




mysql_query("SET CHARACTER SET utf8");








This is due to:

http://dev.mysql.com/doc/refman/5.1/en/charset-conversion.html




"If the column has a binary data type (BINARY
<http://dev.mysql.com/doc/refman/5.1/en/binary-varbinary.html> , VARBINARY
<http://dev.mysql.com/doc/refman/5.1/en/binary-varbinary.html> , BLOB
<http://dev.mysql.com/doc/refman/5.1/en/blob.html> ), all the values that it
contains must be encoded using a single character set (the character set you're
converting the column to). If you use a binary column to store information in multiple
character sets, MySQL has no way to know which values use which character set and cannot
convert the data properly."







Kind Regards,
Janusz Paskiewicz

www.freelancer4u.net



Thread
RE: BLOB data gets encoded as utf8!Andreas Iwanowski10 Feb
  • Re: BLOB data gets encoded as utf8!Johan De Meersman10 Feb