[SOLVED] How to force QSqlTableModel to use UTF-8 encoding?
-
[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).
-
The problem is that you use utf8_bin which set the BINARY_FLAG, which disables the automatic conversion to Unicode.
-
[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(
idsmallint(5) unsigned NOT NULL AUTO_INCREMENT,
namevarchar(50) CHARACTER SET utf8 NOT NULL,
lastvarchar(50) CHARACTER SET utf8 NOT NULL,
phonebigint(20) unsigned zerofill NOT NULL,
addressmediumtext 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.
-
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();@ -
I'm getting mad:
!http://s1.picofile.com/file/6845745470/screenshot7.png()! -
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 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 -
Did you remove the utf8_bin?
-
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:
@"Ø³Ø±ÙØ´"
"ØØ³Ø§Ù "
"ØØ§Ù د" @ -
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. -
Could you please create a minimal compilable example that still exhibits the behavior you just described?
-
[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] -
@#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: -
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.
-
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 tablecustomersDROP TABLE IF EXISTS
customers;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLEcustomers(
idsmallint(5) unsigned NOT NULL AUTO_INCREMENT,
namevarchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
lastvarchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
phonebigint(20) unsigned zerofill NOT NULL,
addressmediumtext 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 tablecustomersLOCK TABLES
customersWRITE;
/*!40000 ALTER TABLEcustomersDISABLE KEYS /;
INSERT INTOcustomersVALUES (1,'xx','xx',00000000000003661195,'xx'),(3,'x','xx',00000000004294967295,'xx'),(4,'xxx','xx',00000000000002241578,'xx'),(5,'سهراب','سپهری',00000000000000000000,'xx'),(12,'نتسنیتب','نتسنیتب',00000000000000000012,'نتسنیتب'),(180,'ش','ش',00000000000000000180,'ش');
/!40000 ALTER TABLEcustomersENABLE KEYS */;
UNLOCK TABLES;--
-- Table structure for tablefactorsDROP TABLE IF EXISTS
factors;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLEfactors(
idsmallint(5) unsigned NOT NULL,
customer_idsmallint(5) unsigned NOT NULL,
stuff_idsmallint(5) unsigned NOT NULL,
seller_idsmallint(5) unsigned NOT NULL,
counttinyint(3) unsigned NOT NULL,
PRIMARY KEY (id),
KEYconstruct_cid(customer_id),
KEYconstruct_sid(stuff_id),
KEYconstruct_seid(seller_id),
CONSTRAINTconstruct_cidFOREIGN KEY (customer_id) REFERENCEScustomers(id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINTconstruct_seidFOREIGN KEY (seller_id) REFERENCESsellers(id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINTconstruct_sidFOREIGN KEY (stuff_id) REFERENCESstuffs(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 tablefactorsLOCK TABLES
factorsWRITE;
/*!40000 ALTER TABLEfactorsDISABLE KEYS /;
INSERT INTOfactorsVALUES (1,4,6,1,5),(2,3,3,2,3),(3,3,6,3,2);
/!40000 ALTER TABLEfactorsENABLE KEYS */;
UNLOCK TABLES;--
-- Table structure for tablesellersDROP TABLE IF EXISTS
sellers;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLEsellers(
idsmallint(5) unsigned NOT NULL,
uidvarchar(20) COLLATE utf8_bin NOT NULL,
passwordvarchar(20) COLLATE utf8_bin NOT NULL,
namevarchar(20) COLLATE utf8_bin NOT NULL,
lastvarchar(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 tablesellersLOCK TABLES
sellersWRITE;
/*!40000 ALTER TABLEsellersDISABLE KEYS /;
INSERT INTOsellersVALUES (1,'admin','1370','x','x'),(2,'hesam','123','x','x'),(3,'x','123','x','x');
/!40000 ALTER TABLEsellersENABLE KEYS */;
UNLOCK TABLES;--
-- Table structure for tablestuffsDROP TABLE IF EXISTS
stuffs;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLEstuffs(
idsmallint(5) unsigned NOT NULL,
weightfloat unsigned NOT NULL,
feefloat unsigned NOT NULL,
p_datedate NOT NULL,
e_datedate NOT NULL,
manufactorertinytext 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 tablestuffsLOCK TABLES
stuffsWRITE;
/*!40000 ALTER TABLEstuffsDISABLE KEYS /;
INSERT INTOstuffsVALUES (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 TABLEstuffsENABLE 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
@