How to write UTF8 text to MySQL from ASP.NET via ODBC?

Question

I'm using MySQL 5 on shared hosting, connecting from ASP.NET 3.5 using the MySQL 5.1 ODBC driver. I'd like to store UTF8 strings. My tables used to be all in "latin1_swedish_ci", but I converted the the database, table, and column to UTF8 using:

ALTER DATABASE `my_db` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
ALTER TABLE `my_table`  DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
ALTER TABLE `my_table` CHANGE `subject` `subjext` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL

But I still get this error when inserting non-ascii characters (like "遊ぶ"）into my database using an ODBCConnection and ODBCCommand:

ERROR [HY000] [MySQL][ODBC 5.1 Driver][mysqld-5.0.51b-community-nt]Incorrect string value: '\xE3\x80\x80\xE6\x89\x8B...' for column 'subject' at row 1

Note that since I'm using the 5.1 driver, I can't use "SET NAMES utf8;" - it produces an error.

Any ideas what I'm missing?

Answer 1

try:

SET NAMES 'utf8';

[edit] or SET NAMES 'utf-8'; [/edit]

always as first command after establishing the connection.

Answer 2

A few of things to check:

1. Make sure your tables and text fields really accept utf8:
   Use the MySQL Query Browser and try to edit some data manually.
   If it stays OK once you saved your edits, then the fields and tables are correctly set.

2. Make sure that you are actually inserting utf8 compliant-characters and not characters based on another form of encoding such as GB2332 for Chinese.
   If that's the case, you may need to convert the strings to utf8 before you can send them to the database.
   You can have a look at using the Encoding class in .Net.
   I've answered a post on something related a while ago and there is a CodeProject article on just this issue.

3. You probably also need to make sure that the ODBC connection string includes the following:

   CharSet=utf8;

There is a list of all the parameters you can use for the ODBC conection.