rcbuchanan
asked on
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.ne wsNews.new sResults.n ewsNewsRes ult[i].new sTitle.Xml Text
live_doc.SearchResponse.ne wsNews.new sResults.n ewsNewsRes ult[i].new sSnippet.X mlText
Thanks!
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.ne
live_doc.SearchResponse.ne
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>
ASKER
Database: Default Char set: Latin1 & Collation: latin1_swedush_ci
ditto table ... and columns.
(defaulted and untouched)
ditto table ... and columns.
(defaulted and untouched)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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
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#'>
ASKER
my JDBC connection string needed to be modified to force UTF8
Azadi