« Google's Answer to Comment Spam | Main | Extending Google Maps With JavaScript »

February 18, 2005

UTF8, MySQL 4.1, and CFMX 7.0

I spent a good portion of my day trying to get unicode to work with MySQL 4.1 and CFMX 7.0. As it turns out, it's actually pretty easy. I had the database tables created correctly. That I learned from earlier versions of MySQL. Just add this to the end of your CREATE TABLE command:

CHARACTER SET utf8;

I even had the connection string right in the data source form in the CFMX administrator:

useUnicode=true&characterEncoding=UTF8

The problem is that the connection string apparently doesn't go in the "connection string" field. Since I'm using a newer MySQL driver than the one that shipped with CFMX 7 (that supports MySQL 4.x), I discovered that you actually have to append the connection string to the JDBC URL, like this:

jdbc:mysql://localhost:3306/dataBaseName?useUnicode=true&characterEncoding=UTF8

Once I made the change, instant Japanese! I hope this saves someone out there several hours of head scratching.

Posted by cantrell at February 18, 2005 05:30 PM | References

Related Entries

Comments

Christian, any idea why the version of the driver which shipped with CF 7 doesn't support 4.x? I saw that after the install and was surprised. So far, that driver is working fine on my 4.x datasources, but I'm wondering if that's going to last.

Posted by: Steve Ray at February 18, 2005 05:40 PM

Licensing issues, I believe.

The biggest issue is authenticating a connection since the type of encryption MySQL uses changed from 3.x to 4.x. Using the new driver is easy and free. You can get instructions here:

http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=6ef0253

Posted by: Christian Cantrell at February 18, 2005 05:51 PM

Here is the Turkish version.
http://demirkapi.net/blogcfc/index.cfm?mode=entry&entry=481739DE-C087-963F-89338436EE1EBB67


My blog (demirkapi.net) works with MySQL 4.1.8 & CFMX 7.


Here is also CFMX7 & PostgreSQL 8 on Windows:
http://demirkapi.net/blogcfc/index.cfm?mode=entry&entry=920F71D6-0E89-4D8A-8CFF7860683A2C1A

Posted by: Oğuz Demirkapı at February 18, 2005 07:38 PM

... and here in this page also there are some Unicode problems.

Check my name in the entry above :)

Posted by: Oguz Demirkapi at February 18, 2005 07:40 PM

But how to use the exist database? we must recreate tables with "CHARACTER SET utf8", or how to convert the exist into urf8,?

Posted by: yiming at February 27, 2005 10:14 PM

This is really annoying problem.

If you do not have a huge amount of databases there is a method that I made before.

First use CF5 and export all database into XML file as Unicode. There are some good XML tools for CF5 such as soXML etc. After that process, you can read these XML files via CFMX and write them into database as Unicode.

Just a simple suggestion.

Posted by: Oguz Demirkapi at March 3, 2005 05:23 PM

!!! I've spent too many hours banging my head against a wall and I still haven't figured this out.

View my post on Macromedia

You can see that I eventually moved to MS SQL Server because I could never get mysql to work.

My question: I'm using the default mysql driver that shipped with CF 6.1 MX. Is the connection string box in the data source window the appropriate place for the characterEncoding variable, or should it go somewhere else?

Posted by: Nathan Given at August 10, 2005 02:12 PM

At least for CFMX 6.1, one needs to use the 3.0.x strain of the connector rather than the 3.1.x variety.

Posted by: Mike Osterman at August 23, 2005 02:39 PM

Thanks just trying to get this to work.

Posted by: Grzegorz Bugaj at February 1, 2006 07:44 AM

I M inserting Mangal Font data into mysql using utf8 through C#. While retriving I M not getting original data
PLZ suggest

Posted by: Shailendra at February 14, 2006 05:58 AM