The bug was updated successfully. The following people were notified: the MySQL developers, the bug reporter, the assigned developer, and nobody else.
Bug #18731 Simply Chinese Character Display as "?????"
Submitted: 3 Apr 2006 9:36 Modified: 10 Jan 2008 18:49
Reporter: Vincent Cheung Email Updates:
Status: Won't fix Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51 OS:Windows (Win 2000 / 2003)
Assigned to: CPU Architecture:Any
Tags: ADO, ODBC5-RC

[3 Apr 2006 9:36] Vincent Cheung 
Description:
Is me again!! 

When I want to migrate to MySQL, I found a charset problem again.
System:

Windows 2000/2003
IIS 5.0 + ASP
MySQL 5.0
UTF8 post Simplify chinese, korea, japanese, and http://mx.yahoo.com/
utf8_general_ci / utf8_bin / utf8_unicode_ci

As the topic, it display Simplify Chinese words as "?????".
Traditional Chinese is no problem.

Using MS SQL is no this type problem.
They use this. I tried disable "session.CodePage = 65001" and CodePage=65001, the problem still here.

GOGOGOGOGOGOGOGOGOGO... Find the 

How to repeat:
Please visit the following demo link:
http://www.nowinlove.com/express/tbupdate.asp

Source code:
<%@ CodePage=65001 Language="VBScript"%>
<% Option Explicit %>
<%
session.CodePage = 65001
response.Charset = "utf-8"
dim conn, rdate
rdate = now
set conn = server.CreateObject("adodb.connection")
%>
<%
conn.open "DRIVER={MySQL ODBC 3.51 Driver};" & _
          "SERVER=192.168.0.8;" & _
          "DATABASE=nowinlove;" & _
          "USER=iuser;" & _
          "PASSWORD=iuser;charset=utf8"

session("subpath") = "/express"
dim rs, sqlrs
set rs = server.CreateObject("adodb.recordset")
if request("posttype") = "new" and request("value") <> "" then
	sqlrs = "insert into idxbody (vabody, nilbodyzhtw, nilbodyzhch, nilbodyenus) values ('"& request("vabody")&"', '"&replace(request("nilbodyzhtw"), "'", "''")&"', '"&request("nilbodyzhch")&"', '"&replace(request("nilbodyenus"), "'", "''")&"')"
	set rs = conn.execute(sqlrs)
elseif request("posttype") = "delete" then
	sqlrs = "delete from idxbody where vabody = '"& request("vabody") &"'"
	set rs = conn.execute(sqlrs)
elseif request("posttype") = "update" then
	sqlrs = "update idxbody set nilbodyzhtw = '"&request("nilbodyzhtw")&"', nilbodyzhch = convert('"&request("nilbodyzhch")&"' using utf8), nilbodyenus = '"&replace(request("nilbodyenus"), "'", "''")&"' where vabody = '"& request("vabody") &"'"
	set rs = conn.execute(sqlrs)
end if 
response.Write(sqlrs)
%>
<br />
<%
sqlrs = "select * from idxbody"
rs.open sqlrs, conn, 1, 3
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Database Migration Test</title>
</head>

<body>
Queue by MySQL &amp; ASP <br />
<table width="100%"  border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td>value</td>
    <td>zhtw</td>
    <td>zhch</td>
    <td>enus</td>
    <td width="100">&nbsp;</td>
  </tr>
<form action="tbupdate.asp" method="post" name="postinfo" id="postinfo">
  <tr>
    <td><input name="vabody" type="text" id="vabody" size="16" /></td>
    <td><input name="nilbodyzhtw" type="text" id="nilbodyzhtw" /></td>
    <td><input name="nilbodyzhch" type="text" id="nilbodyzhch" /></td>
    <td><input name="nilbodyenus" type="text" id="nilbodyenus" /></td>
    <td><input type="submit" name="Submit" value="新增" />
      <input name="posttype" type="hidden" id="posttype" value="new" /></td>
  </tr>
</form>
  <tr>
    <td colspan="5"><hr /></td>
  </tr>
<% if rs.eof = true then %>
  <tr>
    <td colspan="5">Nodata</td>
  </tr>
<% end if %>
<% while rs.eof = false %>
<form action="tbupdate.asp" method="post" name="postinfo" id="postinfo">
  <tr>
    <td><input name="vabody" type="text" id="vabody" value="<% =rs("vabody")%>" size="16" /></td>
    <td>    <input name="nilbodyzhtw" type="text" id="nilbodyzhtw" value="<% =rs("nilbodyzhtw")%>" /></td>
    <td><input name="nilbodyzhch" type="text" id="nilbodyzhch" value="<% =rs("nilbodyzhch")%>" /></td>
    <td><input name="nilbodyenus" type="text" id="nilbodyenus" value="<% =rs("nilbodyenus")%>" /></td>
    <td><input type="submit" name="Submit" value="更新" />
      <input name="posttype" type="hidden" id="posttype" value="update" />
      <a href="tbupdate.asp?posttype=delete&vabody=<% =rs("vabody")%>">DEL</a></td>
  </tr>
</form>
<%
rs.movenext
wend
%>
</table>
</body>
</html>
<%
rs.close
set rs = nothing
%>
<%
conn.close
set conn = nothing
%>

Suggested fix:
Just a tips, I proved post form is no problem that the sample displayed from webpage.

But insert completed. the char is "??????"
It looks like ODBC problem. But no problem while posting Traditional Chinese.
So, it is the database's own translate problem.

At last, not only Simplify Chinese. Also Japanese, Korea
[3 Apr 2006 9:37] Vincent Cheung 
How can I migrate..........
[3 Apr 2006 23:48] Vincent Cheung 
Reminder Notice:

All tests are using UTF8 form post Simplify Chinese, Korea, Japanese.
To utf8_general_ci / utf8_bin / utf8_unicode_ci

NOT using GB2312, S-JIS, etc. to post to their original.
[4 Apr 2006 12:38] MySQL Verification Team 
Bug: http://bugs.mysql.com/bug.php?id=18578 was marked as
duplicate of this one.
[4 Apr 2006 13:33] MySQL Verification Team 
Inserting with QB

Attachment: chinese-char.PNG (image/png, text), 17.20 KiB.
[4 Apr 2006 13:40] MySQL Verification Team 
Thank you for the bug report. I installed my Windows XP with support
for complex Languages, started the server with default character set
utf8, created a table and inserted rows using some words from the
link you provided using QB. You can see the picture I attached.

Could you please test in the same way using QB, if it works then
the issue can be limited to the MyODBC driver or the asp code. Try
for example issuing a query set names utf8 before the insert.

Thanks in advance.
[5 Apr 2006 15:43] Vincent Cheung 
QB result by QB insert

Attachment: QB-result-by-QB-insert.gif (image/gif, text), 14.35 KiB.
[5 Apr 2006 15:44] Vincent Cheung 
This way I already tested. It seems no problem using QB insertion, right?
But have you use the browser to post/retieve data from database?
Be sure, if you use QB to insert char, it could be list by QB.
If you use web to load those data, it will display as "????". 
(Ref file: QB result by QB insert)
If you retrieve it via web using ASP. the result shown as "????"
(Ref file: load-via-Web-by-QB-insert.gif)

If you insert it via WEB, the result shown "????" also. If Load it from QB, the char shown as rubbish chars.
(Ref file: web-post-demo.gif, web-post-result.gif)

But actually UTF8-Traditional Chinese is not no problem at all.
Please see attached. The image dump from QB. All UTF8-TC data posted by web.
(Ref file: web-post-view-by-QB.gif)

I hope my post will help you developers to fix this.
[5 Apr 2006 15:45] Vincent Cheung 
load-via-Web-by-QB-insert

Attachment: load-via-Web-by-QB-insert.gif (image/gif, text), 27.30 KiB.
[5 Apr 2006 15:46] Vincent Cheung 
web post demo

Attachment: web-post-demo.gif (image/gif, text), 32.29 KiB.
[5 Apr 2006 15:47] Vincent Cheung 
web post result

Attachment: web-post-result.gif (image/gif, text), 52.96 KiB.
[5 Apr 2006 15:48] Vincent Cheung 
web-post-view-by-QB

Attachment: web-post-view-by-QB.gif (image/gif, text), 21.23 KiB.
[5 Apr 2006 15:50] Vincent Cheung 
File: web-post-view-by-QB.gif is post like File:QB-result-by-QB-insert.gif

Because it is already the result.
[5 Apr 2006 15:51] Vincent Cheung 
You may test it via this link.
http://www.nowinlove.com/express/tbupdate.asp
[5 Apr 2006 16:19] Vincent Cheung 
It is not only affect at my side. The following topics are talking about UTF8 problem.

http://forums.mysql.com/read.php?103,76426,76426#msg-76426
http://forums.mysql.com/read.php?103,15210,15210#msg-15210
http://forums.mysql.com/read.php?103,17168,17168#msg-17168
http://forums.mysql.com/read.php?103,51669,51669#msg-51669
http://forums.mysql.com/read.php?103,74335,74335#msg-74335
http://forums.mysql.com/read.php?103,73967,73967#msg-73967
http://forums.mysql.com/read.php?103,27224,27224#msg-27224
[5 Apr 2006 16:21] Vincent Cheung 
ref-table-collation

Attachment: ref-table-collation.gif (image/gif, text), 12.88 KiB.
[5 Apr 2006 16:22] Bogdan Degtyariov 
This is a bug of MyODBC Connector
[5 Apr 2006 16:22] Vincent Cheung 
ref-table-collation2 (for Table)

Attachment: ref-table-collation2.gif (image/gif, text), 22.25 KiB.
[7 Apr 2006 9:06] Vincent Cheung 
How about Version 4.x?
At least 2 years, it still not work.
[9 Apr 2006 22:35] Senol Kulbay 
I've lived the same char problem on Turkish and solved.

Try this code for yourself.

<%
  Session.CodePage=0
  Dim strConnection
  Dim Conn
  strConnection="driver={MySQL ODBC 3.51 Driver}; server=yourserver;uid=yourusername;pwd=yourpassword;database=yourdatabase;stmt=SET NAMES 'latin5';"
  Set Conn = Server.CreateObject("ADODB.Connection") 
  Conn.Open strConnection 
%>

Change latin5 with the appropriate one.
[10 Apr 2006 1:31] Vincent Cheung 
Dear Senol Kulbay,

Thanks for your kind help.
But actually the problem is in UTF8 (Universial common char) not a single country language. CodePage = 65001 must used, for documents you may find it from Microsoft.
http://windowssdk.msdn.microsoft.com/library/default.asp?url=/library/en-us/iissdk/html/26...

I tried the following code:

<%@ CodePage=0 Language="VBScript"%>
<% Option Explicit %>
<%
session.CodePage = 0
response.Charset = "utf-8"
set conn = server.CreateObject("adodb.connection")
%>
<%
'conn.open "nildb"
conn.open "DRIVER={MySQL ODBC 3.51 Driver};" & _
          "SERVER=ipaddress;" & _
          "DATABASE=dbname;" & _
          "USER=username;" & _
          "PASSWORD=password;stmt=SET NAMES 'utf8'"
%>

Please see the attached.
The problem could not be fix at this moment.
[10 Apr 2006 1:32] Vincent Cheung 
no-65001-add-stmt

Attachment: no-65001-add-stmt.jpg (image/pjpeg, text), 61.54 KiB.
[10 Apr 2006 1:32] Vincent Cheung 
add-stmt-only

Attachment: add-stmt-only.jpg (image/pjpeg, text), 65.37 KiB.
[10 Apr 2006 12:07] La La Lai 
Thank you very much Senol Kulbay! It works perfectly after I added:

stmt=SET NAMES 'gb2312'

All the Simplified Chinese are shown correctly! (Windows XP/MySQL 5/MySQL ODBC 3.51 driver/ASP)
[11 Apr 2006 1:12] Vincent Cheung 
That's great news!
[11 Apr 2006 1:25] Vincent Cheung 
For GB2312 is works fine I tested already.
But it didn't solve at my situation.

La La Lai... Congratulations for you!
[12 Apr 2006 3:11] Vincent Cheung 
I'm still looking for MySQL worldwide solution.
[25 Apr 2006 7:29] Vincent Cheung 
This problem comes from 3.51 not 5.0.
For 5.0, please refer to <a href="http://bugs.mysql.com/bug.php?id=19321">BUG#19321</a>
[23 May 2006 1:26] Vincent Cheung 
I will never use/test MySQL again.
I rather pay money to use MSSQL / Oracle. Bye Bye.
[13 Dec 2006 0:09] Arshad Khan 
Hello, 

I was able to sucessfully migrate data from my MS Access database to MySQL however I am facing a problem. 

Some records contain in my Access database have special characters and they turn in to illegible characters once migrated to MySQL. For example, this record Ọyọ turns in to this ?y?. 

I have chosen utf-8 table collation for MySQL at the time of migration. 

Can anyone help? We have been working on this for the last three days and it is driving us crazy. 

Cheers, 
Ash
[13 Dec 2006 1:12] Jess Balint 
Please use MySQL Connector/ODBC version 5.00.10 or later. Although version 5 of the ODBC Connector is still in the beta stage, it does include full Unicode support.
[10 Feb 2007 11:58] green su 
Hi Sir
Everything is fine when I Use ASP + myODBC v3 + Big5
In order to display Simply Chinese and Traditional Chinese words 
at the same web page. I update to myODBC v5 and transform big5 into UTF8.
I found  ...
Wher I use ADO in ASP and VB6 to read data , it displays not completely .
In phpadmin ,there are 5 words in the column but only display 2 chinese words
in the ASP and VB6
It seems there is a chr(0) after the text data to termilate the text..
This URL is my Screenshot
http://myweb.hinet.net/home5/showtime/utf8.htm

Thanks for answering my question..

MyODBC can write UTF8 from asp to MySQL now, that's so great.. but read different
[14 Feb 2007 15:35] green su 
My comment relates to this page ..
http://forums.mysql.com/read.php?37,139219,139219#msg-139219
[13 Aug 2007 15:37] Bogdan Degtyariov 
Simplified test case with the table dump

Attachment: 18731.zip (application/not-important, text), 1.81 KiB.
[13 Aug 2007 15:40] Bogdan Degtyariov 
Some results are displayed using the test VB script, but the hieroglyphs in MySQL Query Browser are not the same as in .asp page. Verified against MyODBC 3.51.19.
[23 Aug 2007 15:47] Karim Dahdah 
Hi,

The last three days we were trying (with Classic ASP) to write and read UTF-8 encoded strings from a MySQL 5.0 database. We use the 'MySQL Connector/ODBC v5' driver. Like you already said, writing to the database is not a problem; everything inserted is OK. But reading from it, gives gibberish at output.

Now I have found something strange...

This is the table setup:
CREATE TABLE `tbl_test` (
  `ID` bigint(20) NOT NULL auto_increment,
  `key_value` varchar(255),
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Writing a record:
INSERT INTO tbl_test (key_value) VALUES ('Héllô wörld!');

TEST A: Reading that record:
SELECT key_value FROM tbl_test WHERE ID=1;
-> Gives the result: Héllô wörld!
-> So, nothing wrong here

Now, I write a new record with some Chinese characters in it:
INSERT INTO tbl_test (key_value) VALUES ('Héllô 版权所有不得转载 wörld!');

TEST B: Reading that record:
SELECT key_value FROM tbl_test WHERE ID=2;
-> Gives the result: Héllô ???????? wörld!
-> Very strange, the characters é, ô and ö are displayed correctly, but not the Chinese characters.
-> When I look into the database with MySQL Query Browser, the Chinese characters are visible

Next test...

I change the column type of 'key_value' to 'text'

TEST C: Reading record 1:
SELECT key_value FROM tbl_test WHERE ID=1;
-> Gives the result: Héllô wörld!
-> Not good...

TEST D: Reading record 2:
SELECT key_value FROM tbl_test WHERE ID=2;
-> Gives the result: Héllô ç??æ?æ??æ??不å¾?转载 wörld!
-> Very strange, this gives a totally different result than TEST B!

Next, I tried experimenting with some functions in MySQL and I found this strange behaviour (the field 'key_value' is still a 'TEXT' type):

TEST E: Reading record 1:
SELECT CONVERT(key_value USING utf8) AS key_value FROM tbl_test WHERE ID=1;
-> Gives the result: Héllô wörld!
-> This is correct!

TEST F: Reading record 2 (with the Chinese characters in it):
SELECT CONVERT(key_value USING utf8) AS key_value FROM tbl_test WHERE ID=1;
-> Gives the result: Héllô ???????? wörld!
-> This is not correct! And the same happens with other languages: Greek, Russian, Korean, Arabic... you name it...

Best regards

Karim
[23 Aug 2007 16:05] Karim Dahdah 
I forgot to mention that I used the appropriate headers in ASP:

<%@ CodePage=65001 Language="VBScript"%>
<% response.charset = "utf-8"
response.codepage = 65001
%>

and the right MySQL connection string: SET NAMES utf8;

Greetz,

Karim
[23 Aug 2007 16:07] Karim Dahdah 
I just found another bug report about the same matter:
Bug #26744: http://bugs.mysql.com/bug.php?id=26744

Karim
[24 Aug 2007 8:55] Susanne Ebrecht 
Hi Karim,

the bug is not about ODBC version 5.0 it's about version 3.51.

If you find an ODBC 5.0 bug with the similar issues than yours, please write your issues down there and not here.

Version 5.0 is still beta and we recommend to use the newest 3.51 version. at the moment it is 3.51.19.

Regards,

Susanne
[6 Sep 2007 14:38] Bogdan Degtyariov 
The bug still affects MyODBC 3.51.19
[10 Jan 2008 18:49] Jess Balint 
Unicode is not supported in ADO applications (including ASP) with the Connector/ODBC 3.51 driver. Please consider testing MySQL Connector/ODBC 5.1 which includes Unicode support for ASP.
[1 Dec 2009 3:56] simon riedel 
SQL Dbase

Attachment: 12345.JPG (image/jpeg, text), 35.29 KiB.
[1 Dec 2009 3:59] simon riedel 
(double post above, sorry!) 

Problem still exists? 

I have same problem with: 

MySQL Server 5.0
MySQL ODBC Connector 5.1
MS Access 2003
[1 Dec 2009 7:44] Tonci Grgin 
Simon, I don't follow you... You mean there is a bug in Access ;-)

Seriously, at least you should tell us about DSN used for Access and table structure as well as charsets used for server/client connection/database and table...
[11 Dec 2009 9:19] simon riedel 
Toncil, 
I apologize! 
I was under big pressure to geht this application running. I tried very long and finally was convinced to have found a bug. 
After some days off I realized today that I just made a mistake in connecting to the Server. 
Apologies again for my behavior ! 

(My Idea was that MySQL ODBC Connector would not support Asian Language)

Simon
[11 Dec 2009 9:55] Tonci Grgin 
Simon, no problem.

So it's working now?