Classic ASP, MySQL and UTF-8

Posted 3 January 2013 11 March 2014

This post is more than 12 years old.

Countless references on the web will tell you that you can access Unicode data in MySQL by setting the connection character set to utf8. Mostly, they tell you to execute the statement “set names ‘utf8′” as the first statement in your connection. But when you are accessing MySQL from Classic ASP using the MySQL ODBC driver, you get an error: “SET NAMES not allowed by driver”. Here’s how you actually do it in Classic ASP.

First, here’s a run down of what’s needed to get Unicode data from MySQL onto a Classic ASP web page as UTF-8:

tell Classic ASP that the page will be encoded in UTF-8 — the response codepage does that
tell the web server to add charset=UTF-8 to the content header — the response charset does that
tell browsers that the content is encoded with UTF-8 — use an HTML meta element for that
connect to MySQL with a Unicode encoding

That last one is the trick. Reading the MySQL documentation for the ODBC driver, you might be tempted to add a connection parameter of “charset=utf8” to the database connection string. That does in fact return the data from MySQL as UTF-8. Unfortunately, VBScript just doesn’t understand a stream of UTF-8 data and will give you a nasty mess of corrupted characters where your nice non-ANSI characters should be.

The trick is simple: VBScript does understand 2-byte Unicode characters. To get your nice Unicode data into VBScript strings, you just need to add a connection parameter of “charset=ucs2”. The data will then come across as 2-byte Unicode, and your Classic ASP page will encode and transmit it as UTF-8.

And here’s a simple code example:

<%@ language="vbscript" codepage="65001" %>
<%
Response.CharSet = "utf-8"
%>
<!DOCTYPE html>
<html>

<head>
<meta charset="utf-8">
<title>test MySQL UTF-8</title>
</head>

<body>

<%
Dim db_conn_string, db_user, db_pwd, db
Dim sql, rs

db_conn_string = "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=test;charset=ucs2;Option=3;"
db_user = "scott"
db_pwd = "tiger"

Set db = Server.CreateObject("ADODB.Connection")
db.Open db_conn_string, db_user, db_pwd

sql = "select * from testutf8"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, db, adOpenForwardOnly, adLockReadOnly
While Not rs.EOF
	Response.Write "<p>Name: " & Server.HTMLEncode(rs("Name")) & "</p>" & vbCrLf
	rs.MoveNext
Wend
%>

</body>
</html>

Job is done in any language (well, the characters that fit into both the UTF-8 and UCS2 universes at least!)

Snippets