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

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.

loladiro

That's also absolutely no problem:
!http://dl.dropbox.com/u/32965023/sqltest.png(proof)!

@ QSqlTableModel *model = new QSqlTableModel(0,db);
model->setTable("test2");
model->select();
QTableView v;
v.setModel(model);

v.show();

return a.exec&#40;&#41;;@

soroush

I'm getting mad:
!http://s1.picofile.com/file/6845745470/screenshot7.png()!

loladiro

I think it's something about the tool you used to insert the data. At first I used the mysql command line tool which by default uses they system charset, not UTF-8.

soroush

[quote author="loladiro" date="1308751303"]I think it's something about the tool you used to insert the data. At first I used the mysql command line tool which by default uses they system charset, not UTF-8.[/quote]

dropping table and creating it again didn't help. also I tested insert commend and that is wrong too.
The only differences between command-line tests (working correctly) and the application itself (wrong encoding), is QtGUI module declared in .pro file and QTableView

loladiro

Did you remove the utf8_bin?

soroush

I found problem! consider the code:
@ QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
db.setDatabaseName("shop");
db.setHostName("localhost");
db.setUserName("root");
db.open();
QSqlQuery q("select * from sellers");
q.exec();
while(q.next())
qDebug()<<q.value(3).toString();@
This code In a qt application with no GUI prints correct output:
@
"سروش"
"حسام"
حامد" "
@
but in a Qt widget project with a .pro file containing
@QT += gui@
prints this:
@"Ø³Ø±ÙØ´"
"ØØ³Ø§Ù"
"ØØ§ÙØ¯" @

soroush

bq. Did you remove the utf8_bin?

Yes i removed utf8_bin and binary flags of fields.
utf8_bin is just colliding algorithm that used to compare values inside database. I think this problem is not related to database. I used same database in php and everything was good.

loladiro

Could you please create a minimal compilable example that still exhibits the behavior you just described?

loladiro

[quote author="soroush" date="1308752734"]bq. Did you remove the utf8_bin?

Yes. utf8_bin is just colliding algorithm that is used to compare values inside database. I think it's not related to database. I used this database in php and everything was good.[/quote]

I know, but, as I said,

[quote]
The problem is that you use utf8_bin which sets the BINARY_FLAG, which disables the automatic conversion to Unicode.
[/quote]