Hopeless Migration to MySQL - Chinese problems!
Posted by:
albert mo
Date: December 28, 2005 11:30PM
I want to migrate an existing application from MS Access to MySQL. The target is to have the data maintained by MySQL while using both MS Access and PHP web pages as clients.
The way I use Access as client is to link to MySQL database from Access using MyODBC.
The problem is that I cannot see Chinese data properly after the migration.
If I use MyODBC version 3.51.12, I can enter Chinese but only able to see Chinese entered through Access. Chinese entered through PHP web pages is shown as ??. However, if I look at the data through PHP web pages, the Chinese entered through PHP is OK but those entered through Access are shown as ??. That means, Chinese entered through Access and Chinese entered through PHP are stored in incompatible formats. Since I need to see the same Chinese from both Access clients and PHP web pages, I continue my research as stated in the following paragraphs.
I then tried adding "SET NAMES utf8" in the "initial statement" of the "connection option" of MyODBC to force internal format conversion. But after doing so, connecting to MySQL from Access will result in "send report to microsoft" run-time error.
Finally, I tried using older version (3.51.09) of MyODBC, hoping to solve the run-time error. After switching to this version, no matter whether "SET NAMES ..." is set or not, whenever I input Chinese through Access, I got the "1406 - Data too long for column C1" error. Moreover, I cannot see Chinese at all from Access (including those entered through Access). I also tried different character sets (utf8, big5, ucs2) for the field, table and the database, but it does not help.
Anyone has idea? Or should I give up because there are problems of MySQL 5 in supporting ucs2 (the code, I guess, that is being used by Access)?
I searched the forum and seemed that many got similar problems but none has the answers.
Thanks in anticipation of any comments and ideas sharing.
The details:
Version of Access using: Access 2003
Format of the Access database: Access 2000 format
MySQL version: Distrib 5.0.16