Broken Unicode

[Zealus_com]

Never thought I would have a problem with unicode, but here it is.

The web site I am working on is ColdFusion MX7 and mySQL 5.0.45. The data in the DB is UTF-8 encoded, collation is set to utf8_general_ci. The output of the data from DB is valid, all characters are displayed properly.

Problem starts when I try to update records via site's web interface. I have attached a piece of code as an example (the whole query is rather large) to show that there is no charset messing in the query itself. However, whenever I post unicode characters (the site is bilingual, English and Russian) they get converted to something weird. For example, small Russia letters "a" converted into number 0, "b" into number 1 and so on. So whenever I try to post something like "Колбаса" - I get a bunch of numbers and symbols, some of which are invisible.
I talked to the hosting support for 2 days already, but they still "investigating", which sort of leads me to think they have no clue.

[Newsgroup_User]

try including the following in your Application.cfm/cfc:
<cfscript>
SetEncoding("form","utf-8");
SetEncoding("url","utf-8");
</cfscript>

which mysql drivers are used to connect to the db?


Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Zealus_com]

application.cfm already has:

<cfcontent type="text/html; charset=UTF-8">
<cfset setEncoding("URL", "UTF-8")>
<cfset setEncoding("Form", "UTF-8")>

I also tried using in the page that posts data to the server, common include and application.cfm

<cfquery datasource="skazkaf_skazka">
SET CHARACTER SET UTF8
</cfquery>
<cfquery datasource="skazkaf_skazka">
SET NAMES 'UTF8' COLLATE 'utf8_general_ci'
</cfquery>

How do I tell the driver version? This is a shared hosting, the DSN connection was set up by support.

[Newsgroup_User]

not sure how to check the driver without going into cf admin...
check with the host that they use JDBC drivers, NOT odbc...

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Zealus_com]

From hosting's control panel the ODBC looks switched OFF.

[Zealus_com]

This is the reply I got when requesting driver details and version:

CF DSN "skazkaf_skazka" Driver "MySQL (3.x0)".

[Newsgroup_User]

Zealus.com wrote:
> CF DSN "skazkaf_skazka" Driver "MySQL (3.x0)".
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
i imagine that's your problem.

[Newsgroup_User]

yeah, i do not think v.3 had proper utf/unicode support...

there are many threads on these forums about how to setup & configure
v4/5 mysql connectors on cf7

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Zealus_com]

Paul: please elaborate. I am not a hardcore CF developer, so can't really read your mind here 😞

[Zealus_com]

Turns out the problem got fixed. I found this suggestion on some forums on the internet and suggested that hosting guys tried it.

They added "useUnicode=true&characterEncoding=UTF8" under Connection String in Advanced Setting and it all started to work.

[Oğuz Demirkapı]

with MySQL 3.x?

Really wonder ...

[BKBK]

Zealous.com wrote:
The web site I am working on is ColdFusion MX7 and mySQL 5.0.45. The data in the DB is UTF-8 encoded... This is the reply I got when requesting driver details and version: CF DSN "skazkaf_skazka" Driver "MySQL (3.x0)".

Could it be that the MySQL server version is 5.x, but the Coldfusion Admin is configured for the MySQL 3.x driver?

[Oğuz Demirkapı]

You can find a simple tutorial below for MySQL connection in CF.

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

In CF8 there are new drivers for MySQL 4.x and 5.x and these drivers supports Unicode without any problem.

I hope this helps.