[SOLVED] How to force QSqlTableModel to use UTF-8 encoding?
-
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? -
They are VARCHAR(50) and MEDIUMTEXT.
Here is the create script of a table:
@
CREATE TABLEcustomers
(
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@ -
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..
-
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.
-
[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).
-
[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 TABLEcustomers
(
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 @ -
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(); query.exec(); while (query.next()) { qDebug() << query.value(1).typeName(); qDebug() << query.value(1).toString(); } return 0;
@
-
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. -
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;
}
@ -
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.