Character sets, ColdFusion, and MySQL

I had a problem with character sets when using ColdFusion and MySQL. We found a solution, so I figured I'd post it here so everyone knows.

ColdFusion MX by default uses UTF-8 for it's charater encoding. This is not supported by MySQL before version 4.1. This does not mean you can't store it in MySQL. What you have to do is take the string and encode it in Base64 using the ToBase64 function. You then store it in a text field. When you retrieve it, you have to convert it to binary, then convert it to a string using the ToBinary and ToString functions.

The only issue that we came across was that the Base64 encoding makes the string longer, so if you're having trouble, double check your field lengths. I don't know how much longer it makes strings.

Hope this helps.

BTW, just to add something here, with you hv cfmx 6.1 and access to the administrator, you can change the default charset encoding

I changed my charset encoding in cfmx 6.1 but now it changes hyphen, apastraphe and a few more characters include three dots "..." to "?". Any idea how i can fix that. for the time being i am formatting my strings to use the HTML character code instead of the symbolds themselves...hope some one has an idea about this.