Solved

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

Posted on 2009-05-14
6
699 Views
Last Modified: 2012-05-07
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
Comment
Question by:rcbuchanan
  • 4
  • 2
6 Comments
 
LVL 27

Expert Comment

by:azadisaryev
ID: 24392520
what's the collation and charset settings on your db, db table and the columns in it?

Azadi
0
 

Author Comment

by:rcbuchanan
ID: 24393775
Database: Default Char set: Latin1 & Collation: latin1_swedush_ci
ditto table ... and columns.

(defaulted and untouched)
0
 
LVL 27

Accepted Solution

by:
azadisaryev earned 500 total points
ID: 24394018
they need to be ut8 and one of acceptable utf8 collations.

Azadi
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:rcbuchanan
ID: 24399706
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
 

Author Comment

by:rcbuchanan
ID: 24400073
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
 

Author Closing Comment

by:rcbuchanan
ID: 31581791
my JDBC connection string needed to be modified to force UTF8
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MySQL - how to create and select temp tables? 8 68
SQL Insert Query Help 16 87
MySQL Error 3 35
simple mysql statement 3 6
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Creating and Managing Databases with phpMyAdmin in cPanel.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now