Character encoding with CF and MySQL

[Ashburton]

Okay, I thought this should be rather straight forward but apparently not. I have set up my site to use UTF-8— my cfm pages, the MySQL table, even Dreamweaver. The problem is when I input international character via a form they get written correctly to the MySQL table; however, when I retrieve them in a query and display them on the page I get them displayed incorrectly.

On my input.cfm page I'll enter the string "Téstïñg" in the textbox and submit it. If I look at the record via the MySQL Browser it appears as it should. However when I display it on my output.cfm page it shows the record as "T�st��g" and will do so until I change the meta tag to use charset=ISO-8859-1. Am I missing something or is this how it is suppose to work?

My input.cfm page is set up with both the
<cfprocessingdirective suppresswhitespace="YES" pageencoding="UTF-8">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
tags and a regular input formfield that writes to the MySQL database.

The MySQL table is configured to use the utf8 char set and utf8_unicode_ci collation.

And just to be safe I included useUnicode=true&characterEncoding=utf8&characterSetResults=utf8 in the connection string on the CF Admin datasource setup page.

I'm running CF 6.1, MySQL 4.1, the latest version of Apache Server on a Win2K3 box. I was running the 3.0.16 MySQL JDBC driver but I upgraded it to the 5.0.6 this morning thinking that may fix my issue.

[Ari_Zanuto]

Ashburton,
Whats the collation of your database?

[Ashburton]

utf8-unicode_ci

[Oğuz_Demirkapı]

Please check this tutorial:

http://howtoforge.com/coldfusion7_mysql4.1_connection

[Oğuz_Demirkapı]

Try to put some static text on your page to be sure that you page is in Unicode.

You can also try to activate BOM support for your template.

And also do not trust most of the MySQL clients. There would be problem while connecting database and you may think that data in database is not correct. I use personally Navicat to connect my MySQL DB.

[Ashburton]

Okay, I pasted Téstïñg on my page and got question marks where Unicode characters should have appeared, so apparently my page isn't in Unicode. I can see the display properly on this page in my browser so what do I have to do to make this available on my output page?

[Ashburton · Accepted solution]

I'm still unsure why this works but I've found a solution. I switched all my pages over to character set ISO-8859-1 with the exception of my database table and it works. I get all the normal range character along with the extended Unicode characters to write to the database and output correctly. Unicode characters actually write to the table as their HTML coded character.

If someone feels the need to enlighten me as to why this works please feel free, I'm always willing to learn.

[tomtomtom]

I've got exactly the same problem but with CF8 and MS Access. Set up the site and everything to UTF-8. And the page won't display the characters I need (which is Vietnamese). But they are saved correctly in the database.... Does anybody has a real solution for this - I mean setting the charset on all pages to ISO-8859-1 or whatever doesn't make any sense to me. I want to use UTF-8.
Thanks

[Newsgroup_User]

tomtomtom wrote:
> I've got exactly the same problem but with CF8 and MS Access. Set up the site

and are you using the "access for unicode" driver?

[tomtomtom]

Hello Paul

Your are the best. I didn't think of that and made a test with vietnamese and arab characters and everything works just perfect!!!!!!!

Many thanks