QSqlQuery can return only one row

mamsds

The same issue and solution has been reported here 10+ years ago:

https://stackoverflow.com/questions/17066315/qsqlquery-for-sqlite-in-forward-iteration-with-next-will-only-find-one-row

and what I encounter is exactly the same...I am using Qt 6.6 on Linux. My code is the following:

std::vector<Category> DB::getAllCategories()
{
    auto stmt = R"(
SELECT DISTINCT(category)
FROM my_table
)";
    auto query = openConnThenPrepareQuery(stmt);
    execQuery(query);
    auto allCats = std::vector<Category>();
    // This forward iteration only returns the first row
    while (query.next()) {
       // load data into my struct
    }

    /* This version works as expected, returning many rows
    if (query.last()) {
        do {
       // load data into my struct
        } while (query.previous());
    }
    */
    return allCats;
}

where openConnThenPrepareQuery() and execQuery() are defined somewhere else as the follows:

QSqlQuery DB::openConnThenPrepareQuery(const QString &stmt)
{
    if (!conn.isOpen()) {
        if (!conn.open()) {
            throw runtime_error("Fail!");
        }
    }
    auto query = QSqlQuery(conn);
    if (!query.prepare(stmt)) {
        throw std::runtime_error("Fail!");
    }
    return query;
}
void DB::execQuery(QSqlQuery &query)
{
    if (!query.exec())
        throw runtime_error("Fail!");
}

Any thoughts?

JonB

@mamsds
My thought would be in that SO question you link to even the OP wrote

Edit 2: It seems that the bug has been fixed in Qt 4.8.5.

I do not have SQLite to test. But if you are saying it can only return a single row from a statement which returns multiple rows I would be pretty surprised anybody could even use it....

Could you make 100% clearly whether you are stating it only returns one row in total or that query.next() returns only one row at a time? Does the query matter, e.g. get rid of the DISTINCT. Even get rid of the prepare(). Tell us what COUNT(...) returns in a statement you claim fails to return all rows. Maybe replicate it by a statement of a SQLite system table (I assume it has such a thing?) so that people can test without your data?

Christian Ehrlicher

You are doing something wrong, can't see what but this is working as expected here (Qt6.8):

QSqlQuery doPrepare()
{
    QSqlQuery q;
    q.prepare(R"(SELECT DISTINCT(val)
FROM test
)");
    return q;
}
int main(int argc, char* argv[])
{
    QCoreApplication a(argc, argv);
    auto db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    if (!db.open())
        return -1;
    QSqlQuery q(db);
    q.exec("CREATE TABLE test (id int, val int)");
    q.exec("INSERT INTO test (id, val) VALUES (1, 42)");
    q.exec("INSERT INTO test (id, val) VALUES (2, 42)");
    q.exec("INSERT INTO test (id, val) VALUES (3, 43)");
    q.exec("INSERT INTO test (id, val) VALUES (4, 43)");
    q = doPrepare();
    if (!q.exec())
        return -2;
    while (q.next())
        qDebug() << q.value(0);
    return 0;
}

mamsds

@Christian-Ehrlicher

You are correct, when I try to prepare a minimally reproducible example, i discover the issue: i close the connection and reopen it, which resets the current query...

Christian Ehrlicher

@mamsds Then please mark the topic as solved.