QSqlQuery error handling

SPlatten

I have a query to a database that if I take the query and paste into HeidiSQL fails with Out of Memory. If I do the same query in code I get no error and the query returns with empty data, I've googled QSqlQuery and as far as I can see it shouldn't return that the query was successful if there was an error, here is the code with the query:

    QSqlQuery query;
    query.prepare("SELECT"
                  " `binChunk`"
                  " FROM"
                  " `rdf`"
                  " WHERE"
                  " `biDataset`=?"
                  " AND"
                  " `intBlockNo`=?");
    query.addBindValue(lngDatasetID);
    query.addBindValue(lngBlockNo);
    if ( Trainer::queryDB(query) != true )
    {
        return;
    }
//Whilst there is a block to send and the Trainee is online
    QSqlRecord record(query.record());
    int intTotal(record.count());
    if ( intTotal == 0 )
    {
        return;
    }
    if ( query.next() )
    {
        QSqlField field(record.field(0));
        QVariant varValue(field.value());
        if ( varValue.isValid() != true )
        {
            return;
        }
//Send binary data
qdbg() << __FILE__ << ",L" << __LINE__ << " : " << lngBlockNo;
        sendBinary(lngBlockNo, varValue.toByteArray());
    }

The function Trainer::queryDB is static to the class and has the prototype:

static bool queryDB(QSqlQuery& rQuery, bool blnIgnoreError = false);

Here is the implementation of queryDB:

bool Trainer::queryDB(QSqlQuery& rQuery, bool blnIgnoreError)
{
    bool blnSuccess = rQuery.exec();
    if ( blnSuccess != true && blnIgnoreError == false )
    {       
    //Query wasn't successful, get last error
        QSqlError err = rQuery.lastError();
        if ( err.type() != QSqlError::NoError )
        {
            ImportRDF* pImportDialog(ImportRDF::pInstance());
            logError(err);
            if ( pImportDialog != nullptr )
            {
                pImportDialog->closeDialog();
            }
        }
    }
    return blnSuccess;
}

If I run this query in HeidiSQL:

SELECT
LENGTH(`binChunk`)
FROM
`rdf`
WHERE
`biDataset`=1
AND
`intBlockNo`=0;

The result is 134,217,728

jsulm

@SPlatten Do you get valid data with this:

while (query.next()) {
    QByteArray data = query.value(0).toByteArray();
    ...
}

?

jsulm

@SPlatten What does https://doc.qt.io/qt-5.15/qsqlquery.html#executedQuery return after query execution?
Why do you set table and column names in ``?

SPlatten

@jsulm , I've modified the query function:

bool Trainer::queryDB(QSqlQuery& rQuery, bool blnIgnoreError)
{
    bool blnSuccess = rQuery.exec();
qDebug() << rQuery.executedQuery().toLatin1().data();
    if ( blnSuccess != true && blnIgnoreError == false )
    {       
    //Query wasn't successful, get last error
        QSqlError err = rQuery.lastError();
        if ( err.type() != QSqlError::NoError )
        {
            ImportRDF* pImportDialog(ImportRDF::pInstance());
            logError(err);
            if ( pImportDialog != nullptr )
            {
                pImportDialog->closeDialog();
            }
        }
    }
    return blnSuccess;
}

For the query I'm having a problem with the output is:

SELECT  `binChunk` FROM `rdf` WHERE `biDataset`=? AND `intBlockNo`=?

Obviously it isn't including the parameters I've supplied.

[Edit] Modified again and added:

bool Trainer::queryDB(QSqlQuery& rQuery, bool blnIgnoreError)
{
    bool blnSuccess = rQuery.exec();
qDebug() << rQuery.executedQuery().toLatin1().data();
qDebug() << rQuery.lastError().number();
        if ( blnSuccess != true && blnIgnoreError == false )
    {       
    //Query wasn't successful, get last error
        QSqlError err = rQuery.lastError();
        if ( err.type() != QSqlError::NoError )
        {
            ImportRDF* pImportDialog(ImportRDF::pInstance());
            logError(err);
            if ( pImportDialog != nullptr )
            {
                pImportDialog->closeDialog();
            }
        }
    }
    return blnSuccess;
}

Error number is always -1, what does that mean?

jsulm

@SPlatten Why do you set table and column names in ``?

SPlatten

@jsulm , why not? its good practice and encapsulates the text.

SPlatten

@jsulm , So I edited again and replaced .lastError().number() with .lastError().text().toLatin1().data(). There is no error displayed.

jsulm

@SPlatten said in QSqlQuery error handling:

encapsulates the text

Which text? We are talking about table and column names.

What does https://doc.qt.io/qt-5.15/qsqlquery.html#size return after query execution?

SPlatten

@jsulm , Is something wrong with this code:

    QSqlRecord record(query.record());
    int intTotal(record.count());
    if ( intTotal == 0 )
    {
        return;
    }
    if ( query.next() )
    {
        QSqlField field(record.field(0));
        QVariant varValue(field.value());
        if ( varValue.isValid() != true )
        {
            return;
        }
//Send binary data
        sendBinary(lngBlockNo, varValue.toByteArray());

SPlatten

@jsulm size returns 1. Which is correct, but field shows as "" in the debugger, varValue also shows as "" in debugger. When I get into the function sendBinary there is a check for the byte array:

if ( crbaData.length() == 0 ) {
    return;
}

It does return because the array length is 0.

jsulm

@SPlatten said in QSqlQuery error handling:

QSqlRecord record(query.record());
int intTotal(record.count());
if ( intTotal == 0 )
{
return;
}

Why do you need this?

while (query.next()) {
...
}

is enough.

SPlatten

@jsulm , ok, I can remove that, but that doesn't explain the problem.

jsulm

@SPlatten Also please read what https://doc.qt.io/qt-5.15/qsqlrecord.html#count actually returns

SPlatten

@jsulm , I've removed that check but still doesn't explain why there is no error and no data, yet the data exists and the query is correct.

jsulm

@SPlatten Do you get valid data with this:

while (query.next()) {
    QByteArray data = query.value(0).toByteArray();
    ...
}

?

SPlatten

@jsulm , thank you, I really don't know why but prior to today the code I posted was working, I've just modified it using your example and its ok.

KroMignon

@SPlatten AFAIK, QSqlQuery::record() is to be used after QSqlQuery::exec() to get the field information of the current query.
So you can find the indexes of the value and decode faster the results:

if(query.exec("SELECT * from MyTable where status = 2"))
{
     auto record = query.record();
     int nameCol = record.indexOf("name");
     while(query.next())
         qDebug() << "Found:" << query.value(nameCol ).toString();
}

Which is faster as using query.value("name").toString()