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:
  • default charset for client should be set to utf8 in the /etc/mysql/my.cnf:

    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.