BLOB data gets encoded as utf8!

Hello everyone!

I am using an MFC unicode project that uses ODBC to access a MySQL
5.1.50 database via the MySQL ODBC 5.1.6 driver.
One of the tables contains two LONGBLOB columns, and the table default
charset is utf-8 (since the application is unicode).

However, when inserting into the LONGBLOB columns via an INSERT
statement, the data gets corrupted/modified because is incorrectly UTF-8
encoded.

My insert statement (simplified) does this:

INSERT INTO _table_ (Desc, Data) VALUES ('...', '_blobdata_');

I have also tried:


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: BLOB data gets encoded as utf8!

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=B6kiewicz [mailto:admin@multipasko.pl]=20
Sent: Wednesday, February 09, 2011 7:45 AM
To: Andreas Iwanowski
Subject: BLOB data gets encoded as utf8! (Anyone?)

Before inserting BLOB data use:

=20

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.ht ml




"If the column has a binary data type (BINARY =
, =
VARBINARY =
, BLOB ), 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




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: BLOB data gets encoded as utf8!

--0015174be6568c6616049be79a3e
Content-Type: text/plain; charset=ISO-8859-2
Content-Transfer-Encoding: quoted-printable

I can't help but wonder, if you send a string, does that mean you're puttin=
g
text in a blob ? Blobs are binary, and thus don't get encoded in the sense
of UTF8 vs Unicode. For a string, you may want a TEXT type column.

On the other hand, if you're indeed trying to insert binary data, it is not
the best of ideas to insert it into an SQL string, where it will be subject
to UTF8 or whatever interpretation.

For large objects, and generally with repeating statements too, it's best t=
o
use bind variables to send your data. That also removes the need to worry
about escaping, sql insertion and the like.


2011/2/10 Andreas Iwanowski

> Thank you for your reply, Janusz.
>
> I appreciate your help.
>
> I have tried making that call before the INSERT statement, but to no avai=
l.
> 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 i=
s
> 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 OD=
BC.
>
> 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 fr=
om
> 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=B6kiewicz [mailto:admin@multipasko.pl]
> 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.ht ml
>
>
>
>
> "If the column has a binary data type (BINARY <
> http://dev.mysql.com/doc/refman/5.1/en/binary-varbinary.html > , VARBINARY
> , 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 s=
et
> 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
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dvegivamp@tuxera.b=
e
>
>


--=20
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0015174be6568c6616049be79a3e--