Hi all,

I have serious problems running Unicode (ucs2) on a MySQL Database.

My System: OS - Windows XP MySQL V 4.1.7 MyODBC V3.51.10

My Problem: The application we are developing is a tool for managing datas and recipes for production-machine. We have to distribute this tool worldwide, that means the string-type-datas our customers store in the database can have all charachters defined by UNICODE.org. I like to design the database and the tables this way that I can install the database on any PC in the world, means char-columns should be ucs2. I take MyISAM and InnoDB tables mixed in the database.

We have a simular system running at the moment with VisualBasic6 and Access databases. No there are two demands: importing some of the old Access-tables that are really UNICODE INSERT and UPDATE new datas with a windows applicationt using the MyODBC and Microsoft ADO.

What I have done: Export the Access tables to a Unicode-Textfile and later try to Import this with (LOAD DATA INFILE 'd:/temp/ASCII_Unicode.txt' INTO TABLE EM_Dictionary FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\n\r'). MySQL tells me that one row was inserted and all other rows are warnings. When I look into the imported table everything is stored in the first field. It seems that MySQL don't know the first two bytes ("FF FE") in the textfile which tells that this file contains 2-byte unicode characters and so MySQL don't findthe field and line terminating characters. Export the Access tables to a Unicode-Textfile and later try to Import this with the mysqlimport admin-tool. (same problem) Export the Access tables directly out of Access with the export-function over a ODBC-Connection (same problem) Wrote a VB6 program that read all lines of the exported textfiles and tried to store this lines to the MySQL database with "INSERT INTO" statement . Problem here: Characters bigger then 0x00FF come to the database as 0x003F ("?"). Tried to store a greek character with the MySQL ControlCenter into a databasetable "INSERT INTO table (column) VALUES (CHAR(0x03A0));". After that I directly read the column with "SELECT HEX(column) FROM table;" and the output was 0x00A0. Here also the problem that it looks that MySQL cannot handle the 2-byte characters.

The table I was working with:

CREATE TABLE `table` ( `column1` varchar(222) default NULL, `column2` varchar(222) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=ucs2;

I tried also this, using columntype char as UNICODE:

CREATE TABLE `table` ( `column1` char(222) UNICODE default NULL, `column2` char(222) UNICODE default NULL ) ENGINE=MyISAM DEFAULT CHARSET=ucs2;

But when I look into the database with: mysql>SHOW CREATE TABLE `table`; MySQL tells me that the table was created with varchar!

What have I done wrong? ODBC settings Database settings Table settings

Oliver