MySQL Forums
Forum List  »  Chinese

ASP utf8 (mysql) utf16 (winxp) conflict
Posted by: Kit Kit
Date: September 17, 2006 10:52PM


I am having problem regarding utf8 (used by MySQL) and utf16 (used by WinXP)

I want to store the CJK string into both MySQL and use the string to create a new file name.
The problem here is,if the string is in utf8, MySQL can accept it, but the filename will have strange character.
If the string is in utf16 format, MySQL will store as '???', and the filename will be created correctly in WinXP.

I have attached my code. Notice that, If I set
@CodePage=65001 'UTF-8'
WinXP/IIS will convert all the UTF8 string in the asp file to UTF16 for internal processing.
MySQL can only accept utf8 for client connection, so after UTF16 converted to UTF8, ??? will appear.

If I set
@CodePage=1252 or simply remove this line (default is 1252)
MySQL can store and retrieve correctly, BUT filename will be some weird characters.
WinXP can display double byte Unicode, not UTF8 (Multibyte Unicode), in this case, 3 bytes per cjk character.

I have no problem when using MS Access, as it is using UTF16, but changing to MySQL drives me crazy.
Any Solution for this?
Thanks in advance.

My system:
WinXP (english version),
MySQL 5.0
The database and table I created is innodb in utf8 format.
The ASP file is saved as utf8 format.

<%'@ CodePage=1252 '65001 %>
Option Explicit

Dim strConn, objConn
Dim strSQL, objRS, arrRS
Dim intRow, intRowMax, intCol, intColMax
Dim objFSO, objText
Dim strFileName

strFileName = "一人"

'Insert CJK character into MySQL'
strConn = "Driver={MySQL ODBC 3.51 Driver}; Server=localhost; CharSet=utf8; Port=3306; Option=0; Socket=; Stmt=; Database=zzz; Uid=xxx; Pwd=yyy;"
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConn
objConn.Execute "SET names utf8;"

strSQL = "INSERT INTO tblUser SET `Name` = '" & strFileName & "';"
objConn.Execute strSQL

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "SELECT * FROM tblUser;"

arrRS = objRS.GetRows

Set objRS = Nothing
Set objConn = Nothing

'Create filename using CJK'
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
Set objText = objFSO.CreateTextFile("c:\" & strFileName & ".txt", True, -1) 'Unicode = -1 & do not use Server.MapPath
Set objText = Nothing
Set objFSO = Nothing

'Display the result'
Response.CodePage = 0
Response.CharSet = "utf-8"

Response.Write "<head>"
Response.Write "<meta http-equiv=""content-type"" content=""text/html; charset=UTF-8"">"
Response.Write "</head>"

Response.Write "<table border=""1"">"
For intRow = 0 To intRowMax
Response.Write "<tr>"
For intCol = 0 To intColMax
Response.Write "<td>" & arrRS(intCol, intRow) & "</td>"
Response.Write "</tr>"

mysql> show variables like 'char%';
| 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 | latin1 |
| character_set_system | utf8 |
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\ |
8 rows in set (0.05 sec)

mysql> show variables like 'coll%';
| Variable_name | Value |
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | latin1_swedish_ci |
3 rows in set (0.00 sec)

Options: ReplyQuote

Written By
ASP utf8 (mysql) utf16 (winxp) conflict
September 17, 2006 10:52PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.