CODEWITHSUNDEEP
mysqlqtsqliterecord
user30248
user30248

Reputation: 13

QSqlRecord: Access numeric value from SQLite Table in QT, results are empty

The question I have is similar to a few I've seen, but the solutions don't seem to work for me, so I'll add a new one:

I am trying to access a numeric value (the highest user ID) from an SQLite table into my QT Program, with the following query:

SELECT Name, MAX(ID) FROM User_lib;

This works from the command shell, with the expected result.

I then attempt to access the maximal ID value with:

QSqlQuery qry_ID;    
qry_ID.prepare("SELECT Name, MAX(User_lib.ID) FROM User_lib");   
qry_ID.exec()
qDebug() << qry_ID.record().value("ID").toInt();

The result is zero (and empty if I access "Name" with toString())

Also, as a test

qDebug() << "Number of Rows: " << qry_ID.size();

which I've seen in several other answers gives me -1. On the other hand,

qDebug() << "Number of columns: " << qry_ID.record().count();

gives me "Number of columns 2" as expected. It appears that the query gives no results.

At first I thought that I had a problem with my query, but the same thing is happening when I attempt to count the rows in queries that clearly work correctly (table is displayed, I can add elements correctly, etc), so I think I must be doing something wrong in QT.

Upvotes: 0

Views: 880

Answers (2)

user30248
user30248

Reputation: 13

I haven't checked CL's answer above, which I am sure will work as well, but I have managed to find an alternative solution that worked for me and may be interesting. Since QsqlQuery's internal pointer is set to one field ahead of the first record, I have to advance with next. This is the code that worked:

QSqlQuery qry_ID;
qry_ID.prepare("SELECT Name, MAX(ID) FROM User_lib");
qry_ID.exec();   
qry_ID.next();

QString name = qry_ID.value(0).toString();
int IDmax = qry_ID.value(1).toInt();

If you need to access various rows, a while loop is required, but that is a different question.

Upvotes: 1

CL.
CL.

Reputation: 180172

Let's see what you actually get in the command-line shell:

sqlite> .mode columns
sqlite> .header on
sqlite> SELECT Name, MAX(ID) FROM User_lib;
Name        MAX(ID)
----------  ----------
user30248   8562493

So in the output that you get from the query, the second column is not named ID but MAX(ID).

The documentation actually says:

The name of a result column is the value of the "AS" clause for that column, if there is an AS clause. If there is no AS clause then the name of the column is unspecified and may change from one release of SQLite to the next.

So use AS, or access the column by its index.

SQLite computes output rows on demand, so it is not possible to give a count of rows before they have all been read. Therefore, the QuerySize feature is not supported by the SQLite driver.

Upvotes: 1

Related Questions