[SOLVED] How to force QSqlTableModel to use UTF-8 encoding?

soroush

Hi

I'm using QSqlTableModel / QTableView to represent tables of an existing MySQL database. Everything works good else than unicode strings. They are interpreted as ASCII characters and not shown correctly. When fetching records from database with QSqlRecord, I use UTF-8 decoder to obtain UTF-8 encoded QString like this:
@
// Open MySQL database
QSqlQuery query("SELECT * from sellers");
QTextDecoder *decoder = QTextCodec::codecForName("UTF-8")->makeDecoder();
QString uid = decoder->toUnicode(query.value(i_uid).toByteArray());@
How do I do same thing for QSqlTableModel?
Also I'm not sure my above code is good enough. Is there a better way (probably application-wide) to force application to use a specific encoding for strings?

loladiro

Two questions:

What version of MySql are you using?

Did you set QTextCodec::codecForLocale()?

soroush

5.1.54
No! what should I do?
Update: I tried
@QTextCodec::setCodecForLocale(QTextCodec::codecForName("UTF-8"));@
before instantiating my MainWindow but doesn't help.

loladiro

Of what MySQL type is the field (are the fields) you are trying to read?

soroush

They are VARCHAR(50) and MEDIUMTEXT.
Here is the create script of a table:
@
CREATE TABLE customers (
id smallint(5) unsigned NOT NULL AUTO_INCREMENT,
name varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
last varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
phone bigint(20) unsigned zerofill NOT NULL,
address mediumtext CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci@

loladiro

Ok. what does query->data().toString() give you? (Sorry for the amount of question, I was going through the MySQL driver source, and it seems, that it automatically stores it as Unicode in QString).

soroush

query->data().toString() gives me a wrongly decoded string. For example for a Persian name like شرکت نرم‌افزاری مرویٰ query->data().toString() returns a QString containing:
Ø´Ø±Ú©Øª ÙØ±ÙâØ§ÙØ²Ø§Ø±Û ÙØ±ÙÛÙ°
instead of correct string.

loladiro

May I ask what tool you used to insert your data into the DB?

jim_kaiser

The charset can normally can be set at table, db or server. I think.. you might also need to create your database with the charset. I remember having a similar issue in my web app..

@ CREATE DATABASE mydb DEFAULT CHARACTER SET utf8 @

Some info in this "link":http://www.phpwact.org/php/i18n/utf-8/mysql..

loladiro

I actually tested it and everything works fine as long as one uses Qt to write the table.

dangelog

I would check the MySQL Qt plugin source code -- conversion between QString and VARCHAR is supposed to be 100% automatic (see [[doc:sql-types]] in the docs).

loladiro

That's what I did. And it is (well as long as you don't somehow get the binary flag involved).

EDIT: Seems as if the binary flag is also set if you use the uft8_bin collation.

soroush

bq. May I ask what tool you used to insert your data into the DB?

MySQL admin and QueryBrowser. But does it matter?

bq. I actually tested it and everything works fine as long as one uses Qt to write the table.

Yes. I think it's not a database issue, database just stores strings with given encoding. It's all about QVariant. there should be some way to tell QVariant how to interpret binary data to create strings. and actually there is a way (QTextCodec) but I can't put things together to work!

I'm not sure but It seems to work if a subclass of QSqlTableModel used and text edit delegate (or something like that) replaced with one uses encoding...

I saw some examples of QTableView / Model that cells of tables are replaced with customized or completely new widgets.

dangelog

[quote author="soroush" date="1308747079"]bq. May I ask what tool you used to insert your data into the DB?

MySQL admin and QueryBrowser. But does it matter?

bq. I actually tested it and everything works fine as long as one uses Qt to write the table.

Yes. I think it's not a database issue, database just stores strings with given encoding. It's all about QVariant. there should be some way to tell QVariant how to interpret binary data to create strings. and actually there is a way (QTextCodec) but I can't put things together to work![/quote]

It's NOT QVariant. If the QVariant returned by a query is wrapping a QString, the encoding conversion was already done for you by the SQL driver (or by whatever put the QString there). If you're 100% sure that the data stored in the DB is correct, you should try to debug the plugin itself.

[quote]I'm not sure but It seems to work if a subclass of QSqlTableModel used and text edit delegate (or something like that) replaced with one uses encoding...
[/quote]

This may actually be a workaround (that is: a subclass which overrides data/setData and performs another encoding conversion there).

loladiro

The problem is that you use utf8_bin which set the BINARY_FLAG, which disables the automatic conversion to Unicode.

soroush

[quote author="loladiro" date="1308747478"]The problem is that you use utf8_bin which set the BINARY_FLAG, which disables the automatic conversion to Unicode.[/quote]

I tried everything, I promise! with and without binary flag there is no difference. for example this table is shown wrong :
@CREATE TABLE customers (
id smallint(5) unsigned NOT NULL AUTO_INCREMENT,
name varchar(50) CHARACTER SET utf8 NOT NULL,
last varchar(50) CHARACTER SET utf8 NOT NULL,
phone bigint(20) unsigned zerofill NOT NULL,
address mediumtext CHARACTER SET utf8 NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 @

loladiro

I used the following test program and it worked (your table):
@
QSqlQuery query("SELECT * from test2");
QSqlQuery query2(QString("INSERT INTO test2 VALUES("%1","%2","%2","%1","%2")").arg(QString::number(0),QString::fromUtf8("شرکت نرم‌افزاری مروی")));

query2.exec&#40;&#41;;

query.exec(&#41;;

while (query.next(&#41;&#41; {
    qDebug() << query.value(1).typeName();
         qDebug() << query.value(1).toString();
     }

return 0;

@

soroush

Ok, I do a test to understand behavior of QVariant with strings
@int main()
{
QString s = "فارسی";
QByteArray b = s.toAscii();
for (int i = 0; i < b.size(); ++i) {
qDebug()<<(int)b[i];
}
qDebug()<<b.size();
QVariant v(b);
QString s2 = v.toString();
qDebug() << s2;
}@
Above code prints:
@-39
-127
-40
-89
-40
-79
-40
-77
-37
-116
10
"فارسی" @
Which is correct. It seems that QVariant knows how to convert UTF-8 encoded strings of bytes to QStrings (is not?), so I'm going to play around database.

loladiro

True, but what is really happening (inside the driver) is more like:
@
int main()
{
QString s = "فارسی";
QByteArray b = s.toUtf8();
for (int i = 0; i < b.size(); ++i) {
qDebug()<<(int)b[i];
}
qDebug()<<b.size();
QVariant v(b);
QString s2 = v.toString();
qDebug() << s2;
}
@

soroush

Ok I tried this code with same database:
@int main()
{
QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
db.setDatabaseName("shop");
db.setHostName("localhost");
db.setUserName("root");
db.setPassword(<password>);
qDebug()<<db.open();
QSqlQuery q("select * from customers");
q.exec();
while(q.next())
qDebug()<<q.value(1).toString();
}@

works well. but there is no hope with QSqlTableView.