rollback issue with database

[Djony]

I am developing Qt application which connects to MySQL database. I want to use rollback() method when insertion of some table fails (for example, if insertion of new record in table goes ok, but insertion of record in relation table fails for some reason, I would like to rollback insertion of succesfully inserted record in table). Now, this is my code :

if ((transaction=m_dbm->DBM_setTransaction(this))==true)
		{			
			if (m_dbm->DBM_updateTestCase(testCase,this))
//in update TestCase is several insertions and updates called. I execute in this function several queries
			{				
    	 		if (m_dbm->DBM_commit(this))
    				QMessageBox::information(this,"Message", QString("TestCase table has been successfully updated"),
    				    				QMessageBox::Ok, QMessageBox::NoButton );
			}
    		else 
    		{	    			
    			m_dbm->DBM_callRollback(this);
    			QMessageBox::information(this,"Message", QString("Update of TestCase table has failed"),
    				    				QMessageBox::Ok, QMessageBox::NoButton );
    		}
		}
		else 
		{
			if (m_dbm->DBM_updateTestCase(testCase,this))
    			QMessageBox::information(this,"Message", QString("TestCase table has been successfully updated"),
    				    				QMessageBox::Ok, QMessageBox::NoButton );
    		else
    			QMessageBox::information(this,"Message", QString("Update of TestCase table has failed"),
    				    				QMessageBox::Ok, QMessageBox::NoButton );
		}

if ((transaction=m_dbm->DBM_setTransaction(this))==true)
		{			
			if (m_dbm->DBM_updateTestCase(testCase,this))
//in update TestCase is several insertions and updates called. I execute in this function several queries
			{				
    	 		if (m_dbm->DBM_commit(this))
    				QMessageBox::information(this,"Message", QString("TestCase table has been successfully updated"),
    				    				QMessageBox::Ok, QMessageBox::NoButton );
			}
    		else 
    		{	    			
    			m_dbm->DBM_callRollback(this);
    			QMessageBox::information(this,"Message", QString("Update of TestCase table has failed"),
    				    				QMessageBox::Ok, QMessageBox::NoButton );
    		}
		}
		else 
		{
			if (m_dbm->DBM_updateTestCase(testCase,this))
    			QMessageBox::information(this,"Message", QString("TestCase table has been successfully updated"),
    				    				QMessageBox::Ok, QMessageBox::NoButton );
    		else
    			QMessageBox::information(this,"Message", QString("Update of TestCase table has failed"),
    				    				QMessageBox::Ok, QMessageBox::NoButton );
		}

To copy to clipboard, switch view to plain text mode 

Now, when my insertion fails (which i know by qsqlquery messages) shown code doesn't rolls back data inserted in function DBM_updateTestCase. I don't know what am I doing wrong. Thanx for any reply.

[Djony]

Can anybody answer this at least. Does rollback undoes just last query or all queries in between calling() transaction and calling rollback()?

[wysota]

Which MySQL database are we talking about? Does it support transactions? Use QSqlDriver::hasFeature to test for QSqlDriver::Transactions.

Just to answer the last question - rollback cancels all queries since the beginning of the transaction.

[Djony]

Well, the code of my function DBM_callTransaction() is this:

bool DbmApi::DBM_setTransaction(QWidget * parent)
{
	return m_dbLocalDb.transaction();
}

bool DbmApi::DBM_setTransaction(QWidget * parent)
{
	return m_dbLocalDb.transaction();
}

To copy to clipboard, switch view to plain text mode 

And when you look at the code above you will see that I check does database supports transaction. I've checked, and DBM_callTransaction() returns true .That is why this behavior is strange, at least to me.

[wysota]

What happens if you execute the same SQL code from the MySQL console?

[Djony]

Happens the same thing as when I run the application. It fails on some queries because some values (which by declaration can't be NULL) are empty. That's why queries fail. So, I would like to have option, in cases of this kind, to roll back queries that have been succesfull. See example I have described in my first post on this topic why I need that. Thank you for your replies.

[wysota]

I meant you to check whether rollback rolls back the transaction correctly when using the console.

BEGIN TRANSACTION;
INSERT INTO ...;
ROLLBACK;

BEGIN TRANSACTION;
INSERT INTO ...;
ROLLBACK;

To copy to clipboard, switch view to plain text mode 

[Methedrine]

From what I understood, you are either looking for savepoints in transactions (which mysql doesn't support, afaik), or you want to validate, and possibly correct, your data before starting the transaction.

[wysota]

Maybe it'd work if you used QSqlDriver::beginTransaction() instead of QSqlDatabase::transaction()?