QT 5.1 Mysql Transaction

[Anenja]

Hello there,

i have some mysql qt problems i dont understand. First some speccs: mysql 5.6, qt 5.1, windows7 and ubuntu 12.04. Inno db table.

this simple snippet starts a transaction, inserted data into table1 and commit the data. But the problem is the data will be inserted twice - i cant figure it out why this happens.

QSqlDatabase db = QSqlDatabase::database("dbOne"); //is already opened and all works fine...
qDebug() << db.transaction(); // returns true
QSqlQuery qry("SET autocommit = 0",db); // test purpose
QSqlQuery qry2("INSERT INTO table1(col1) VALUES(1)",db);
if (!qry.exec()){  // returns true
  qDebug() << qry.lastError();
}else{
  qDebug() << qry2.exec();//returns true
  qDebug() << db.commit() << db.lastError().text(); // commit returns true and last error string is empty, But the Data is inserted twice, why?
}

QSqlDatabase db = QSqlDatabase::database("dbOne"); //is already opened and all works fine...
qDebug() << db.transaction(); // returns true
QSqlQuery qry("SET autocommit = 0",db); // test purpose
QSqlQuery qry2("INSERT INTO table1(col1) VALUES(1)",db);
if (!qry.exec()){  // returns true
  qDebug() << qry.lastError();
}else{
  qDebug() << qry2.exec();//returns true
  qDebug() << db.commit() << db.lastError().text(); // commit returns true and last error string is empty, But the Data is inserted twice, why?
}

To copy to clipboard, switch view to plain text mode 

If I try the same thing with a store_procedure and wrap it with the code above (instead of exec query, call storeProc) - the data will be inserted twice. If I remove the qry.exec() line the data is only inserted once.

Another problem when I use a storeProcedure with many inserts on different tables and in a loop in c++ qt the commit() (outside the loop - transaction starts before the loop and commit after the loop) returns an error after i try to commit the data: "commands out of sync you can't run this command now".

I Dont get the point what happens here. A Simple insert, a simple transaction and two behaviour i didnt get...
Thanks for any kind of help!

best regards
ane

---

Added after 11 minutes:

If I ry this in mysql workbench all is fine...

start transaction;
INSERT INTO table1(col1) VALUES(1)
commit; # or rollback all works fine

[toufic.dbouk]

Hello,

QSqlQuery::QSqlQuery ( const QString & query = QString(), QSqlDatabase db = QSqlDatabase() )
Constructs a QSqlQuery object using the SQL query and the database db. If db is not specified, or is invalid, the application's default database is used. If query is not an empty string, it will be executed.

So apparently you are executing the query twice hence the two inserts happen.
Check this link QSqlQuery
Good luck.

[Anenja]

Oh thanks for this information - i really missed this in the docs.
Really big Thanks! This only works for updates/inserts the cTor execute. For Select it doesn´t work... really confusing but anyway,..

Is it possible to create a transaction for a loop? S.th like this.

(this code wont run just for demonstrate purpose)
qDebug() << db.transaction(); 
QSqlQuery qry("SET autocommit = 0",db);
bool allOk = false;
// start loop and insert some data
for (int i = 0; i < 5; ++i){
  QSqlQuery qry2("INSERT INTO table1(col1) VALUES(i)",db);
  int newID = qry2.lastInsertId();
  qry2.exec("CALL greatProc(newID)");
  // get return value of store proc and run again
 if (storeProcRetVal)
  allOk = true;
else{
  allOk = false;  // error and we are done
  beak;
  }
}
 
// looping finished commit or rollback
if (allOk)
  db.commit();
else
  db.rollback();

(this code wont run just for demonstrate purpose)
qDebug() << db.transaction(); 
QSqlQuery qry("SET autocommit = 0",db);
bool allOk = false;
// start loop and insert some data
for (int i = 0; i < 5; ++i){
  QSqlQuery qry2("INSERT INTO table1(col1) VALUES(i)",db);
  int newID = qry2.lastInsertId();
  qry2.exec("CALL greatProc(newID)");
  // get return value of store proc and run again
 if (storeProcRetVal)
  allOk = true;
else{
  allOk = false;  // error and we are done
  beak;
  }
}

// looping finished commit or rollback
if (allOk)
  db.commit();
else
  db.rollback();

To copy to clipboard, switch view to plain text mode 

Thanks again for any kind of help!
ane

[ChrisW67]

Starting a transaction with MySql will automatically disable autocommit, so you might as well remove all the autocommit stuff.

QSqlQuery::prepare() outside the loop and QSqlQuery::bindValue() inside the loop.

[Anenja]

Thanks for ur reply. I give it a try and post the result.