No result in QTSql query

Viewed 75

I've created a function to exeute a query to postgresql database. I call the function with the query string, a boolean in order to tell me if the query is ok or not and an error string. If I try with a correct query (tested in postgresql prompt) I dont enter in xQueryResult.next() loop but xQueryResult is not empty. I use c++17, postgresql database and QTSql library (Qt 5.15)

#include "SqlDatabase.h"
#include <QtSql\QSqlError>
#include <QtSql\QSqlQuery>
#include <QtSql/QSqlRecord>
#include <QJsonObject>
#include <QJsonArray>


QJsonDocument SqlDatabase::fun(const QString &i_xQuery, bool& bOk, QString& strErrorMessage)
{
    QJsonDocument xJsonDoc;
    QSqlQuery xQueryResult = m_pxCurrentDatabase->exec(i_xQuery);

    if (QSqlError::NoError != xQueryResult.lastError().type())
    {
        bOk = false;
        strErrorMessage = QString("%1: %2").arg(__FUNCTION__).arg(xQueryResult.lastError().text());
        return xJsonDoc;
    }

    QJsonArray xRecordsArray;
    int iFieldIndex = 0;
    int iFieldCount = 0;
    xQueryResult.first();
    while (true == xQueryResult.next())
    {
        const QSqlRecord& xSqlRecord = xQueryResult.record();
        QJsonObject xJsonObj;

        if (iFieldCount == 0)
            iFieldCount = xSqlRecord.count();

        for (iFieldIndex = 0; iFieldIndex < iFieldCount; iFieldIndex++)
        {
            QString strKey = xSqlRecord.fieldName(iFieldIndex);
            xJsonObj[strKey] = QJsonValue::fromVariant(xSqlRecord.value(iFieldIndex));
        }

        xRecordsArray.append(xJsonObj);
    }

    QJsonObject xObject;
    xObject["Records"] = xRecordsArray;
    xObject["NumRowsAffected"] = xQueryResult.numRowsAffected();
    xObject["LastInsertId"] = xQueryResult.lastInsertId().toString();
    xJsonDoc.setObject(xObject);

    bOk = true;
    strErrorMessage = "";
    return xJsonDoc;
}

i call the function from main with

 QJsonDocument xResultDoc;
 std::unique_ptr<Exercise::Comm::SqlDatabase> m_pxDatabase;
QString strQuery = "INSERT INTO public.tab1 (id,name_id,descr_id,description,"user") 
VALUES (
'test',
'$Default',
0,
'test',
''
)RETURNING id;"
  bool bOk;
  QString strErrorMessage;
xResultDoc = m_pxDatabase->exec2(strQuery, bOk, strErrorMessage);
2 Answers

you do not need to call first() and then next() just call next(). In your code first result from PG was allways eaten. So, if your query contained one result, then the loop was not entered

#include "SqlDatabase.h"
#include <QtSql\QSqlError>
#include <QtSql\QSqlQuery>
#include <QtSql/QSqlRecord>
#include <QJsonObject>
#include <QJsonArray>


QJsonDocument SqlDatabase::fun(const QString &i_xQuery, bool& bOk, QString& strErrorMessage)
{
    QJsonDocument xJsonDoc;
    QSqlQuery xQueryResult = m_pxCurrentDatabase->exec(i_xQuery);

    if (QSqlError::NoError != xQueryResult.lastError().type())
    {
        bOk = false;
        strErrorMessage = QString("%1: %2").arg(__FUNCTION__).arg(xQueryResult.lastError().text());
        return xJsonDoc;
    }

    QJsonArray xRecordsArray;
    int iFieldIndex = 0;
    int iFieldCount = 0;
    while (xQueryResult.next())
    {
        const QSqlRecord& xSqlRecord = xQueryResult.record();
        QJsonObject xJsonObj;

        if (iFieldCount == 0)
            iFieldCount = xSqlRecord.count();

        for (iFieldIndex = 0; iFieldIndex < iFieldCount; iFieldIndex++)
        {
            QString strKey = xSqlRecord.fieldName(iFieldIndex);
            xJsonObj[strKey] = QJsonValue::fromVariant(xSqlRecord.value(iFieldIndex));
        }

        xRecordsArray.append(xJsonObj);
    }

    QJsonObject xObject;
    xObject["Records"] = xRecordsArray;
    xObject["NumRowsAffected"] = xQueryResult.numRowsAffected();
    xObject["LastInsertId"] = xQueryResult.lastInsertId().toString();
    xJsonDoc.setObject(xObject);

    bOk = true;
    strErrorMessage = "";
    return xJsonDoc;
}

also using code like this xObject["LastInsertId"] = xQueryResult.lastInsertId().toString(); is very dangerous

  1. When while loop is finished the result is positioned after the last record and false is returned
  2. if you insert more then one row the behavior is undefined QT documentation

The code is ok. Bug is in qt version. Updating Qt code works properly.

Related