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"



  • 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?



  • 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();
    
        }
    

Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.