How to store and retrieve Chinese and other foreign characters using Coldfusion MX7 and MySQL5.0?

My client needs to convert their survey questions and answers in different foreign languages mainly Chinese and Russian. Currently all are working well in English. If I try to store the chinese characters by using CFQUERY, I got the following error:


Error Executing Database Query.
General error: Incorrect string value: '\x82\x08' for column 'String_UTF8' at row 1
 
The error occurred in D:\lan.512group.com\test\lang.cfm: line 49

47 :
48 :       <cfquery name="TableIDs" datasource="#application.datasource#" dbtype="ODBC" username="#application.datauser#" password="#application.datapass#">
49 :             INSERT INTO Comment_Mixed (Test_name, String_ASCII,String_Latin1, String_UTF8, String_GBK, String_Big5) VALUES ('#test_name#', null, null,'#trim(input)#', null, null)
50 :       </cfquery>
51 :

SQL          INSERT INTO Comment_Mixed (Test_name, String_ASCII,String_Latin1, String_UTF8, String_GBK, String_Big5) VALUES ('Input Chinese UTF-8', null, null,'W‚', null, null)
DATASOURCE         extranet_01
VENDORERRORCODE         1366
SQLSTATE         S1000
Please try the following:

    * Check the ColdFusion documentation to verify that you are using the correct syntax.
    * Search the Knowledge Base to find a solution to your problem.

Browser         Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.1) Gecko/2008070208 Firefox/3.0.1
Remote Address         192.168.0.14
Referrer         http://cfserver/test/lang.cfm
Date/Time         12-Sep-08 10:56 AM
Stack Trace
at cflang2ecfm1923750369.runPage(D:\lan.512group.com\test\lang.cfm:49) at cflang2ecfm1923750369.runPage(D:\lan.512group.com\test\lang.cfm:49)

java.sql.SQLException: General error: Incorrect string value: '\x82\x08' for column 'String_UTF8' at row 1
      at org.gjt.mm.mysql.MysqlIO.sendCommand(Unknown Source)
      at org.gjt.mm.mysql.MysqlIO.sqlQueryDirect(Unknown Source)
      at org.gjt.mm.mysql.MysqlIO.sqlQuery(Unknown Source)
      at org.gjt.mm.mysql.Connection.execSQL(Unknown Source)
      at org.gjt.mm.mysql.Connection.execSQL(Unknown Source)
      at org.gjt.mm.mysql.Statement.execute(Unknown Source)
      at org.gjt.mm.mysql.jdbc2.Statement.execute(Unknown Source)
      at coldfusion.server.j2ee.sql.JRunStatement.execute(JRunStatement.java:212)
      at coldfusion.sql.Executive.executeQuery(Executive.java:705)
      at coldfusion.sql.Executive.executeQuery(Executive.java:638)
      at coldfusion.sql.Executive.executeQuery(Executive.java:599)
      at coldfusion.sql.SqlImpl.execute(SqlImpl.java:236)
      at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:498)
      at cflang2ecfm1923750369.runPage(D:\lan.512group.com\test\lang.cfm:49)
      at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:152)
      at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:343)
      at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65)
      at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:210)
      at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:51)
      at coldfusion.filter.PathFilter.invoke(PathFilter.java:86)
      at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:50)
      at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28)
      at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38)
      at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38)
      at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22)
      at coldfusion.CfmServlet.service(CfmServlet.java:105)
      at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:78)
      at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:91)
      at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42)
      at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:257)
      at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:527)
      at jrun.servlet.jrpp.JRunProxyService.invokeRunnable(JRunProxyService.java:204)
      at jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java:349)
      at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:457)
      at jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:295)
      at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66)

-------------------------------------------------------------------------------------------------------------------------------

My test code:

 <html><head>
 <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
 </head><body>
 <cfform method="post" action="lang.cfm">
 <cfoutput>
      <cfscript>
             SetEncoding("form","utf-8");
      </cfscript>
      <cfprocessingdirective pageencoding="utf-8">
<!--- Default input text --->
  <cfset input = '???'>
  <cfset input_hex = 'E794B5E8A786E69CBA2FE99BBBE8A696E6A99F'>
<cfset test_name = "Input Chinese UTF-8">
<!--- Form submit detection --->

<!--- Process form input data --->
       <cfif isdefined("form.Submit")>
                <cfif isdefined("form.Input")>
                  <cfset input = form.Input>
               </cfif>
            

<!---   Set character_set_client and character_set_connection --->
      <cfquery name="TableIDs" datasource="#application.datasource#" dbtype="ODBC" username="#application.datauser#" password="#application.datapass#">
            SET character_set_client=utf8
      </cfquery>

      <cfquery name="TableIDs" datasource="#application.datasource#" dbtype="ODBC" username="#application.datauser#" password="#application.datapass#">
            SET character_set_connection=utf8
      </cfquery>
      <cfquery name="TableIDs" datasource="#application.datasource#" dbtype="ODBC" username="#application.datauser#" password="#application.datapass#">
            SET character_set_database=utf8
      </cfquery>
      <cfquery name="TableIDs" datasource="#application.datasource#" dbtype="ODBC" username="#application.datauser#" password="#application.datapass#">
            SET character_set_server=utf8
      </cfquery>
      <cfquery name="TableIDs" datasource="#application.datasource#" dbtype="ODBC" username="#application.datauser#" password="#application.datapass#">
            SET NAMES utf8
      </cfquery>
      
      <cfquery name="TableIDs" datasource="#application.datasource#" dbtype="ODBC" username="#application.datauser#" password="#application.datapass#">
            DELETE FROM Comment_Mixed WHERE Test_Name ='#test_name#'
      </cfquery>

      <cfquery name="TableIDs" datasource="#application.datasource#" dbtype="ODBC" username="#application.datauser#" password="#application.datapass#">
            INSERT INTO Comment_Mixed (Test_name, String_ASCII,String_Latin1, String_UTF8, String_GBK, String_Big5) VALUES ('#test_name#', null, null,'#trim(input)#', null, null)
      </cfquery>

      </cfif>

<!--- Display form --->
      <cfinput type="Text" size="40" maxlength="64" name="Input" value="#input#"/><br/>
      <cfinput type="Submit" name="Submit" value="Submit"/>

<!--- Generate reply --->
      <cfquery name="TableIDs" datasource="#application.datasource#" dbtype="ODBC" username="#application.datauser#" password="#application.datapass#">
            SET character_set_results=utf8
      </cfquery>

<!---   Set character_set_results --->
      <cfquery name="select_query" datasource="#application.datasource#" dbtype="ODBC" username="#application.datauser#" password="#application.datapass#">
            SELECT * FROM Comment_Mixed WHERE Test_Name = '#test_name#'
      </cfquery>
      
      <cfif select_query.recordcount LTE 0>
          <cfset output = 'Query returns Zero records'>
       <cfelse>
            <cfset output = #select_query.String_UTF8#>
       </cfif>

   <pre>
    Content-Type:<Br>
      text/html; charset=utf-8<Br>
    You have submitted:<Br>
      Text = #input#
    Saved and retrieved from database:<Br>
      Text = #output#
    </pre>
  </cfoutput>
 </cfform>

</body></html>
--------------------------------------------------------------------------------------------------------------------------------------------
I was frustrated with the output. I write this script by using the following PHP script. But PHP script works fine with same Mysql Database.



<?php #MySQL-Input-Chinese-UTF8.php
# Copyright (c) 2007 by Dr. Herong Yang, http://www.herongyang.com/
#
  print('<html><head>');
  print('<meta http-equiv="Content-Type"'.
    ' content="text/html; charset=utf-8"/>');
  print('</head><body>'."\n");

# Default input text
  $input = '???';
  $input_hex = 'E794B5E8A786E69CBA2FE99BBBE8A696E6A99F';

# Form submit detection
  $submit = isset($_REQUEST["Submit"]);

# Process form input data
  if ($submit) {
    if (isset($_REQUEST["Input"])) {
      $input = $_REQUEST["Input"];
    }
    $con = mysql_connect("localhost", "root", "");
    $ok = mysql_select_db("test", $con);
    $test_name = "Input Chinese UTF-8";

#   Set character_set_client and character_set_connection
      mysql_query("SET character_set_client=utf8", $con);
      mysql_query("SET character_set_connection=utf8", $con);

#   Delete the record
    $sql = "DELETE FROM Comment_Mixed WHERE Test_Name ='$test_name'";
    mysql_query($sql, $con);

#   Build the SQL INSERT statement
    $sql = "INSERT INTO Comment_Mixed (Test_name, String_ASCII,String_Latin1, String_UTF8, String_GBK, String_Big5) VALUES ('$test_name', null, null, '$input', null, null)";

      echo $sql;

#   Run the SQL statement
    mysql_query($sql, $con);

    mysql_close($con);
  }

# Display form
  print('<form>');
  print('<input type="Text" size="40" maxlength="64"'
   . ' name="Input" value="'.$input.'"/><br/>');
  print('<input type="Submit" name="Submit" value="Submit"/>');
  print('</form>'."\n");

# Generate reply
  if ($submit) {
    $con = mysql_connect("localhost", "root", "");
    $ok = mysql_select_db("test", $con);

#   Set character_set_results
      mysql_query("SET character_set_results=utf8", $con);

    $sql = "SELECT * FROM Comment_Mixed"
      . " WHERE Test_Name = '$test_name'";
    $res = mysql_query($sql, $con);
    $output = 'SELECT failed.';
    if ($row = mysql_fetch_array($res)) {
      $output = $row['String_UTF8'];
    }  
    mysql_free_result($res);

    print('<pre>'."\n");
    print('Content-Type:'."\n");
    print('  text/html; charset=utf-8'."\n");
    print('You have submitted:'."\n");
    print('  Text = '.$input."\n");
    print('  Text in HEX = '.strtoupper(bin2hex($input))."\n");
    print('  Default HEX = '.$input_hex."\n");
    print('Saved and retrieved from database:'."\n");
    print('  Text = '.$output."\n");
    print('  Text in HEX = '.strtoupper(bin2hex($output))."\n");
    print('</pre>'."\n");

    mysql_close($con);
  }

  print('</body></html>');
?>

The followings are my current MySQL Database charsets:

Variable_name       Value
character_set_client       utf8
character_set_connection       utf8
character_set_database       utf8
character_set_filesystem       binary
character_set_results       utf8
character_set_server       utf8
character_set_system       utf8
character_sets_dir       D:\Program Files\MySQL\MySQL Server 5.0\share\char...

Any help will be appreciated!!!