Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 728
  • Last Modified:

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

0
rcbuchanan
Asked:
rcbuchanan
  • 4
  • 2
1 Solution
 
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
 
azadisaryevCommented:
they need to be ut8 and one of acceptable utf8 collations.

Azadi
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now