[odb-users] inserting chinese characters into mysql error (utf8mb4 and utf8 are tried)

[Sten Kultakangas]

Hi

\xD6\xD0\xB9\xFA doesn't look like an UTF8-encoded 中国
Please double check whether the strings in your source file are actually
UTF-8 encoded, not e.g. big5-encoded. Use a hex viewer like builtin in
notepad++ or Midnight Commander.

UTF8 for 中国 should be \xE4\xB8\xAD\xE5\x9B\xBD

Best regards,
Sten Kultakangas

On Thu, Aug 1, 2019 at 2:57 PM 不明真相 <icewill9999 at vip.qq.com> wrote:

> When i insert some chinese characters into mysql  via odb, i got the error
> message "Incorrect string value: '\xD6\xD0\xB9\xFA' for column 'NATION' at
> row 1", i know it's the wrong setting of character set, i tried my efforts
> to fix the issue but all in vain.
> firstly i used odb2.4, mysql 8.0 mysql connector c 6.1.11 and vs2015 in my
> project, i tried using utf8mb4 charset, below is my configuration file:
> [mysqld] character-set-server = utf8mb4 collation-server =
> utf8mb4_general_ci init_connect='SET NAMES utf8mb4'
> skip-character-set-client-handshake = true [mysql]
> default-character-set=utf8mb4 [client] default-character-set=utf8mb4and it
> seems nothing wrong with the mysql server:     mysql> SHOW VARIABLES WHERE
> Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
>    +--------------------------+--------------------+     | Variable_name
>         | Value              |
>  +--------------------------+--------------------+     |
> character_set_client     | utf8mb4            |     |
> character_set_connection | utf8mb4            |     |
> character_set_database   | utf8mb4            |     |
> character_set_filesystem | binary             |     |
> character_set_results    | utf8mb4            |     | character_set_server
>    | utf8mb4            |     | character_set_system     | utf8
>    |     | collation_connection     | utf8mb4_general_ci |     |
> collation_database       | utf8mb4_general_ci |     | collation_server
>    | utf8mb4_general_ci |
>  +--------------------------+--------------------+          mysql> show
> create table basininfo;
>  ------------------------------------------------------------------------------------------------+
>             | basininfo | CREATE TABLE `basininfo` (           `ID` int(11)
> NOT NULL AUTO_INCREMENT,           `BASIN_NAME` varchar(1000) DEFAULT
> NULL,           `NATION` varchar(1000) DEFAULT NULL,             PRIMARY
> KEY (`ID`)         ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
> the code piece induced the error is below:     auto_ptr<odb::database> db(
>           new odb::mysql::database(         "news"     // database login
> name             , "news123" // database password        , "newsdb" //
> database name             , "localhost"           , 3306          ,
> nullptr       ,"utf8mb4"     ));           transaction t(db->begin());
>                BasinInfo binfo;     binfo.setNation("中国"); //chinese
> character insert into column 'Nation'     db->persist(binfo);
>  t.commit();after that, i changed the mysql server to 5.7, replace the
> connector c to the libraray and include files that bundled with mysql 5.7,
> and rebuild the odb-libmysql, but that effort does not do any help. finally
> i changed the charset to utf8 and recreate the database, but problem
> remains the same. i also start a post on stackoverflow at
> https://stackoverflow.com/questions/57283107/problem-when-inserting-chinese-characters-into-mysql-via-c-odb,
> for now i dont get the problem solved!do you have any idea about how to fix
> it, thanks!