CODEWITHSUNDEEP
mysqlasp-classicodbc
4532066
4532066

Reputation: 2110

Emoji data retrieved via ODBC Connection appears as question mark

I connect to MySQL version 5.5.50-log from classic ASP pages.

The MySQL Database is set up as follows:

The table and field:

To test, I have data in the table, which contains this string: 🍔(T_T) é, è, à, ç

This is a screenshot of the data from the SQLyog:

enter image description here

This is my test web page:

	<!DOCTYPE html>
	
	<html lang="en">
		<head>
			<meta charset="utf-8">
			<meta http-equiv="X-UA-Compatible" content="IE=edge">
			<meta name="viewport" content="width=device-width, initial-scale=1.0">
			<title>Test</title>
			<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">
		</head>
		<body>
		<p>Pasted directly from database field: 🍔(T_T) é, è, à, ç</p>
		<p>Returned from SQL statement: ?(T_T) é, è, à, ç</p>
		</body>
	</html>

The issue is not that emoji data cannot be displayed on the web page, as the directly pasted content from MySQL appears fine. The issue is that once the data is returned from an SQL Select from MySQL via the ODBC Driver, it does not render correctly.

I have the following set in the ASP code:

Response.CodePage = 65001
Response.CharSet = "utf-8"

I have tried other variations - e.g. 

Response.CodePage = 1252
Response.LCID = 1060
Response.Charset = "utf-8"

But they made no difference.

I have tried a range of MySQL ODBC Drivers - e.g. 

''----------------------
''connection string
''----------------------

Dim oConn
set oConn = Server.CreateObject("ADODB.Connection")
'oConn.Open "DRIVER={MySQL ODBC 5.1 Driver}; Server=localhost; Database=mydb; User=root; Password=testing; Option=3; CharSet=utf8mb4; stmt=SET NAMES 'utf8mb4';"

That doesn't work...

I have tried using a System DSN instead using these drivers:

None of them solve the issue.

I wondered if there is any way around this, or if there is basically no way to display emoji data that is retrieved from a MySQL database using Classic ASP?

As far as I know, from reading elsewhere, the MySQL database is set up correctly, in a way which supports storing emoji characters, confirmed by the fact I can view it stored in the database without an issue. It's just when I try and pull it out of the database via the Classic ASP to MySQL connection that the emoji character is not displayed.

Edit - the ASP page itself is saved as a UTF-8 encoded file:

enter image description here

Upvotes: 2

Views: 512

Answers (2)

AlexLaforge
AlexLaforge

Reputation: 532

Even with a MySQL/MariaDB database fully in utf8mb4 with collation utf8mb4_unicode_ci, there is a bug in a lot of ODBC drivers preventing to READ BACK DATA, making out-of-BMP characters (like Emoji) appear as question marks. Such bugs are referenced here :

Here are 2 possible solutions to finally READ back Emojis from database, in Classic ASP (but can apply to other languages) :

  1. Run MySQL v8 and upgrade to ODBC Connector 8.0.27 at least. Before this ODBC version, the bug is still here, and as stated by MySQL, the ODBC connector (even in version 8) does not allow SET NAMES ... in the initial statement, making it impossible to use initstmt=set names 'utf8mb4' in the connection string as a workaround.

  2. Run MariaDB 10.3 +. Conversely to MySQL, MariaDB DOES allows SET NAMES ... in the initial statement ! That's great, because you can now use initstmt=set names 'utf8mb4';charset=utf8 (yes, do not specify utf8mb4 here as the charset, but use utf8 instead, as this encoding is used as the transport character set to communicate with the server). Use the MariaDB ODBC 3.1.20 Driver. And obviously, as stated by Mighty Chaffinch in this thread, your ASP page must be saved as UTF-8 in your text editor, and need to include :

Session.CodePage = 65001;
Response.ContentType = "text/HTML";
Response.CodePage = 65001;
Response.Charset = "UTF-8";

Happy Classic ASP !

Upvotes: 0

Daniel Nordh
Daniel Nordh

Reputation: 390

On top of setting the responses and saving the file as UTF-8, you may also need to set the page language like this. Note that this should be at the very top of the file before anything else:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%
response.codepage = 65001
response.charset = "utf-8"
%>

I've fought a lot with encoding and half our system is made up of non-UTF files and this code right here is what has made me able to work with UTF-8 for my own functions.

Upvotes: 1

Related Questions