Qt Forum

    • Login
    • Search
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Search
    • Unsolved

    Update: Forum Guidelines & Code of Conduct

    Unsolved QSqlTableModel only fetching table headers (with names) and no data from table QODBC

    General and Desktop
    odbc sql qsqltablemodel netezza qtableview
    4
    17
    8858
    Loading More Posts
    • 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.
    • michalos
      michalos last edited by michalos

      Hi,
      I'm trying to get data from a table in my SQL database to a QTableView.
      Should be easy, and it is.. when I'm conected to a SQLite db.
      But I need to connect to get data from a Netezza db.
      I've configured a ODBC connection and I'm able to get an answer for a QSqlQuery, but I cannot get the QSqlTableModel to work. Is there some magic trick I need to do, so that my model gets populated with the data from the table?
      All I get are headers with the SQL table's column names.
      In the picture below I have two views. First (upper) has a table populated by a QSqlQuery and a QStandardItemModel, and second is with the QSqlTableModel.

      Does anyone have a clue why is the QSqlTableModel not filled with the data from the table?

      Image of the view

      code I use to populate the lower tables:

          model = new QStandardItemModel(this);
          tModel = new QSqlTableModel(this, db);
      
          tModel->setTable("CLIENTS");
          tModel->setEditStrategy(QSqlTableModel::OnFieldChange);
          tModel->select();
          tModel->fetchMore();
          ui->tableView_2->setModel(tModel);
          setModel(); //populate model from a query
          ui->tableView->setModel(model);
      

      And the rest of the code for populating the upper view.

      void MainWindow::setModel()
      {
          model->clear();
          QSqlQuery q;
      //    q.setForwardOnly(true);
          // WYSWIETLANIE
          QString query = "SELECT * FROM CLIENTS ;";
          // DODAWANIE
      
      
          qDebug() << query;
          if (!q.exec(query))
               qDebug() << q.lastError();
      
          QList<QStandardItem*> listaItemow0;
          QList<QStandardItem*> listaItemow1;
          QList<QStandardItem*> listaItemow2;
          QList<QStandardItem*> listaItemow3;
          QList<QStandardItem*> listaItemow4;
          QStandardItem* item0;
          QStandardItem* item1;
          QStandardItem* item2;
          QStandardItem* item3;
          QStandardItem* item4;
      
          while (q.next()) {
               qDebug() << "----------";
      ////        for(int i=0; i<5; i++){
                  qDebug() << q.value(0).toString() << q.value(1).toString() << q.value(2).toString() << q.value(3).toString() << q.value(4).toString();
      ////        }
              item0 = new QStandardItem(q.value(0).toString());
              item1 = new QStandardItem(q.value(1).toString());
              item2 = new QStandardItem(q.value(2).toString());
              item3 = new QStandardItem(q.value(3).toString());
              item4 = new QStandardItem(q.value(4).toString());
              listaItemow0.append(item0);
              listaItemow1.append(item1);
              listaItemow2.append(item2);
              listaItemow3.append(item3);
              listaItemow4.append(item4);
      
      
          }
          model->appendColumn(listaItemow0);
          model->appendColumn(listaItemow1);
          model->appendColumn(listaItemow2);
          model->appendColumn(listaItemow3);
          model->appendColumn(listaItemow4);
          model->setHeaderData(0, Qt::Horizontal, tr("Nr Ubezpieczenia"));
          model->setHeaderData(1, Qt::Horizontal, tr("Imię"));
          model->setHeaderData(2, Qt::Horizontal, tr("Nazwisko"));
          model->setHeaderData(3, Qt::Horizontal, tr("Adres"));
          model->setHeaderData(4, Qt::Horizontal, tr("Medium Kontaktu"));
      }
      
      //Initializing the connection to the db:
      QSqlError initDatabase(QSqlDatabase &db) {
          db = QSqlDatabase::addDatabase("QODBC");
          db.setDatabaseName("NZSQL");
          db.setUserName("admin");
          db.setPassword("password");
          if (!db.open()) qDebug() << "Can't connect with database.";
          else return db.lastError();
      
      1 Reply Last reply Reply Quote 0
      • SGaist
        SGaist Lifetime Qt Champion last edited by

        Hi,

        Did you check whether QSqlTableModel returned any error ?

        Also, in your initDatabase function, why return lastError only if everything goes correctly ?

        Interested in AI ? www.idiap.ch
        Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

        1 Reply Last reply Reply Quote 3
        • michalos
          michalos last edited by

          @SGaist I cannot check it right now, but I'll let You know in a couple hours.

          I know that the initDatabase function return is illogical. I copied it from a colleague and will change it eventually.
          First I wanted to know if there's a way to populate the QSqlTableModel. I would have less work, than with the other solution.

          I also tried QSqlQueryModel and the QSqlRelationalTableModel. The results were the same.

          1 Reply Last reply Reply Quote 0
          • SGaist
            SGaist Lifetime Qt Champion last edited by

            That's why I suggested to check the error state of the QSqlTableModel. If you can get values with a QSqlQuery then the model should also work.

            Interested in AI ? www.idiap.ch
            Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

            1 Reply Last reply Reply Quote 0
            • michalos
              michalos last edited by michalos

              The error is:
              "Forward-only queries cannot be used in a data model"

              So if this: >link to a topic< is correct, I need to do this the longer way (using QStandardItemModel and populating it with an QSqlQuery)

              1 Reply Last reply Reply Quote 0
              • SGaist
                SGaist Lifetime Qt Champion last edited by

                Can you test with a QSqlQueryModel ?

                Interested in AI ? www.idiap.ch
                Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                1 Reply Last reply Reply Quote 0
                • michalos
                  michalos last edited by michalos

                  I've tested with QSqlQueryModel and the QSqlRelationalTableModel. It's the same.
                  No resoults, only header names.

                  1 Reply Last reply Reply Quote 0
                  • SGaist
                    SGaist Lifetime Qt Champion last edited by

                    What kind of database are you connection to ?

                    Interested in AI ? www.idiap.ch
                    Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                    1 Reply Last reply Reply Quote 0
                    • michalos
                      michalos last edited by

                      My connection is to a IBM Netezza database.

                      1 Reply Last reply Reply Quote 0
                      • SGaist
                        SGaist Lifetime Qt Champion last edited by

                        Ok, so not easily reproducible...

                        Interested in AI ? www.idiap.ch
                        Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                        1 Reply Last reply Reply Quote 1
                        • J
                          jebi last edited by

                          Hi,
                          I've got the same probleme with MS SQL server.
                          I want to use a QSqlQueryModel for getting datas in a QTableView

                          Error
                          QSqlError("", "Forward-only queries cannot be used in a data model", "")

                          Code I used :

                          QSqlQueryModel *model = new QSqlQueryModel();
                          QSqlQuery qry;
                          qry.exec("Select * from villes");        
                          model->setQuery (qry);
                              listing = new QTableView;
                             listing->setModel (model);   
                              qDebug()<<model->rowCount ();/ / this return 0 but Database get 13000 lines
                                  qDebug()<<model->lastError ();
                          

                          Could you help me please ?

                          Best regards,
                          Jebi

                          1 Reply Last reply Reply Quote 0
                          • michalos
                            michalos last edited by

                            Hi,

                            I don't know anything about MS SQL, but I would start by changing 'qry.exec' to 'qry.prepare' or deleting the QSqlQuery like so:

                            QSqlQueryModel *model = new QSqlQueryModel();
                            QSqlQuery qry;
                            qry.prepare("Select * from villes");        
                            model->setQuery (qry);
                                listing = new QTableView;
                               listing->setModel (model);   
                                qDebug()<<model->rowCount ();/ / this return 0 but Database get 13000 lines
                                    qDebug()<<model->lastError ();
                            

                            or just:

                            QSqlQueryModel *model = new QSqlQueryModel();
                            
                            model->setQuery ("Select * from villes");
                            
                                listing = new QTableView;
                               listing->setModel (model);   
                                qDebug()<<model->rowCount ();/ / this return 0 but Database get 13000 lines
                                    qDebug()<<model->lastError ();
                            
                            J 1 Reply Last reply Reply Quote 0
                            • michalos
                              michalos last edited by

                              If this doesn't help try:

                              http://stackoverflow.com/questions/3060916/connection-to-sql-server-with-qt

                              or the VoidRealms tutorials:

                              https://www.youtube.com/watch?v=3XE2bKUAxfw&t=500s

                              1 Reply Last reply Reply Quote 1
                              • J
                                jebi @michalos last edited by

                                @michalos
                                thanks a lot for your fast answers.

                                I tried your solutions but problem is the same.

                                I'm sure it's not a problem from connection to server because all my queries works.

                                In this case, I have the name of my columns, but no datas in QtableView

                                Here is the form :
                                0_1490198881369_upload-ed5800b4-3b3f-4318-aa4e-4113b06fd140.

                                Best regards.

                                Jebi

                                1 Reply Last reply Reply Quote 0
                                • SGaist
                                  SGaist Lifetime Qt Champion last edited by

                                  Why not use a QSqlTableModel for the villes table ?

                                  Interested in AI ? www.idiap.ch
                                  Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                                  1 Reply Last reply Reply Quote 1
                                  • michalos
                                    michalos last edited by

                                    I would try, as SGaist suggests.

                                    Also:

                                    What version of Qt are You using and on what platform?

                                    did You try to set setForwardOnly() to false? (just a guess, as I said, I'm no expert)

                                    1 Reply Last reply Reply Quote 0
                                    • jwernerny
                                      jwernerny last edited by

                                      I've uncovered the same issue with QSqlQueryModel using the Windows QODBC database engine connected to MS SQL Server, and have been do some digging. Hopefully this will help others experiencing this issue.

                                      There are a couple of different things happening here.

                                      First, QSqlQueryModel does not support forward only queries. If you try to use a forward only query, QSqlQueryModel::lastError() will return "Forward-only queries cannot be used in a data model".

                                      That seems clear, but does not explain the situation when the forward only is left in its default false state or explicitly set in code. During my exploration of the issue, I would notice that even though I called QSqlQuery::setForwardOnly(false), a call to QSqlQuery::isForwardOnly() made after the query was executed would show forward only set to true, something I did not understand.

                                      Then I read the Qt documentation for setForwardOnly:

                                      Setting forward only to false is a suggestion to the database engine, which has the final say on whether a result set is forward only or scrollable. isForwardOnly() will always return the correct status of the result set.

                                      The database engine has the final say whether or not forward only is used!

                                      Why was the database engine setting forward only? Qt's SQL Database Drivers documentation gives part of the answer if you know what you are looking at.

                                      ODBC Stored Procedure Support

                                      With Microsoft SQL Server the result set returned by a stored procedure that uses the return statement, or returns multiple result sets, will be accessible only if you set the query's forward only mode to forward using QSqlQuery::setForwardOnly().

                                      By observation, the QODBC database engine (in Qt 5.8) recognizes when more than one return set has resulted from the query, so it is automatically setting forward only to true.

                                      How are multiple result sets generated? I've uncovered a few ways.

                                      • A query that has multiple select statements (e.g. select * from table1; select * from table2).
                                      • Stored Procedures In particular, MS SQL Server documentation has this to say:

                                      SQL Server stored procedures have four mechanisms used to return data:

                                      • Each SELECT statement in the procedure generates a result set.
                                      • The procedure can return data through output parameters.
                                      • A cursor output parameter can pass back a Transact-SQL server cursor.
                                      • The procedure can have an integer return code.

                                      The first item in Microsoft's list was the key for me. When NOCOUNT is OFF, Even a simple internal variable assignment using a select (e.g. select @user = 'fred') generated a return set. Setting NOCOUNT to ON stops this behavior. From Microsoft's documentation on NOCOUNT

                                      SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

                                      The Solution for me turned out to be two things:

                                      • Add SET NOCOUNT ON to the top of my stored procedure so that only the select statement I cared about was returned
                                      • Instead of using QSqlQueryModel, manually extract the results from the query and build my own QStandardItemModel.
                                      1 Reply Last reply Reply Quote 2
                                      • First post
                                        Last post