Solved

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

Posted on 2009-05-14
6
706 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

815 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

8 Experts available now in Live!

Get 1:1 Help Now