0

I've looked everywhere, and I can't work out whats going wrong.

I want to be able to queue up a few database queries, and be able to roll them back if any query should fail. I couldn't get that to work, so I tried reducing the problem to a simple example (started transaction, ran two queries, tried rolling back) but even that isn't working.

This code should; start a transaction, attempt to create two tables, and then be rolled back so no changes appear in the database.

The call to rollback() is returning true, so it says its working, but when I look in the database, the tables were created anyway.

Here's my code:

db = QSqlDatabase.database()
db.transaction()
q = QSqlQuery(db)
q.exec_("create table a ...") #omitted but I have checked they are fully valid queries
q.exec_("create table b ...")
db.rollback()
Owen Tourlamain
  • 174
  • 1
  • 8

1 Answers1

1

If you check the MySQL documentation regarding transactions you will find any query that creates or modifies a table automatically commits the current transaction - hence cannot be rolled back. https://dev.mysql.com/doc/refman/5.1/en/implicit-commit.html

You would have to manually drop the tables yourself.

PaulF
  • 6,673
  • 2
  • 18
  • 29
  • OK, it appears my understanding of transactions is flawed, what is the purpose of rolling back then? It sounds like the only things I could rollback would be `select` and `show` which seems a bit redundant – Owen Tourlamain Sep 07 '15 at 15:07
  • Have a look at these sites : http://stackoverflow.com/questions/2159045/when-should-i-use-transactions-in-my-queries -- http://www.sitepoint.com/mysql-transactions-php-emulation/ -- http://www.tutorialspoint.com/mysql/mysql-transactions.htm – PaulF Sep 07 '15 at 15:16