PDA

View Full Version : QT 5.1 Mysql Transaction



Anenja
8th October 2013, 13:31
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?
}

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
8th October 2013, 13:51
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 (http://qt-project.org/doc/qt-4.8/qsqlquery.html#QSqlQuery-2)
Good luck.

Anenja
8th October 2013, 14:32
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();


Thanks again for any kind of help!
ane

ChrisW67
8th October 2013, 21:51
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
10th October 2013, 09:07
Thanks for ur reply. I give it a try and post the result.