Wrox Programmer Forums
Classic ASP Professional For advanced coder questions in ASP 3. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Professional section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 18th, 2006, 02:40 AM
Registered User
Join Date: Jul 2003
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default asp utf8 for mysql, utf16 for winXP :(


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#65292;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#65349;#65362; 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 = "#19968;#20154;"

'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)

Similar Threads
Thread Thread Starter Forum Replies Last Post
FileSystemObject - UTF8 File Format itHighway Classic ASP Databases 0 May 25th, 2006 09:14 PM
UTF16 in ASP.NET muthukumar Oracle 0 November 8th, 2005 04:48 AM
utf8 support in PHP 4.3.11 jaya_76 Pro PHP 3 October 4th, 2005 06:00 AM
How to convert UTF8 code to character ghostnguyen C# 1 July 18th, 2005 05:28 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.