Select from SQLite with Qt

Question

I try to deal with SQLite database on Qt 4.5.3 on Linux. I've already created DB with another program and it looks like this: screenshot

Then, I try to perform select on Qt:

db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(filename); // Here is FULL path to the database. I've checked it twice :)
bool ok = db.open();
qDebug() << db.tables();

QSqlQuery query;
query.exec("select * from lessons");
qDebug() << query.size();
qDebug() << query.isSelect();
qDebug() << query.isValid();

But debug console says:

("lessons", "weeklessons", "weeks") 
-1 
true 
false 

Why it's select nothing? What I have doing wrong?

Answer 1

Without knowing much about Qt, I was asking myself one question: How does the QSqlQuery know which connection to use. Looking that up in the manual revealed that there are basically two possibilities to execute a query:

1. query = db.exec("select * from lessons");
   Using the database connection, and executing from there.
2. QSqlQuery query( db );
   Constructing the query using the existing database and then executing the query:
   query.exec("select * from lessons");

edit: After reading a bit more, it seems that default connections are supported, so your example should work…

Could you try passing the query as the constructor's value instead? Maybe it works then.

QSqlQuery query("select * from lessons");

Answer 2

In your "QSqlQuery query;" declaration, you have to specify the database connection, e.g. "QSqlQuery query(db)"

Answer 3

Given that your program reports that the query is invalid, have a look at the error message as follows:

QDebug() << query.lastError().text();

This should help you in debugging the problem.

Answer 4

The isValid() method returns true if the query is positionned on a valid record, but after calling exec(), it isn't : you have to move to a valid record first, for example with query.first() or query.next(). See Qt docs : http://doc.qt.nokia.com/4.6/qsqlquery.html

The size() returning -1 doesn't mean there is no result : SQLite is one of the databases for which the size of the query is not directly available (look in the documentation for QSqlDriver::hasFeature()). You can check that rows are returned and find the size with a loop and query.next().

Depending on what you want to do with the result of your select, you could also use QSqlQueryModel instead of QSqlQuery.

Answer 5

This can give you a answer: http://www.qtcentre.org/threads/7904-Is-there-any-other-reason-why-QSqlQuery-size%28%29-returns-1

Answer 6

Thank you very Much! that's what I'm searching for hours!