Qt Forum

    • Login
    • Search
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Search
    • Unsolved

    Update: Forum Guidelines & Code of Conduct


    Qt World Summit: Early-Bird Tickets

    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"

    General and Desktop
    1
    2
    670
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • V
      Vasiliy last edited by 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?

      1 Reply Last reply Reply Quote 0
      • V
        Vasiliy last edited by

        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();
        
            }
        
        1 Reply Last reply Reply Quote 0
        • First post
          Last post