Perl, C++, mysql and UTF8

Recently I finished with Russian translation of LinuxMCE UI. But as I know now it was the easiest part. After addition Russian descriptions in the database I faced with problem of displaying them. I made a research and found following. To have UTF8 data in the mysql database a few steps should be done:

  • default charset for server should be set to utf8 in the /etc/mysql/my.cnf:
    [mysqld]
    default-character-set=utf8
  • default charset for client should be set to utf8 in the /etc/mysql/my.cnf:
    [client]
    default-character-set=utf8

    After that the charset for mysql shell will be UTF8 but not latin1 as in the stock version.
  • default charset for desired database should be set to utf8:
    alter database pluto_main charset=utf8;
  • default charset for desire table should be set to utf8:
    alter table Text_LS charset=utf8;
  • default charset for all text fields in the table should be also set to utf8:
    alter table Text_LS modify column `Description` longtext CHARACTER SET utf8 COLLATE utf8_general_ci
  • the client application should pass UTF8 flag to tell mysql about charset for connection. Here is a Perl example:
    my $dbh = DBI->connect("dbi:mysql:pluto_main;host=localhost", "root") or die "Cannot connect to database: $DBI::err!";
    $dbh->{'mysql_enable_utf8'} = 1;
    $dbh->do('SET NAMES utf8');

To display UTF8 text in the Perl script just set UTF8 charset for STDOUT:
binmode STDOUT, ":utf8";

But with LinuxMCE the situation is more complicate. Its UI is developed on C++. So, After a googling I found the way to set UTF8 for connection. There is a function mysql_options() in the Mysql C API. It should be called after mysql_init() but before mysql_connect() or mysql_real_connect() and allows to set desire charset:
mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, 'utf8');
and run some SQL statement when connecting to the MySQL server:
mysql_options(&mysql, MYSQL_INIT_COMMAND, 'SET NAMES utf8');
But it didn’t help. The Russian text is still displayed as question mark. So, have to dig LinuxMCE code to solve that. Otherwise the Russian translation won’t be added.

Published by

Michael Stepanov

Site owner and admin :)

One thought on “Perl, C++, mysql and UTF8”

Leave a Reply

Your email address will not be published. Required fields are marked *