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!!!
by: azadisaryevPosted on 2008-09-11 at 23:11:11ID: 22457450
i am not sure ODBC mysql drivers can tell utf-8 from a hole in the ground... why don't you use proper JDBC drivers? also, depending on your mysql version, it may not properly support UTF-8/Unicode...
); ;
you may also try moving
<cfscript>
SetEncoding("form","utf-8"
SetEncoding("url","utf-8")
</cfscript>
to the very top of your page from where it is now. though i am not sure it will make any difference...