I've read many threads on Stack and other forums, but still can't figure how to make it work.
The configuration
- I have a MySQL 8.0 database named test, and configured it with default CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci.
- Inside there's a table creatively named table, this too configured as utf8mb4 and utf8mb4_0900_as_ci.
- The only field is named field and is a blob type, where I write and read encoded data using AES_ENCRYPT/DECRYPT
- VBScript classic connect to it using the last ODBC 8 UNICODE driver
- I've tried the SET NAMES statement but is not supported in this ODBC version. Instead the charset is included directly in the connection string.
Following other threads, I made a test file
- without any external include
- saved as UTF8
- specified all the possible Vbscript Codepage and Charset at the beginning of the file
- the connection string specifies a charset, I've tried both ucs2 and utf8mb4
- the html segment specifies the utf charset both in the Content Type and in the form
- when reading from the database I specify to CONVERT USING utf8mb4
The page is setup to be the simplest test possible, with a form on top, and the results listed below.
All the simple text is processed correctly, while any emoji is read as ?
For example, if this is the text passed through the form -> hello
this is what is returned -> hello ? ? ? ? ? ? ?
Executing the same SELECT query in Workbench 8, shows the correct text with the emoji, so it's not a problem directly related to the Codepage or Charset, or when it writes the data, but only when it read them.
VarType report the RS("Field") as a simple string / 8.
I've spent the last days studying and testing all the possible solutions, but can't solve it :/
<%@Language="VBScript" CodePage="65001"%>
<% Option Explicit %>
<%
' THE PAGE ENCODING
Response.ContentType = "text/html;charset=UTF-8"
Session.CodePage = 65001
Response.CodePage = 65001
Response.CharSet = "UTF-8"
'----------------------------------------------------------------
dim dbConn, sql, RS
' SIMPLE CONNECTION STRING
' NOTE THAT I'VE TRIED BOTH charset=ucs2 AND charset=utf8mb4
Set dbConn = Server.CreateObject ("ADODB.Connection")
dbConn.Open "DRIVER={MySQL ODBC 8.0 UNICODE Driver}; SERVER=127.0.0.1; PORT=3306; DATABASE=test; Uid=user; Pwd=password; charset=ucs2;"
'----------------------------------------------------------------
%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>test MySQL UTF-8</title>
</head>
<body>
<form action="?action=insert" method="post" accept-charset="utf-8">
<textarea name="text"></textarea>
<input type="submit" value="Insert">
</form>
<%
'----------------------------------------------------------------
' INSERT THE STRING WHEN THE FORM IS SUBMITTED
If Request.Querystring("action") = "insert" Then
sql = "INSERT INTO table (Field) VALUES(AES_ENCRYPT('" & Request.Form("text") & "', 'AES_Key'))"
Response.Write(sql & "</br>")
dbConn.execute(sql)
End If
'----------------------------------------------------------------
' LIST ALL THE RECORDS FROM THE TABLE
sql = "SELECT CONVERT(AES_DECRYPT(Field, 'AES_Key') USING utf8mb4) AS Field FROM table"
Set RS = dbconn.execute(sql)
Do Until RS.EOF
Response.Write(RS("Field") & "</br>")
RS.MoveNext
Loop
RS.close
dbConn.close
%>
</body>
</html>
