0

I have been stuck up in this scenario.

My application is in C++ which connects to MySQL database 5.5.34 through MySQL odbc connector v5.2 Unicode Driver.

My tables were using the character set and collation properties 'utf-8'. To insert supplementary unicode characters, i changed it via

My table was initially created:

CREATE TABLE mytable (SAMPLECOLUMN text) ENGINE=InnoDB DEFAULTCHARSET=utf8;

changed to,
ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

After this, I am able to directly (not through my app) insert the supplementary characters into the column holding 'TEXT' data type.

The odbc connector crashes when it encounters supplementary characters.

The following are some of my my.ini configurations.

[mysql]
default-character-set=utf8

[mysqld]
character-set-server=utf8
default-storage-engine=INNODB

The query that i am trying to insert:

INSERT INTO mytable (SAMPLECOLUMN) VALUES ("乕乭乺丕");

any pointers/heads up would be great.

Also, tried using v5.3 Unicode MYSQL odbc connector and v1.0.5 MariaDB ODBC connector, still the same.

Thanks in advance.

D3XT3R
  • 181
  • 2
  • 15
  • found this one in mysql forums another user with the same problem http://bugs.mysql.com/bug.php?id=67297 – D3XT3R Jan 13 '16 at 05:17

2 Answers2

4

The workaround here is to use MariaDB odbc connector to connect to MySQL database. https://mariadb.com/my_portal/download/connector-odbc/1.0

Make sure you set this in ODBC dsn and keep Connection Character Set empty

MariaDB connector

Starting v5.2 of MyODBC, SET NAMES has been deprecated. Hence the need to use MariaDB connector. Hope the supplementary character issue is fixed in MySQL odbc connector.

D3XT3R
  • 181
  • 2
  • 15
  • Incredible! It works. Thank you so much. Finally I can load my 4-byte Unicode texts from MySQL via ODBC! https://apps.timwhitlock.info/unicode/inspect?s=%F0%9F%98%83 – Kim Homann Nov 22 '19 at 07:56
0

You need to issue SET NAMES utf8mb4 after connecting to MySQL. Otherwise, the connection cannot handle the 4-byte characters.

If that is not sufficient, then chase down SQLExecDirectW -- see if there is an update to it.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • MySQL connector does not allow me to `SET NAMES utf8mb4` through `initstmt` parameter in ODBC – D3XT3R Jan 16 '16 at 17:45