1

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

The MySQL Database is set up as follows:

  • Database charset: utf8mb4
  • Database collation: utf8mb4_general_ci

The table and field:

  • Character Set: utf8mb4
  • Collation: utf8mb4_general_ci

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:

  • MySQL ODBC 5.1 Driver
  • MySQL ODBC 8.0 Unicode Driver
  • MySQL ODBC 8.0 ANSI Driver

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

4532066
  • 2,042
  • 5
  • 21
  • 48
  • 1
    Using `Response` alone is not enough, the ASP page needs to be saved in the correct encoding and IIS has to be told to read it as UTF-8. See [this answer](https://stackoverflow.com/a/21914278/692942). – user692942 Nov 19 '19 at 23:03
  • @Lankymart - thanks for your reply. The page is saved as UTF-8, I have added a screenshot to confirm as an edit at the end of my question. – 4532066 Nov 20 '19 at 06:19
  • Would recommend going through [these steps](https://stackoverflow.com/a/34356187/692942) and checking each on off. If after following these it still doesn’t work I would look at the driver you are using as well. – user692942 Nov 20 '19 at 06:54
  • Thanks @Lankymart - I have set this at the very top of the page: `<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>` and set the response.codepage and charset as per your advice on the answers you've linked to. No joy. I've tried ODBC 8, 5.1 and 3.51, none of them do the trick. I've tried connection string methods, and using a System DNS with Charset set and initial statement set to `set names utf8mb4` or `set names 'utf8mb4'`, nothing works... I think I'll have to give it up! – 4532066 Nov 21 '19 at 22:28
  • Sorry to hear that, it does sound as it maybe a MySQL Driver issue but I don’t have enough experience using MySQL to best advise. But maybe [this will help](https://stackoverflow.com/a/14015499/692942), good luck. – user692942 Nov 21 '19 at 22:41
  • @4532066 I'm having exactly the same problem and I've gone through all the suggested solutions, but even if the data is stored correctly in the db (Driver 8.0 UNICODE), when asp retrieves it, it shows only a "??" for each emoji. Have you found a solution? :/ – Fehu Mar 29 '21 at 13:20
  • 1
    @Fehu I never got it working. In the end I used PHP for the pages where I needed to extract data that might include emoji data. Sorry I have no solution. – 4532066 Mar 29 '21 at 17:55

1 Answers1

1

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.

Daniel Nordh
  • 362
  • 3
  • 15