List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:February 10 2011 6:22am
Subject:Re: BLOB data gets encoded as utf8!
View as plain text  
I can't help but wonder, if you send a string, does that mean you're putting
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 to
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 <namezero@stripped>

> 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
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>


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

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