Loading Unicode Data to mySQL

--_000_989F36F99568944BB3D368A10AC0D65908A072FE10INDXM3122di rs_
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi,

Currently we are trying to load Unicode data encoded in UTF-8 to mySQL =
but the data is getting corrupted during load. Loading is done through =
Informatica (ETL Tool) and data is properly extracted and interpreted by =
Informatica but still it is failing to load it in the correct format. =
When we are trying to insert the same data manually it is successful =
i.e. data is getting loaded to mySQL successfully. Also when loading is =
done to a flat file or Oracle then also loading is happening as usual.

Given below are the details:

Server: MySQL
Version: 5.1.51-enterprise-gpl-pro-log

mysql> SHOW VARIABLES LIKE 'character_set%';

+--------------------------+----------------------------+
Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8
| character_set_connection | utf8
| character_set_database | utf8
| character_set_filesystem | binary
| character_set_results | utf8
| character_set_server | utf8
| character_set_system | utf8
| character_sets_dir | /usr/share/mysql/charsets/
+--------------------------+----------------------------+

Mysql> SHOW VARIABLES LIKE 'collation%';

+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_unicode_ci
| collation_database | utf8_unicode_ci
| collation_server | utf8_unicode_ci
+----------------------+-------------------+

Table details:

Column Data type: TEXT
Collation: utf8 - utf8_unicode_ci
Engine: InnoDB
Column Collation: Table Default

ODBC DRIVER: 6.0

Please suggest based on the above settings what we can do to load =
Unicode data.

Best Regards
Swagat


This message is for the designated recipient only and may contain =
privileged, proprietary, or otherwise private information. If you have =
received it in error, please notify the sender immediately and delete =
the original. Any other use of the email by you is prohibited.

--_000_989F36F99568944BB3D368A10AC0D65908A072FE10INDXM3122di rs_--

Re: Loading Unicode Data to mySQL

2011/1/20 :
> Hi,

I lack on knowledge about "Informatica" software. But if you are
really sure that the problem is not on source data and not on backend
configuration, then it is just in the middle. :-)

I will be more specific (at least, as far as I can be). In a MySQL, a
charset is negotiated on connection. This character set can be
different from the server's default. For example, PHP connector uses
latin instead of utf8 by default unless specifically configured. Check
your software/ODBC settings (if they are available)
http://dev.mysql.com/doc/refman/5.1/en/connector-odbc-config uration-connection-parameters.html

Charsets can be a mess if you have several layers and only one of them
fails, but if you understand fully all of MySQL parameters is not so
difficult.

--
Jaime Crespo
MySQL & Java Instructor
Software Developer
Warp Networks


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org