Using ColdFusion to display Chinese characters from an AS / 400 server
I am writing a ColdFusion program that uses an cfquery
AS / 400 to retrieve data from an iSeries table and then displays that data on a web page. Several times the data is in Chinese, but it does not output Chinese characters correctly.
I have built the query below for testing,
<cfprocessingdirective pageEncoding="UTF-8" />
<cfquery name="Test" Datasource = "AS400">
select dsc1 from sales where ref = '123456'
</cfquery>
<cfoutput>#test.dsc1#</cfoutput>
The result should be "M5 方 头 螺栓", but I only get "M5". I did another test:
<cfset x = "M5方头螺栓"/>
<cfoutput>#x#</cfoutput>
and the Chinese do not betray any problems.
Since ColdFusion can display characters when they are written in the code, but not when it goes to fetch data via SQL, it seems that the problem is related to ODBC settings or ColdFusion Server data source parameters, but I'm not familiar enough with these settings so you know what needs to be changed to make it work.
source to share
A workaround was found and discussed in the comments. Adding some details here as an answer for future visitors to this page.
There are several considerations when using Unicode (Chinese) characters:
- The data type for the database table must be set to
nvarchar
- Form processing script (CFML) must be set to
utf-8
- I believe ColdFusion is the default, but you can specify that the parameter should be sure.
For example:<cfprocessingDirective pageEncoding="utf-8″>
- I believe ColdFusion is the default, but you can specify that the parameter should be sure.
- Enable "String Format" in ColdFusion Data Source Preferences
- In the ColdFusion data source settings, select the appropriate data source that you are using. Then press the button "show advanced settings". This will show an option for "String Format" Include high ASCII and Unicode characters for data sources configured for non-Latin characters. Select this option and save the data source.
The problem for the OP was that they were using an ODBC datasource and the "String Format" option was not available. After some research and not having any way of configuring an ODBC data source for this parameter, I recommended trying the built-in JDBC driver for "DB2 Universal Database" that comes with ColdFusion. Switching to this driver resolved this issue for the OP.
From comments
Good information. Although this "Include string format ..." is needed with added support for cf_sql_nvarchar in CF10 +? - @Leigh
I believe Lee is correct that newer versions of ColdFusion (10 and later) have much better support for nvarchar fields.
It should also be noted that some older versions of ColdFusion do not always work with the generic DB2 driver installed, and it doesn’t look like the older versions, even if it is, I’m not sure if the new one is there either, but using the "different" option with jt400. jar should work as well. - @MHall
source to share
You have already proven that CF can output UTF-8 characters correctly. Have you tried running this query in the DB Console or UI? Are you getting the correct characters?
If the characters were stored as VARCHAR and not NVARCHAR then there is nothing you can do. The data must be correctly saved first.
If the symbols are stored correctly in the database, try adding <cfprocessingdirective pageEncoding="utf-8">
to the beginning of the query. CF should be used UTF-8
by defualt, but this will force the correct character set if it isn't for some reason.
source to share