Solved QSqlDatabase usage of "SET @.." in a query cause error: "Commands out of sync; you can't run this command now QMYSQL: Unable to commit transaction"

[Vasiliy]

Hi,
My system:

• Ubuntu 16.04
• Qt 5.7
• Qt Creator 4.0.2
• MySQL server Ver 14.14 Distrib 5.7.11, for Linux (x86_64)
• QtSQL plugin compiled

In my code I successfully create database connection create database, create tables, etc. All goes fine until I've started to use "SET @" (sql user variables) in my queries. For example:

    QString query =
        "SET @maxRecords:=" + QString("%1").arg(((SQL_REQUEST_RECORDS_NUM-2)<=0)?10:(SQL_REQUEST_RECORDS_NUM-2)) +";"
        "SELECT @num:= COUNT(*) FROM `" + uuid +"`; "
        "SELECT @dividerCalculated:= FLOOR(COUNT(*)/@maxRecords) FROM `" + uuid +"`; "
        "SELECT @divider:= IF (@num <= @maxRecords,1,@dividerCalculated); "
        "SET @i := 0; "
        "SELECT DATE_FORMAT(AVG(`timeStamp`),'%Y-%m-%d %T') as timeStamp, AVG(`value`) as value "
        " FROM "
        " ( "
        "        SELECT "
        "                @i:=@i+1 as rownum, "
        "                FLOOR(@i/@divider) AS `group`, "
        "                `timeStamp`, "
        "                `value` "
        "        FROM `" + uuid +"` "
        "        ORDER BY `timeStamp` ASC "
        ") AS result "
        "GROUP BY `result`.`group`;";

    QList<QSqlRecord> recs;
    QSqlQuery *pq = new QSqlQuery(m_database);
    //qDebug()<<"m_database.connectionNames():"<<m_database.connectionNames();
    //qDebug()<<"m_database.connectionName():"<<m_database.connectionName();
    qDebug()<< query.toLatin1().constData();

    m_database.transaction();

    pq->prepare(query);
    if (!pq->exec()){
        qDebug()<<"Err: pq "<<pq->lastError().text().toLatin1().data(); //<---------- GOT ERROR HERE
        m_database.rollback();
        return;
    }

    if (!m_database.commit()){
        qDebug()<<"Err: m_database "<<m_database.lastError().text().toLatin1().data();
        m_database.rollback();
        return;
    }


    while ( pq->next() )
    {
        recs.push_back( pq->record() );
        qDebug()<< pq->record();

    }

    qDebug()<<"______________________________________";
    //qDebug()<< query.toLatin1().constData();
    //qDebug()<< sql.record();

Result:

Err: m_database  Commands out of sync; you can't run this command now QMYSQL: Unable to commit transaction

After checking QUERY in MySQL Workbench I've found the query from my code valid. Than I've cut query in my Qt code and found trouble maker:

QString query =  
"SET @var1:=1;"
"SET @var2:=1"

cause the same error:

Commands out of sync; you can't run this command now QMYSQL: Unable to commit transaction

So, my question is:
Seems group of "SET @" or "SELECT" follow one by one in a query make the issue, how can I work around it?

[Vasiliy]

Answer for future generations:

form complex (nested) query as list instead of single QString, each QString in the list is SQL query

    QList<QString> query;

    query   <<"SET @maxRecords:=" + QString("%1").arg(((SQL_REQUEST_RECORDS_NUM-2)<=0)?10:(SQL_REQUEST_RECORDS_NUM-2)) +";\n"
            <<"SELECT @num:= COUNT(*) FROM `" + uuid +"`; \n"
            <<"SELECT @dividerCalculated:= FLOOR(@num/@maxRecords); \n"
            <<"SELECT @divider:= IF (@num <= @maxRecords,1,@dividerCalculated); \n"
            <<"SET @i := 0; \n"
            <<"SELECT DATE_FORMAT(AVG(`timeStamp`),'%Y-%m-%d %T') as timeStamp, AVG(`value`) as value \n"
            " FROM \n"
            " ( \n"
            "        SELECT \n"
            "                @i:=@i+1 as rownum, \n"
            "                FLOOR(@i/@divider) AS `group`, \n"
            "                `timeStamp`, \n"
            "                `value` \n"
            "        FROM `" + uuid +"` \n"
            "        ORDER BY `timeStamp` ASC \n"
            ") AS result \n"
            "GROUP BY `result`.`group`;\n";

And as usual make transaction:

QSqlQuery *pq = new QSqlQuery(m_database);
    pq->setForwardOnly(true);
    m_database.transaction();

     QListIterator<QString> i(query);
     while (i.hasNext()){
         pq->prepare(i.next());
         if (!pq->exec()){
             qDebug()<<"Err: pq "<<pq->lastError().text().toLatin1().data();
             m_database.rollback();
             return;
         }
     }


    if (!m_database.commit()){
        qDebug()<<"Err: m_database "<<m_database.lastError().text().toLatin1().data();
        m_database.rollback();
        return;
    }


    while ( pq->next() )
    {
        recs.push_back( pq->record() );
        //qDebug()<< pq->record();

    }