Retrieving row count from QSqlQuery, but got -1

Question

I'm trying to get the row count of a QSqlQuery, the database driver is qsqlite

bool Database::runSQL(QSqlQueryModel *model, const QString & q)
{
    Q_ASSERT (model);

    model->setQuery(QSqlQuery(q, my_db));
    rowCount = model->query().size();
    return my_db.lastError().isValid();
}

The query here is a select query, but I still get -1;

If I use model->rowCount() I get only ones that got displayed, e.g 256, but select count(*) returns 120k results.

What's wrong about it?

score 9 · Answer 1 · edited Nov 24 '15 at 21:11

9

This row count code extract works for SQLite3 based tables as well as handles the "fetchMore" issue associated with certain SQLite versions.

QSqlQuery query( m_database );

query.prepare( QString( "SELECT * FROM MyDatabaseTable WHERE SampleNumber = ?;"));
query.addBindValue( _sample_number );

bool table_ok = query.exec();
if ( !table_ok )
{
    DATABASETHREAD_REPORT_ERROR( "Error from MyDataBaseTable", query.lastError() );
}
else
{
    //  only way to get a row count, size function does not work for SQLite3
    query.last();
    int row_count = query.at() + 1;
    qDebug() << "getNoteCounts = " << row_count;
}

edited Nov 24 '15 at 21:11

whereisSQL

638
2
13
23

answered Nov 24 '15 at 20:33

SportPilot

101
1
5

Am I correct is saying that doing this makes it so you can no longer use setForwardOnly() since you will need to seek back to first when you want to iterate over the results? I currently use this method to get the row count because I need it to calculate a maximum number of steps for a progress bar ahead of time; however, iterating over the results is pretty slow even for only a few thousand. It seems being forward only could help a lot but then I can't check the row count. Would it be faster to repeat the query again but with COUNT to get it that way? – oblivioncth Aug 15 '20 at 17:36

score 6 · Accepted Answer · answered Jun 08 '13 at 06:06

The documentation says:

Returns ... -1 if the size cannot be determined or if the database does not support reporting information about query sizes.

SQLite indeed does not support this.

Please note that caching 120k records is not very efficient (nobody will look at all those); you should somehow filter them to get the result down to a manageable size.

score 0 · Answer 3 · answered Jul 16 '23 at 10:03

0

Got the same problem,

query.numRowsAffected() works for me.

answered Jul 16 '23 at 10:03

Zhang

3,030
2
14
31

Retrieving row count from QSqlQuery, but got -1

3 Answers3