Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. [SOLVED] How to force QSqlTableModel to use UTF-8 encoding?
Forum Updated to NodeBB v4.3 + New Features

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

Scheduled Pinned Locked Moved General and Desktop
35 Posts 4 Posters 25.2k Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • L Offline
    L Offline
    loladiro
    wrote on last edited by
    #19

    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;
    }
    @

    1 Reply Last reply
    0
    • S Offline
      S Offline
      soroush
      wrote on last edited by
      #20

      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.

      1 Reply Last reply
      0
      • L Offline
        L Offline
        loladiro
        wrote on last edited by
        #21

        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;;@
        
        1 Reply Last reply
        0
        • S Offline
          S Offline
          soroush
          wrote on last edited by
          #22

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

          1 Reply Last reply
          0
          • L Offline
            L Offline
            loladiro
            wrote on last edited by
            #23

            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.

            1 Reply Last reply
            0
            • S Offline
              S Offline
              soroush
              wrote on last edited by
              #24

              [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

              1 Reply Last reply
              0
              • L Offline
                L Offline
                loladiro
                wrote on last edited by
                #25

                Did you remove the utf8_bin?

                1 Reply Last reply
                0
                • S Offline
                  S Offline
                  soroush
                  wrote on last edited by
                  #26

                  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:
                  @"سروش"
                  "حسام"
                  "حامد" @

                  1 Reply Last reply
                  0
                  • S Offline
                    S Offline
                    soroush
                    wrote on last edited by
                    #27

                    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.

                    1 Reply Last reply
                    0
                    • L Offline
                      L Offline
                      loladiro
                      wrote on last edited by
                      #28

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

                      1 Reply Last reply
                      0
                      • L Offline
                        L Offline
                        loladiro
                        wrote on last edited by
                        #29

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

                        1 Reply Last reply
                        0
                        • D Offline
                          D Offline
                          dangelog
                          wrote on last edited by
                          #30

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

                          Software Engineer
                          KDAB (UK) Ltd., a KDAB Group company

                          1 Reply Last reply
                          0
                          • S Offline
                            S Offline
                            soroush
                            wrote on last edited by
                            #31

                            @#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:

                            1 Reply Last reply
                            1
                            • L Offline
                              L Offline
                              loladiro
                              wrote on last edited by
                              #32

                              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.

                              1 Reply Last reply
                              0
                              • S Offline
                                S Offline
                                soroush
                                wrote on last edited by
                                #33

                                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
                                @

                                1 Reply Last reply
                                0
                                • L Offline
                                  L Offline
                                  loladiro
                                  wrote on last edited by
                                  #34

                                  Very funny.
                                  [quote]
                                  utf8_bin
                                  utf8_bin
                                  utf8_bin
                                  utf8_bin
                                  utf8_bin
                                  utf8_bin
                                  utf8_bin
                                  utf8_bin
                                  utf8_bin
                                  utf8_bin
                                  utf8_bin
                                  [/quote]
                                  If you remove that:
                                  !http://dl.dropbox.com/u/32965023/result.png(none)!

                                  Edit: BTW, I added the last two lines to test.

                                  Edit2: I adjusted the uft8_bin's in the quote to exactly match the number in your dump

                                  1 Reply Last reply
                                  0
                                  • S Offline
                                    S Offline
                                    soroush
                                    wrote on last edited by
                                    #35

                                    I removed uft8_bin from table option tab in MySQL Admin. but each time I insert data using MySQL Query Browser, colliding method is added to all tables of database.

                                    I'm going to create re-database with mysql cli.

                                    Thank you very much :-)

                                    UPDATED:
                                    I do everything from beginning, and remove every colliding method. Now everything works fine:
                                    !http://s1.picofile.com/file/6846154924/screenshot9.png()!
                                    Thanks again.

                                    1 Reply Last reply
                                    0

                                    • Login

                                    • Login or register to search.
                                    • First post
                                      Last post
                                    0
                                    • Categories
                                    • Recent
                                    • Tags
                                    • Popular
                                    • Users
                                    • Groups
                                    • Search
                                    • Get Qt Extensions
                                    • Unsolved