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

[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();

query.exec();

while (query.next()) {
    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();@

[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]

[dangelog]

[quote author="loladiro" date="1308749245"]True, but what is really happening (inside the driver) is more like:
@
QString s = "فارسی";
@
[/quote]

BEWARE OF ENCODING ISSUES if you do that!

[soroush]

@#include <QtGui/QApplication>
#include <QMainWindow>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlTableModel>
#include <QTableView>
#include <QDebug>

int main(int argc, char argv[])
{
QApplication a(argc, argv);
QMainWindow w;
QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
db.setDatabaseName("shop");
db.setHostName("localhost");
db.setUserName("root");
db.setPassword(password);
qDebug() << db.open();
QSqlTableModel model = new QSqlTableModel(0,db);
model->setTable("sellers");
model->select();
QTableView* view = new QTableView();
view->setModel(model);
view->show();
w.setCentralWidget(view);
QSqlQuery q1("select * from sellers");
q1.exec();
w.show();
return a.exec();
}@
The result is:
!http://s1.picofile.com/file/6845918508/screenshot8.png()!
but this code:
@
#include <QApplication>
#include <QVariant>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QDebug>

int main(int argc, char *argv[])
{
QApplication a(argc, argv);
QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
db.setDatabaseName("shop");
db.setHostName("localhost");
db.setUserName("root");
db.setPassword(password);
qDebug()<<db.open();
QSqlQuery q("select * from sellers");

while(q.next())
    qDebug()<<q.value(3).toString();

return a.exec();
}@
results correct output:

[loladiro]

Ok, that's really weird, could you do a mysql dump of your table, so that I can test it.

[quote author="peppe" date="1308753285"]
BEWARE OF ENCODING ISSUES if you do that!
[/quote]

Yeah, I know, it was just to illustrate a point.

[soroush]

bq. Ok, that’s really weird, could you do a mysql dump of your table, so that I can test it.

@
-- MySQL dump 10.13 Distrib 5.1.54, for debian-linux-gnu (x86_64)

-- Host: localhost Database: shop

---

-- Server version 5.1.54-1ubuntu4

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8 /;
/!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE /;
/!40103 SET TIME_ZONE='+00:00' /;
/!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;
/!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;
/!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' /;
/!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table customers

DROP TABLE IF EXISTS customers;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
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=181 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table customers

LOCK TABLES customers WRITE;
/*!40000 ALTER TABLE customers DISABLE KEYS /;
INSERT INTO customers VALUES (1,'xx','xx',00000000000003661195,'xx'),(3,'x','xx',00000000004294967295,'xx'),(4,'xxx','xx',00000000000002241578,'xx'),(5,'سهراب','سپهری',00000000000000000000,'xx'),(12,'نتسنیتب','نتسنیتب',00000000000000000012,'نتسنیتب'),(180,'ش','ش',00000000000000000180,'ش');
/!40000 ALTER TABLE customers ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table factors

DROP TABLE IF EXISTS factors;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE factors (
id smallint(5) unsigned NOT NULL,
customer_id smallint(5) unsigned NOT NULL,
stuff_id smallint(5) unsigned NOT NULL,
seller_id smallint(5) unsigned NOT NULL,
count tinyint(3) unsigned NOT NULL,
PRIMARY KEY (id),
KEY construct_cid (customer_id),
KEY construct_sid (stuff_id),
KEY construct_seid (seller_id),
CONSTRAINT construct_cid FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT construct_seid FOREIGN KEY (seller_id) REFERENCES sellers (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT construct_sid FOREIGN KEY (stuff_id) REFERENCES stuffs (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table factors

LOCK TABLES factors WRITE;
/*!40000 ALTER TABLE factors DISABLE KEYS /;
INSERT INTO factors VALUES (1,4,6,1,5),(2,3,3,2,3),(3,3,6,3,2);
/!40000 ALTER TABLE factors ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table sellers

DROP TABLE IF EXISTS sellers;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE sellers (
id smallint(5) unsigned NOT NULL,
uid varchar(20) COLLATE utf8_bin NOT NULL,
password varchar(20) COLLATE utf8_bin NOT NULL,
name varchar(20) COLLATE utf8_bin NOT NULL,
last varchar(20) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table sellers

LOCK TABLES sellers WRITE;
/*!40000 ALTER TABLE sellers DISABLE KEYS /;
INSERT INTO sellers VALUES (1,'admin','1370','x','x'),(2,'hesam','123','x','x'),(3,'x','123','x','x');
/!40000 ALTER TABLE sellers ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table stuffs

DROP TABLE IF EXISTS stuffs;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE stuffs (
id smallint(5) unsigned NOT NULL,
weight float unsigned NOT NULL,
fee float unsigned NOT NULL,
p_date date NOT NULL,
e_date date NOT NULL,
manufactorer tinytext COLLATE utf8_bin NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table stuffs

LOCK TABLES stuffs WRITE;
/*!40000 ALTER TABLE stuffs DISABLE KEYS /;
INSERT INTO stuffs VALUES (1,1,285,'2011-06-18','2012-06-18','IBM'),(2,1.35,300,'2009-01-10','2010-05-21','Microsoft'),(3,5.5,25,'2001-07-25','2011-07-25','x x x'),(6,3.5,950,'2001-07-25','2021-07-25','x x x');
/!40000 ALTER TABLE stuffs ENABLE KEYS /;
UNLOCK TABLES;
/!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE /;
/!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS /;
/!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS /;
/!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION /;
/!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2011-06-22 19:18:39
@