[Solved] QSQLQuery Error When Value Not Found

[Fahmy]

Hi,

I'm using SQLite to store my workers data. No errors occur when the data exist but when there is not, it'll throw this:

@ASSERT failure in QList<T>::at: "index out of range"@

Here's my code.

@QStringList data;

QSqlQuery query("SELECT * FROM worker_data WHERE worker_id = '" + workerID + "'");

while(query.next())
{
    if (!query.isActive) {
        data << "Problems.";
        return data;
    } else {
        for(int i = 0; i < 40; i++)
        {
            data << query.value(i).toString();
            qDebug() << query.value(i).toString();
        }

        return data;
    }
}@

[Skyrim]

hi

line 3 - try this

@
QSqlQuery query(QString("SELECT * FROM worker_data WHERE worker_id = '%1' ").arg(workerID)));
@

[ObiWan]

As far as I can see, you return nothing, if the query returns an empty result set.
Instead of 2 "return data;" (in line 9 an 17), I would put just one "return data;" after the while loop.

[ChrisW67]

Your construct will (attempt to) return 40 columns of data from the first row of the query result if there are any rows. Are there really 40 columns in your data? If the index out of range is coming from line 13 then this would imply less than 40 columns.

It will not return from the code we can see if the query returns zero rows. If the routine falls off the end of the function without another return the compiler will be issuing an error. If you do return data in the case of no record found and your later code assumes it has 40 elements then it will break in the way you describe.

BTW: You should never build SQL from string fragments unless you absolutely must; your example is not one of those occasions. Search the web for SQL injection.

@
// Something like this is safer
QStringList data;

QSqlQuery query;
query.prepare("SELECT * FROM worker_data WHERE worker_id = ?");
query.bindValue(0, workerID);
if (query.exec()) {
// since you only want the first row no while is required
if (query.next()) {
// automatically adjusts to the column count
for (int i = 0; i < query.record().count(); ++i)
data << query.value(i).toString();
}
}
else
qFatal("Bad query: %s", query.lastError().text());
return data; // zero or more entries
@

[Fahmy]

Thanks for all the replies. I tried everything suggested but its still happen. From debugging, the problem come with this line of code:

@while (query.next())@
or
@if (query.next()) @

Is there other way to check if the sqlite return 'nothing'? because I couldn't find it...

By the way, thanks for remind me about SQL injection. I'm aware about it but it's not what am I worry right now. After all I'll using the program by myself and I've already limit the input to 0-9 digit only.

[Fahmy]

Ok..solved. Using size() to check whether SQLite return any row.

Once again thanks for all :-)