Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2003
    Location
    USA
    Posts
    252

    ColdFusion MX7 and Unicode problem

    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 have been talking to the hosting support for 2 days already, but they still "investigating", which sort of leads me to think they have no clue.

    Code:
    <cfquery datasource="data_source">
      UPDATE Products SET Catalog=
      <cfif IsDefined("FORM.Catalog") AND #FORM.Catalog# NEQ "">
        '#FORM.Catalog#'
          <cfelse>
          NULL
      </cfif>
      , ProductName=
      <cfif IsDefined("FORM.ProductName") AND #FORM.ProductName# NEQ "">
        '#FORM.ProductName#'
          <cfelse>
          NULL
      </cfif>
       , ProductName_Rus=
      <cfif IsDefined("FORM.ProductName_Rus") AND #FORM.ProductName_Rus# NEQ "">
        '#FORM.ProductName_Rus#'
          <cfelse>
          NULL
      </cfif>
    Web Design and Development by Zealus - Complete business solutions for the web!

    Small business blog - small business consulting, company analysis, advisory and more.

  2. #2
    Join Date
    Jan 2006
    Location
    Athens, Greece
    Posts
    1,479
    Whenever you start the db connection issue this query:
    "SET NAMES UTF8"

  3. #3
    Join Date
    Feb 2003
    Location
    USA
    Posts
    252
    Updated:
    the reference here: http://dev.mysql.com/doc/refman/5.0/...onnection.html shows that there are several ways of doing this:

    SET NAMES 'charset_name' COLLATE 'collation_name'
    SET NAMES 'charset_name'
    SET CHARACTER SET charset_name

    So, which one of these should I use?
    Last edited by Zealus.com; 03-10-2008 at 02:41 PM. Reason: Smarter questions inserted.
    Web Design and Development by Zealus - Complete business solutions for the web!

    Small business blog - small business consulting, company analysis, advisory and more.

  4. #4
    Join Date
    Jan 2006
    Location
    Athens, Greece
    Posts
    1,479
    It makes sure the data in & out of the DB are transfered as utf8 characters. Try it.

  5. #5
    Join Date
    Feb 2003
    Location
    USA
    Posts
    252
    Just did, doesn't work
    Web Design and Development by Zealus - Complete business solutions for the web!

    Small business blog - small business consulting, company analysis, advisory and more.

  6. #6
    Join Date
    Feb 2003
    Location
    USA
    Posts
    252
    Update: tried both in the form that posts actual data to the server and in application.cfm - didn't work in both cases.
    Web Design and Development by Zealus - Complete business solutions for the web!

    Small business blog - small business consulting, company analysis, advisory and more.

  7. #7
    Join Date
    Feb 2003
    Location
    USA
    Posts
    252
    Update2:
    just received the version details of database driver
    Code:
    CF DSN  "data_source"  Driver "MySQL (3.x0)".
    Web Design and Development by Zealus - Complete business solutions for the web!

    Small business blog - small business consulting, company analysis, advisory and more.

  8. #8
    Join Date
    Jul 2005
    Location
    Huh... where am I again?
    Posts
    974
    In the application.cfm put:
    Code:
    <cfscript>
    SetEncoding("form","utf-8");
    SetEncoding("url","utf-8");
    </cfscript>
    -Steven | u2-web, LLC - Clustered Shared Hosting
    "It is the mark of an educated mind to be able to entertain a thought without accepting it" -Aristotle

  9. #9
    Join Date
    Feb 2003
    Location
    USA
    Posts
    252
    I dugg this solution on Sitepoint and suggested that hosting guys tried it. Turns out it fixed the problem.

    They added "useUnicode=true&characterEncoding=UTF8" under Connection String in Advanced Setting and it all started to work.
    Web Design and Development by Zealus - Complete business solutions for the web!

    Small business blog - small business consulting, company analysis, advisory and more.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •