One Star

[resolved] Encoding problems during conversion of XML to MySQL

Hi guys,
I've tried hours to solve this, but I am already going nuts here. I have a public XML file () that I want to fetch and map to a MySQL table (columns: id, name, address, country, list). So I use this:
tFile_Fetch --onSubJobOk--> tFileInputXML --> tMap --> MySQLOutput
It works fine. All rows are written to MySQL, but cyrillic characters etc. are shown as multiple question marks in MySQL. So I thought it has to be an encoding problem (like the XML being non UTF-8) but the XML says "encoding=UTF-8", all options in Talend are set to UTF-8 and the MySQL database and the tables use charset "utf8" with collation "utf8_unicode_ci". When I open the XML file in a simple text editor, I also see the correct chars. I just doesn't work when writing them to MySQL.
Do you have an idea why this could happen? This is driving me crazy. I would really appreciate your help
btw.: using Talend Open Studio 5.5.1
thx
1 ACCEPTED SOLUTION

Accepted Solutions
One Star

Re: [resolved] Encoding problems during conversion of XML to MySQL

Thanks jlolling, I've found the error: If anybody is experiencing the same problems, try adding the following parameters to your JDBC Connection (in my case the MySQL Connection parameters): characterEncoding=utf8&characterSetResult=utf8&useUnicode=true. This worked for me.
Thanks again
3 REPLIES
Seventeen Stars

Re: [resolved] Encoding problems during conversion of XML to MySQL

The encoding is only a matter of parsing the XML file. The job internal converts all char sets to UTF-16 (Java default encoding).
The database driver takes that UTF-16 String an sends it to the database. 
If you have configured your table as UTF-8 everything should be fine.
I suggest for your tests you write cyrillic letters with another tool in your database (e.g. use the MySQL clients) and check if you could read it correctly. 
You should - for tests - change the output from the tMysqlOutput to a tFileOutputDelimited (also configured as UTF-8) and check if you can read this file correctly. 
I guess the read xml is not correctly configured.  
One Star

Re: [resolved] Encoding problems during conversion of XML to MySQL

Thanks jlolling, I've found the error: If anybody is experiencing the same problems, try adding the following parameters to your JDBC Connection (in my case the MySQL Connection parameters): characterEncoding=utf8&characterSetResult=utf8&useUnicode=true. This worked for me.
Thanks again
One Star

Re: [resolved] Encoding problems during conversion of XML to MySQL

Hello All,
I am using Talend 5.5.1 and facing problem with few characters like Å etc. I have done the following steps but still facing the issue
- Changed Talend encoding type to utf8
-  Database connection I changed it to - characterEncoding=utf8&characterSetResult=utf8&useUnicode=true
but unable to resolve the error. 
Any help would be greatly appreciated