[x]
Posted via EE Mobile

Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.

Question
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

7.4

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

Asked by ramesh_ayyanar in MySQL Server, ColdFusion Application Server, Cold Fusion Markup Language

Tags: Macromedia, Coldfusion, MX7

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!!!
[+][-]09/11/08 11:11 PM, ID: 22457450Accepted Solution

View this solution now by starting your 30-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

About this solution

Zones: MySQL Server, ColdFusion Application Server, Cold Fusion Markup Language
Tags: Macromedia, Coldfusion, MX7
Sign Up Now!
Solution Provided By: azadisaryev
Participating Experts: 3
Solution Grade: A
 
[+][-]09/12/08 07:01 AM, ID: 22459879Assisted Solution

Assisted solutions are selected by the member who asked the question as a comment that contributed to their question's solution.

Start your 30-day free trial to view this Assisted Solution or ask the Experts your question.

 
[+][-]10/05/08 11:44 AM, ID: 22645595Administrative Comment

Experts Exchange has a courteous staff of administrators who help members get the most out of the website by means of administrative comments like this one.

Start your 30-day free trial to view this Administrative Comment or ask the Experts your question.

 
[+][-]10/05/08 03:51 PM, ID: 22646357Expert Comment

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]10/09/08 03:07 PM, ID: 22682885Administrative Comment

Experts Exchange has a courteous staff of administrators who help members get the most out of the website by means of administrative comments like this one.

Start your 30-day free trial to view this Administrative Comment or ask the Experts your question.

 
[+][-]10/15/08 06:10 PM, ID: 22727295Administrative Comment

Experts Exchange has a courteous staff of administrators who help members get the most out of the website by means of administrative comments like this one.

Start your 30-day free trial to view this Administrative Comment or ask the Experts your question.

 
 
Loading Advertisement...
20091111-EE-VQP-92 / EE_QW_2_20070628