SQLite+Qt: Select from table always returns single row

Question

I use the following piece of code to fetch data from SQLite DB using Qt:

QSqlQuery wordQuery( QString( "SELECT id, word FROM Words WHERE ref_id = :ref_id" ) );
wordQuery.bindValue( ":ref_id", refId );
wordQuery.setForwardOnly( true );
wordQuery.exec( );

while( wordQuery.next( ) ) {
    // obtain data from `wordQuery` using QSqlQuery::value( )
}

The Words table do contain rows with ref_id field equal to needed value. I've checked this with sqlbrowser supplied as Qt demo application. But QSqlQuery::next( ) returns true only the first time and I cannot fetch rest of rows.

BTW, here I found similar question but for me backward iteration doesn't work

Below I put the whole function code:

QList<Word> LyricsMasterModel::fetchWords( const DbId &refId )
{
QList<Word> result;

if ( !db.isOpen( ) && !db.open( ) ) {
    return result;
}

QSqlQuery wordQuery;
wordQuery.setForwardOnly( true );
wordQuery.prepare( QString( "SELECT id, word FROM %1 WHERE ref_id = :ref_id" )
    .arg( WORDS_TABLE_NAME ) );
wordQuery.bindValue( ":ref_id", refId );
if ( !wordQuery.exec( ) ) {
    qDebug( ) << "SQL QUERY ERROR:" << wordQuery.lastError( ).text( );
}

const QSqlRecord wordTuple = wordQuery.record( );
const int idIndex = wordTuple.indexOf( "id" );
Q_ASSERT( -1 != idIndex );
const int wordIndex = wordTuple.indexOf( "word" );
Q_ASSERT( -1 != wordIndex );

while (wordQuery.next()) {
    Word word;

    word.setId( wordQuery.value( idIndex ).toByteArray( ) );
    word.setSongId( refId );
    word.setWord( wordQuery.value( wordIndex ).toString( ) );

    result << word;
}

db.close( );
return result;
}

Solution: I didn't manage to find the reason of the problem, but the following variant appeared to be the working one:

QSqlQuery wordQuery;
wordQuery.prepare( QString( "SELECT id, word FROM %1 WHERE ref_id = :ref_id" )
    .arg( WORDS_TABLE_NAME ) );
wordQuery.bindValue( ":ref_id", refId );
const bool res = wordQuery.exec( );
Q_ASSERT( res );

if ( wordQuery.last( ) ) {
    do {
        // do stuff
    } while (wordQuery.previous());
}

@LaszloPapp, no, I wasn't. But if I use `wordQuery.exec( "SELECT id, word FROM Words WHERE ref_id = 1" );` and set the `ref_id` parameter inside the query string there is no result rows at all, i.e. `QSqlQuery::next( )` returns false on the first call. — Ivan, Jan 03 '14 at 17:17

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

1

Your code has one major issue for the binding, namely:

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.

So, your query will be executed during the construction since it is not empty, and the bind is already too late for that.

I would personally construct the instance with an empty string as the default, and then do an explicit preparation as per documentation.

So, I would write something like this:

QSqlQuery wordQuery();
wordQuery.setForwardOnly(true);
wordQuery.prepare("SELECT id, word FROM Words WHERE ref_id = :ref_id");
wordQuery.bindValue(":ref_id", refId);
if (!wordQuery.exec())
    qDebug() << "SQL QUERY ERROR:" << wordQuery.lastError().text();

while (wordQuery.next()) {
    // obtain data from `wordQuery` using QSqlQuery::value( )
}

edited Jun 20 '20 at 09:12

Community

1
1

answered Jan 03 '14 at 17:08

László Papp

51,870
39
111
135

It still returns a single row:( – Ivan Jan 03 '14 at 17:26
@Ivan: you mean you do not get any errors and the exec() succeeds fine with true? Can you show your refId and the rows that you expect to be returned? Can you also show the internals of your while loop? The error must be somewhere else, not in this code... – László Papp Jan 03 '14 at 17:28
@Ivan: let me repeat the third time, please tell us if the exec succeeds, and if not, let us know the error message. Also, the record snippet is moot because value() can take a string just fine. – László Papp Jan 03 '14 at 17:39
`exec()` returns true – Ivan Jan 03 '14 at 17:44
@Ivan: as already asked, what is the refId? What happens if you hard code it rather than binding? Can you show your rows to see if they are right, or at least the code, if any, that inserts the rows? – László Papp Jan 03 '14 at 17:45
BTW, in Qt 4.8.4 `value( )` cannot take string as input (http://qt-project.org/doc/qt-4.8/qsqlquery.html#value). As I wrote in the comment to the question if I hardcode the `refId` value like this: `SELECT id, word FROM Words WHERE ref_id = 1` `exec( )` still returns true but `next( )` does not returns true in for the first time, i.e. I cannot get any rows at all. – Ivan Jan 03 '14 at 17:51
Right, you have not mentioned Qt 4, and the default assumption is Qt 5 these days. Either way, show what you insert into the database table. – László Papp Jan 03 '14 at 17:53
The table `Words` has three columns: "id", "ref_id", "word". Content is: (1, 1, "lkjlkj;"), (2, 1, "lkjlkjlk"), (3, 1, "asdasd"), (4, 2, "sdfsdgdfg"), (5, 2, "gdfjgl"), (6, 2, "dfgkl;g") – Ivan Jan 03 '14 at 17:56
@Ivan: have you checked if the query worked with the sqlite command line or any other client for your database table? Just in case... – László Papp Jan 03 '14 at 18:01
Yes, I've checked and result is confusing: the query `SELECT id, word FROM Words WHERE ref_id = 1` returns nothing but other queries (like `select *`, `select * where id = 1`) return what is expected – Ivan Jan 03 '14 at 18:04
Right, so your problem is not actually with the Qt code since even an sqlite execute for the query does not work as you expect... Are you sure you have not made any typo? Anyway, this sounds like a voodoo, so please paste a self-contained code that I can run manually and test it. It really should not be this hard. – László Papp Jan 03 '14 at 18:05
I didn't find the solution, but I found some dumb workaround instead (See the question). And since it works, the problem is rather in Qt's implementation than in my queries. – Ivan Jan 04 '14 at 05:11
1

@Ivan: what happens if you drop `wordQuery.setForwardOnly(true);` from my code? You refuse to provide a self-contained snippet, and you blame Qt? :) – László Papp Jan 04 '14 at 05:13

SQLite+Qt: Select from table always returns single row

1 Answers1