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. SQLite3 Errors: Parameter count mismatch and Unable to fetch row
Forum Updated to NodeBB v4.3 + New Features

SQLite3 Errors: Parameter count mismatch and Unable to fetch row

Scheduled Pinned Locked Moved Solved General and Desktop
qt5.11sqlite3
13 Posts 5 Posters 5.3k 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.
  • dheerendraD Offline
    dheerendraD Offline
    dheerendra
    Qt Champions 2022
    wrote on last edited by
    #2

    Hope the person table exist in the db. I'm assuming that you have created person table outside this program.

    Did you look at the QSqlQuery class in Qt Assistant. It has small example and you can try for it. It works.

    Dheerendra
    @Community Service
    Certified Qt Specialist
    http://www.pthinks.com

    C 1 Reply Last reply
    1
    • dheerendraD dheerendra

      Hope the person table exist in the db. I'm assuming that you have created person table outside this program.

      Did you look at the QSqlQuery class in Qt Assistant. It has small example and you can try for it. It works.

      C Offline
      C Offline
      Chrinkus
      wrote on last edited by
      #3

      @dheerendra Yes, from the command line I have launched the sqlite3 prompt and created a person table. I have used DB Browser for SQLite to assure myself that yes, the table and its limited schema are as I expect.

      1 Reply Last reply
      0
      • dheerendraD Offline
        dheerendraD Offline
        dheerendra
        Qt Champions 2022
        wrote on last edited by
        #4

        Can you try something like follows ?

          QSqlQuery query;
          query.prepare("INSERT INTO person (id, forename, surname) "
                        "VALUES (:id, :forename, :surname)");
          query.bindValue(":id", 1001);
          query.bindValue(":forename", "Dheerendra");
          query.bindValue(":surname", "PthinkS");
          query.exec();
        

        Dheerendra
        @Community Service
        Certified Qt Specialist
        http://www.pthinks.com

        1 Reply Last reply
        1
        • Christian EhrlicherC Offline
          Christian EhrlicherC Offline
          Christian Ehrlicher
          Lifetime Qt Champion
          wrote on last edited by
          #5

          You should check the return value of the QSqlQuery::prepare() - there is no table 'people' I would guess. This is working perfectly fine:

          int main(int argc, char *argv[])
          {
              QCoreApplication a(argc, argv);
          
              // Create DB connection
              auto db = QSqlDatabase::addDatabase("QSQLITE");
              if (!db.open())
                  qDebug() << "Error: could not open database";
              else
                  qDebug() << "Database opened successfully";
          
              QSqlQuery q;
              if (!q.exec("CREATE TABLE people (name text);")) {
                  qDebug() << "can not create table:"<< q.lastError();
                  return 1;
              }
              // Add a record
              auto queryAdd1 = QSqlQuery{};
              if (!queryAdd1.prepare("INSERT INTO people (name) VALUES ('Sally')")) {
                  qDebug() << "can not prepare query:"<< queryAdd1.lastError();
                  return 1;
              }
              //queryAdd1.bindValue(":name", QString{"Sally"});
              if (queryAdd1.exec())
                  qDebug() << "Person added";
              else
                  qDebug() << "Error: could not add person "
                           << queryAdd1.lastError()
                           << queryAdd1.lastQuery();
          
              // Query all
              auto queryAll = QSqlQuery{"SELECT * FROM people;"};
              int idName = queryAll.record().indexOf("name");
              while (queryAll.next()) {
                  auto name = queryAll.value(idName).toString();
                  qDebug() << "Selected: " << name;
              }
          
              return a.exec();
          }
          

          Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
          Visit the Qt Academy at https://academy.qt.io/catalog

          1 Reply Last reply
          2
          • C Chrinkus

            I'm very new to Qt and after doing a few GUI tutorials I wanted to try working with a database. I found a user-made tutorial and followed it through only to crash into the 'Parameter count mismatch' error. There was an error in preparing the insert query, the value binding wasn't working properly. I replaced the binding tag with an actual hard-coded value to ensure a valid query and that's when I landed on the 'Unable to fetch row' and 'No query' errors.

            I searched SO and found many hits that matched my problem however many of them are unanswered.

            I distilled the project down to a minimal recreation as seen below. The bindValue call has been commented out and the hard-coded value is present. This results in 'Unable to fetch row' and 'No query'.

            #include <QCoreApplication>
            
            #include <QDebug>
            #include <QSqlDatabase>
            #include <QSqlQuery>
            #include <QSqlError>
            #include <QSqlRecord>
            
            int main(int argc, char *argv[])
            {
                QCoreApplication a(argc, argv);
            
                // Create DB connection
                auto db = QSqlDatabase::addDatabase("QSQLITE");
                db.setDatabaseName("test.db");
                if (!db.open())
                    qDebug() << "Error: could not open database";
                else
                    qDebug() << "Database opened successfully";
            
                // Add a record
                auto queryAdd1 = QSqlQuery{};
                queryAdd1.prepare("INSERT INTO people (name) VALUES ('Sally')");
                //queryAdd1.bindValue(":name", QString{"Sally"});
                if (queryAdd1.exec())
                    qDebug() << "Person added";
                else
                    qDebug() << "Error: could not add person "
                             << queryAdd1.lastError()
                             << queryAdd1.lastQuery();
            
                // Query all
                auto queryAll = QSqlQuery{"SELECT * FROM people;"};
                int idName = queryAll.record().indexOf("name");
                while (queryAll.next()) {
                    auto name = queryAll.value(idName).toString();
                    qDebug() << "Selected: " << name;
                }
            
                return a.exec();
            }
            
            JonBJ Offline
            JonBJ Offline
            JonB
            wrote on last edited by JonB
            #6

            @Chrinkus
            For queryAdd1 you correctly call queryAdd1.exec().

            For queryAll you call next() without having called exec(). Although I must respect that @Christian-Ehrlicher says his code is "working fine", I must say I am surprised at this. The documentation (http://doc.qt.io/qt-5/qsqlquery.html#next, http://doc.qt.io/qt-5/qsqlquery.html#isActive) seems quite clear that you must have called exec() before next():

            Note that the result must be in the active state
            ...
            An active QSqlQuery is one that has been exec()'d successfully but not yet finished with.

            C 1 Reply Last reply
            0
            • Christian EhrlicherC Offline
              Christian EhrlicherC Offline
              Christian Ehrlicher
              Lifetime Qt Champion
              wrote on last edited by
              #7

              @JonB said in SQLite3 Errors: Parameter count mismatch and Unable to fetch row:

              I must say I am surprised at this.

              RTFM ;)
              http://doc.qt.io/qt-5/qsqlquery.html#QSqlQuery-1

              Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
              Visit the Qt Academy at https://academy.qt.io/catalog

              1 Reply Last reply
              2
              • C Offline
                C Offline
                Chrinkus
                wrote on last edited by
                #8

                Okay! I solved the issue. When I created test.db from the command line I created it in the project directory where my source, headers and project files were. Upon clicking 'run' Qt creates a separate build directory where it runs the executable from. This new directory obviously does not have my database in it.

                The issue was compounded by the SQLite behaviour that attempting to open a database that does not exists instead creates it for you. So I had a pre-created test.db file in my QtSqliteTest1 folder with my people table and another test.db file in the auto-generated
                build-QtSqliteTest1-Desktop_Qt_5_11_12_MSVC2017_64bit-Debug folder, created by my call to open the original db.

                What's more, if I change my build to release, a NEW directory is thus made for the release-build files so a new database is created upon running.

                Lesson learned, don't expect my databases to follow me around.

                The tutorial I was following from 2015 instructs you to create your database up front before starting Qt. However, looking at the GitHub repo shows that the original author was creating the table from her final main.cpp file, a step she did not indicate in the tutorial. Somehow I missed this line in her README.md:

                IMPORTANT If you don't see your database file show up in your file directory, you need to go to Projects -> Build & Run -> Run ->
                Working Directory. This is where your database file will be generated.

                Wow! What a fun couple of days!

                1 Reply Last reply
                3
                • JonBJ JonB

                  @Chrinkus
                  For queryAdd1 you correctly call queryAdd1.exec().

                  For queryAll you call next() without having called exec(). Although I must respect that @Christian-Ehrlicher says his code is "working fine", I must say I am surprised at this. The documentation (http://doc.qt.io/qt-5/qsqlquery.html#next, http://doc.qt.io/qt-5/qsqlquery.html#isActive) seems quite clear that you must have called exec() before next():

                  Note that the result must be in the active state
                  ...
                  An active QSqlQuery is one that has been exec()'d successfully but not yet finished with.

                  C Offline
                  C Offline
                  Chrinkus
                  wrote on last edited by
                  #9

                  @JonB said in SQLite3 Errors: Parameter count mismatch and Unable to fetch row:

                  @Chrinkus
                  For queryAdd1 you correctly call queryAdd1.exec().

                  For queryAll you call next() without having called exec(). Although I must respect that @Christian-Ehrlicher says his code is "working fine", I must say I am surprised at this. The documentation (http://doc.qt.io/qt-5/qsqlquery.html#next, http://doc.qt.io/qt-5/qsqlquery.html#isActive) seems quite clear that you must have called exec() before next():

                  Note that the result must be in the active state
                  ...
                  An active QSqlQuery is one that has been exec()'d successfully but not yet finished with.

                  My queryAll is constructed with a valid SQL statement so it is executed immediately, no need to call exec().

                  QSqlQuery::QSqlQuery(const QString &query = QString(), QSqlDatabase db = QSqlDatabase())

                  Constructs a QSqlQuery object using the SQL query and the database db. If db is not specified, or is invalid, the application's default
                  database is used. If query is not an empty string, it will be executed.

                  JonBJ 1 Reply Last reply
                  0
                  • C Chrinkus

                    @JonB said in SQLite3 Errors: Parameter count mismatch and Unable to fetch row:

                    @Chrinkus
                    For queryAdd1 you correctly call queryAdd1.exec().

                    For queryAll you call next() without having called exec(). Although I must respect that @Christian-Ehrlicher says his code is "working fine", I must say I am surprised at this. The documentation (http://doc.qt.io/qt-5/qsqlquery.html#next, http://doc.qt.io/qt-5/qsqlquery.html#isActive) seems quite clear that you must have called exec() before next():

                    Note that the result must be in the active state
                    ...
                    An active QSqlQuery is one that has been exec()'d successfully but not yet finished with.

                    My queryAll is constructed with a valid SQL statement so it is executed immediately, no need to call exec().

                    QSqlQuery::QSqlQuery(const QString &query = QString(), QSqlDatabase db = QSqlDatabase())

                    Constructs a QSqlQuery object using the SQL query and the database db. If db is not specified, or is invalid, the application's default
                    database is used. If query is not an empty string, it will be executed.

                    JonBJ Offline
                    JonBJ Offline
                    JonB
                    wrote on last edited by JonB
                    #10

                    @Chrinkus

                    My queryAll is constructed with a valid SQL statement so it is executed immediately, no need to call exec().

                    Sorry, yes, I realized this from @Christian-Ehrlicher 's link. I had not realized that one overload would actually execute the query! :)

                    1 Reply Last reply
                    0
                    • Christian EhrlicherC Offline
                      Christian EhrlicherC Offline
                      Christian Ehrlicher
                      Lifetime Qt Champion
                      wrote on last edited by
                      #11

                      This overload is really confusion and I wonder if it should not be marked as deprecated. There were already some bugreports about this because the usage was unexpected to the users so they did an exec afterwards and screwed up the query with it.

                      Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                      Visit the Qt Academy at https://academy.qt.io/catalog

                      JonBJ kshegunovK 2 Replies Last reply
                      1
                      • Christian EhrlicherC Christian Ehrlicher

                        This overload is really confusion and I wonder if it should not be marked as deprecated. There were already some bugreports about this because the usage was unexpected to the users so they did an exec afterwards and screwed up the query with it.

                        JonBJ Offline
                        JonBJ Offline
                        JonB
                        wrote on last edited by JonB
                        #12

                        @Christian-Ehrlicher
                        Thank you for posting that. Even though you put a smiley in your earlier reply, I wondered if you were annoyed with me! It's easy to criticize other peoples' library function decisions, but for me having one constructor which actually executes the SQL query while the others do not is "too much", I would expect to have to do some explicit .exec() before that happened.

                        1 Reply Last reply
                        0
                        • Christian EhrlicherC Christian Ehrlicher

                          This overload is really confusion and I wonder if it should not be marked as deprecated. There were already some bugreports about this because the usage was unexpected to the users so they did an exec afterwards and screwed up the query with it.

                          kshegunovK Offline
                          kshegunovK Offline
                          kshegunov
                          Moderators
                          wrote on last edited by kshegunov
                          #13

                          @Christian-Ehrlicher said in SQLite3 Errors: Parameter count mismatch and Unable to fetch row:

                          This overload is really confusion and I wonder if it should not be marked as deprecated. There were already some bugreports about this because the usage was unexpected to the users so they did an exec afterwards and screwed up the query with it.

                          I appreciate your discreetness, but I don't try to hide my stupidity. ;)

                          Read and abide by the Qt Code of Conduct

                          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