Hi,
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 aftｅｒ 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
objRS.Close
Set objRS = Nothing
objConn.Close
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
objText.Close
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>"
Next
Response.Write "</tr>"
Next
%>
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)