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"
Solved
General and Desktop
-
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(); }