SQLite3 Errors: Parameter count mismatch and Unable to fetch row

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

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

[Chrinkus]

@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.

[dheerendra]

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

[Christian Ehrlicher]

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

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

[Christian Ehrlicher]

@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

[Chrinkus]

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!

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

[JonB]

@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! :)

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

[JonB]

@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.

[kshegunov]

@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. ;)