2007-10-07

ASP.NET 2.0 with MySQL 4.1.2 - unicode problem

Hey guys
This is my first post and the reason i've actually started this blog.
you see, it's now 3:00 AM here but i could not get sleep until I share this with the world :)

during the last few days i struggled connecting MySQL database, version 4.1.2, through my ASP.NET application (using SubSonic DAL btw - great project, thanks guys!), and sending unicode utf-8 format data.
The problem was that when I tried saving data (Insert or Update SQL Commands) i got question marks ('?????') saved instead of unicode characters, like hebrew for example.

I've looked up at all the forums and I have tried everything. I mean really - EVERYTHING.

For example, I've read that I should send, right after I'm establishing the connection,
SET NAMES utf8 COLLATE 'utf8_unicode_ci'; SET CHARACTER SET utf8;

So I've tried that... but no good, still got the question marks.

I've downloaded the connector code and recompiled it. tried to see what does it sends. The ASP Connector version was 5.1.3 btw, and i've also used the MySQL.WEB functionality in order to use the ASP:Login, membership and roles functionality, again - thanks guys :) works like magic.

I must mention that when I tried these on my local MySQL 5 installed on my computer - there were no unicode problems. Only when I've tried it on the MySQL 4 that is installed on the web server I'm using, then I had these issues.

Eventually I've tried it all. I've set every variable on the server to UTF-8. I've changed all the database, tables and fields collations to the same collation - utf8_unicode_ci.
But the problem was not corrected.

I could not sleep. I could not eat. I had to fix that damn issue.
I've started looking on the ASP Connector for MySQL code. I've learn that it uses encoding for the transmision... but setting it to UTF-8 instead of Latin1 did not help either.

Eventually I've found in some forum a suggestion. Adding CharSet=utf8 to the connection string.
And that was it :)
That made the unicode finally work.

So if you're reading my post right now and having the same problem... try all these, including sending the set names command of course.

Good luck! and happy programming ;)

Liad

8 comments:

john clinton said...

Great Blog! I tried lot but not got solution before this blog...

Eran (Aka parnar) said...

dude great work on this solution

i've been searching over the internet but nothing until i got to this blog.
thank you very much :)

malalin said...

You Are the KING...

Best Solution...

michshat said...

i've only looked for the solution half an hour, but it got me little a worry ... glad i came across your blog

Trevor said...

Thanks! Only took 10 mins to find this after thumbing through useless discussion boards. Great!

ENKi said...

Hi, i got this error while i tried to input something into database.

Incorrect string value: '\xE0\xB8\xA7\xE0\xB8\xB1...' for column 'title1' at row 1

i have added 'charset=utf8;' in my connection string and as well as my table.

any advise...??

halntit said...

great!!!!!

less environment polution said...

Great!!! Thanks.

pip install pymssql fails with 'sqlfront.h': No such file or directory

I've tried to install pymssql on Windows using command line: pip install pymssql The operation fails with an error: fatal error C108...