1

Let me preface this question by saying that I know that this subject is tackled from various angles in numerous posts. However, I have spent hours and hours on this and still cannot get it to work, so asking for help.

I have some chinese characters in cells in an Excel sheet. I am using VBA to export the content of these cells into a mysql table. From the mysql command line, I am able to insert the chinese characters into the table successfully (I am using utf8mb4 charset).

I have also set charset=utf8mb4 in the VBA connection string.

However, I cannot get VBA to include the chinese characters in the query string, which is as follows:

    SQLStr = "INSERT INTO " & VBA.Trim(databaseName) & ".translationTable" & " VALUES ('" & _
    timestamp & "','" & _
    Replace(Worksheets("translationTable").Range("IORange").Cells(Counter, 2), Chr(10), "") & "','" & _
    Replace(Worksheets("translationTable").Range("IORange").Cells(Counter, 3), Chr(10), "") & "','" & _
    Replace(Worksheets("translationTable").Range("IORange").Cells(Counter, 4), Chr(10), "") & "','" & _
    Replace(Worksheets("translationTable").Range("IORange").Cells(Counter, 4), Chr(10), "") & "')"
    rs.Open SQLStr, oConn

I know that VBA editor will not display the chinese characters in any case. However, chinese characters inserted from VBA are showing up as ?? in the database as well.

It seems that something that I am doing is converting the chinese characters to ASCII, before the INSERT statement is executed.

What am I doing wrong?

MNM
  • 37
  • 7

1 Answers1

1

The usual cause of multiple question marks:

  • you had utf8-encoded data (good)
  • SET NAMES latin1 was in effect (default, but wrong)
  • the column was declared CHARACTER SET latin1 (default, but wrong)

If those tips don't suffice, then show us what is in the tables, using something like

SELECT col, HEX(col) FROM tbl...

Chinese cannot be "converted to ascii".

Addenda

Try setting the DNS's 'Connect Options'->'Initial Statement' to 'SET NAMES utf8mb4'. -- MySQL ODBC 3.51 Driver UTF-8 encoding

Community
  • 1
  • 1
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • SET NAMES is not allowed by ODBC/Mysql driver (i am using 5.5.25). Instead I have set charset in connection string (as mentioned above), however I do not know if it is sufficient.. The problem is not on the database side as when I insert chinese characters via MySQL workbench it works. The hex of the chinese characters when inserted via Workbench is E4BAA4E4BB98 while from VBA is it 3F3F. – MNM Feb 01 '16 at 07:28
  • What is the version number of your Connector/ODBC ? – Rick James Feb 01 '16 at 17:33
  • Unicode is not supported in ADO applications (including ASP) with the Connector/ODBC 3.51 driver. Please consider testing MySQL Connector/ODBC 5.1 which includes Unicode support for ASP. -- http://bugs.mysql.com/bug.php?id=18731 – Rick James Feb 01 '16 at 17:53