Problem inserting special Hungarian characters into db

[Guest]

Hi,

I've posted this question in the database connection forum but put it here too because I don't know where to fit better.
I read that it would be the most straightforward way to do everything in UTF-8 because it handles well special characters so I've tried to set up a simple testing environment. Besides I use CF MX7 and my hosting provider creates the dsn for me so I think the db driver is JDBC but not sure.

1.) In Dreamweaver I created a page with UTF-8 encoding set the Unicode Normalization Form to "C" and checked the include unicode signature (BOM) checkbox. This created a page with the meta tag: <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />. I've checked the HTTP header with an online utility at delorie.com and it gave me the following info:
HTTP/1.1, Content-Type: text/html; charset=utf-8, Server: Microsoft-IIS/6.0

2.) Then I put the following codes into the top of my page before everything:
<cfprocessingdirective pageEncoding = "utf-8">
<cfset setEncoding("URL", "utf-8")>
<cfset setEncoding("FORM", "utf-8")>
<cfcontent type="text/html; charset=utf-8">

3.) I wrote some special Hungarian chars (<p>őű</p>) into the page and they displayed well all the time.

4.) I've created a simple MySQL db (MySQL Community Edition 5.0.27-community-nt) on my shared hosting server with phpMyAdmin with default charset of UTF-8 and choosing utf8_hungarian_ci as default collation. Then I creted a MyISAM table and the collation was automatically applied to my varchar field into wich I stored data with special chars. I've checked the properties of the MySQL server in MySQL-Front prog and found the following settings under the Variables tab: character_set_client: utf8, character_set_connection: utf8, character_set_database: latin1, character_set_results: utf8, character_set_server: latin1, character_set_system: utf8, collation_connection: utf8_general_ci, collation_database: latin1_swedish_ci, collation_server: latin1_swedish_ci.

5.) I wrote a simple insert form into my page and tried it using both the content of the form field and a hardcoded string value and even tried to read back the value of the #FORM.special_char# variable. In each cases the special Hungarian chars changed to "q" or "p" letters.

Can anybody see something wrong in the above mentioned or have an idea to test something else?
I am thinking about to try this same page against a db on my other hosting providers MySQL server.
Here is the to the form: http://209.85.117.174/pages/proba/chartest/utf8_1/form.cfm

Thanks, Aron

[Guest]

I've created the test db on another MySQL server at another hoster provider changed the DSN and the result is the same: it writes the string into that db with p and q instead of the spec chars. I'm a bit confused now.

[Pekka]

character_set_database: latin1

That's wrong. That means that all databases (and tables, fields etc under it) are told to store data encoded with latin1/iso-8859-1.

If you can, run this to display the charset of the table in question: SHOW CREATE TABLE YOUR_TABLE_NAME_HERE;

The digg guys ran into a similar problem:

http://blog.digg.com/?p=53

[Guest]

Some new info about the advancements in my project:

I've tried to make the insertion at a third hosting provider's MySQL server with my 'everything is UTF-8' test case and IT'S DONE! There are my lovely spec chars :-)

Then I've checked the char encoding according -Per's tip in all of my so far used test MySQL dbs and it reported that 'CHARSET=utf8 COLLATE=utf8_hungarian_ci' so this part seems to me OK.

I asked my hosting provider where my production app should run about the db driver and they told it's JDBC (what version of Jconnect still donno') and they are ready to append &characterSetResults=UTF-8 to the JDBC url connection string (somebody told this tip also as a possible soultion) but they asked me to provide the complete connection string to be used for my datasource. I've tried to compose it in my localhost development environment in ColdFusion Admin but it gave me a Connection verification failed error. So I think I did something wrong and need help to write the correct connection string that can be passed to the hosting provider. So the connection string structure I tried to use in the JDBC URL field of the datasource area of CFAdmin is something like this: jdbc:mysql://someipaddresshere/mydbname&characterSetResults=UTF-8
How can it be corrected?

Thanks, Aron

[Pekka]

We're using connector/j 3.1.6 and doesn't need any special params in the connection string for utf-8 to work.

Depending on your driver version, you maybe want to try utf8 instead of utf-8:

http://bugs.mysql.com/bug.php?id=9206

[Pekka]

And its:

jdbc:mysql://someipaddresshere/mydbname?characterSetResults=UTF8

NOT

jdbc:mysql://someipaddresshere/mydbname&characterSetResults=UTF8