QT-Database classes and UTF8 charsets in mysql

[Wurgl]

I am wondering how to use UTF8 charset correct with mysql.

I have a database which looks like

mysql> show create database Testdb;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| testdb   | CREATE DATABASE `Testdb` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+

mysql> show create database Testdb;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| testdb   | CREATE DATABASE `Testdb` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+

To copy to clipboard, switch view to plain text mode 

Then I have a table which looks like this one:

mysql> show create table Source;
+--------+--------------
| Table  | Create Table
+--------+----------------
| Source | CREATE TABLE `Source` (
  `title` varchar(255) character set utf8 collate utf8_bin default NULL,
...
  `id` mediumint(9) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+--------+--------------

mysql> show create table Source;
+--------+--------------
| Table  | Create Table
+--------+----------------
| Source | CREATE TABLE `Source` (
  `title` varchar(255) character set utf8 collate utf8_bin default NULL,
...
  `id` mediumint(9) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+--------+--------------

To copy to clipboard, switch view to plain text mode 

So wherever I can, there is information to use UTF-8 as the character set.

When inserting german umlaut chars, I see those in the database with the expected coding. However, when I retrieve records with those umlauts, I see garbage characters in my Qt-Application instead of those umlauts.

So far I did not find any way to tell the Qt-Classes which charset the data from the queries holds, however I expected that the classes retrieve that information.

As it seems, I have set various flags for the database itself also to uft8

mysql> show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

mysql> show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

To copy to clipboard, switch view to plain text mode 

What did I miss? Any hints?

Thanks

[yakin]

Is your application running in utf8 charset. (echo $LANG on X; on Windows, it should be UTF8)

You may try the following. I am not shure, if that helps?

QTextCodec::setCodecForCStrings(QTextCodec::codecForName("UTF-8"));
      QTextCodec::setCodecForLocale(QTextCodec::codecForName("UTF-8"));

QTextCodec::setCodecForCStrings(QTextCodec::codecForName("UTF-8"));
      QTextCodec::setCodecForLocale(QTextCodec::codecForName("UTF-8"));

To copy to clipboard, switch view to plain text mode 

[Wurgl]

In general I have no problem with whatever chars, even chinese chars work ... although I do not understand what they mean :-)

But until now, I used my own class to retrieve databases, but I would like to use as much as I can from Qt.

For example this code:

QStandardItem *item = new QStandardItem();
        item->setData("Ue: Ü", Qt::DisplayRole);

QStandardItem *item = new QStandardItem();
        item->setData("Ue: Ü", Qt::DisplayRole);

To copy to clipboard, switch view to plain text mode 

shows up as expected. Even in the same QTreeView.

[The Storm]

When getting the data from the query use QString::fromUtf8() on the utf8 data only and you will get correct results.

P.S. I'm just guessing, I have never tried that myself.

[Wurgl]

You mean something like (untested):

QSqlRecord record = query.record();
QString myValue = QString::fromUtf8(record.value(0).toString().toAsc ii());

looks really strange :-D

I thought that the database class finds the character set from the table definition, as it finds the names of columns, the types and all this stuff.