Problems Inserting Foreign Language Characters (Chinese, Russian etc) into MySQL Table (with ColdFusion)

Can you help?

I'm reading news in multiple languages from english thru russian, chinese and japanese ... and ... inserting into a temporary table for sorting and display.
BUT ... the foreign language characters (ie. chinese characters) appear in the database (mysql 5.x) as ????x???????????xx///???? etc.

What on earth am I doing wrong!?

The database fields in question are msn_title and msn_snippet ...
coming from:

live_doc.SearchResponse.newsNews.newsResults.newsNewsResult[i].newsTitle.XmlText
live_doc.SearchResponse.newsNews.newsResults.newsNewsResult[i].newsSnippet.XmlText

Thanks!

<cfquery name="insertnews" datasource=#db_source# username=#db_username# password=#db_password#>
insert into news_msnlive 
(msn_domainid, msn_request, msn_source, msn_url, msn_title, msn_snippet, msn_date, msn_breakingnews, msn_requeststring) 
values (#loadDomains.emaildomain_id#, now(), '#live_doc.SearchResponse.newsNews.newsResults.newsNewsResult[i].newsSource.XmlText#', '#live_doc.SearchResponse.newsNews.newsResults.newsNewsResult[i].newsURL.XmlText#', '#live_doc.SearchResponse.newsNews.newsResults.newsNewsResult[i].newsTitle.XmlText#', '#live_doc.SearchResponse.newsNews.newsResults.newsNewsResult[i].newsSnippet.XmlText#', #ConvertEpochTime(live_doc.SearchResponse.newsNews.newsResults.newsNewsResult[i].newsDate.XmlText)#, '#live_doc.SearchResponse.newsNews.newsResults.newsNewsResult[i].newsBreakingNews.XmlText#', '#livebody#')
</cfquery>

Open in new window

rcbuchananAsked:
Who is Participating?
 
azadisaryevCommented:
they need to be ut8 and one of acceptable utf8 collations.

Azadi
0
 
azadisaryevCommented:
what's the collation and charset settings on your db, db table and the columns in it?

Azadi
0
 
rcbuchananAuthor Commented:
Database: Default Char set: Latin1 & Collation: latin1_swedush_ci
ditto table ... and columns.

(defaulted and untouched)
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
rcbuchananAuthor Commented:
Azadi:
I changed the table chr set and collation to UT8 and ... no change.  The foreign characters remain ??????!?#!?!/!? instead of the correct Kanjii etc.

Per below ... the foreign language text is definitely being picked up.

 1 - [à… x èԐ A] øm ä/ˆø lݐ

Does it have anything to do with the way I'm inserting the variables? newsTitle and newsSnippet?

Richard
0
 
rcbuchananAuthor Commented:
I found this test code online at :
http://www.lynchconsulting.com.au/blog/index.cfm/2008/2/7/CFMX-Mysql-Query-String-for-UTF8-and-Multiple-Queries

I used the following table for testing:

CREATE TABLE `utf8test` (
`id` int(11) NOT NULL,
`data` varchar(25) default NULL,
`datalong` text,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

- see the attached code snippet ...
it fails to display (cfdump) the correct foreign characters.



Even this does not work! What on earth am i doing wrong!?
Richard
<cfset datasource = "demo">
<cfset testData = "ø¢FW">
<cfquery name="test" datasource="demo">
   DELETE FROM utf8test;
</cfquery>
 
<cfquery name="test" datasource="demo">
   INSERT INTO utf8test (id,data,datalong)
   VALUES(1,<cfqueryparam value="#testData#">,<cfqueryparam value="#testData#">)
</cfquery>
 
<cfquery name="test" datasource="demo">
   SELECT * FROM utf8test
</cfquery>
 
<cfdump var='#test#'>

Open in new window

0
 
rcbuchananAuthor Commented:
my JDBC connection string needed to be modified to force UTF8
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.