If an error occurs in updating any of the tables, I use this code to cancel the transaction:
However, if an error occurs in one of the tables, the tables that were updated without error using RecordSet.Edit and RecordSet.Update seemed to ignore the ROLLBACK command and committed to an update anyway. Completely disregarding the ROLLBACK!
I also tried the db.Rollback and db.SQLExecute(“ROLLBACK”) with the same results.
I checked to make sure that db.CommitTransaction was not applied in any part of my code.
Why is ROLLBACK not working. Is this a bug or am I doing something wrong?
Thank you for your reply Norman. I am working with MariaDB tables. If what you are saying is true, that InnoDB and NDB tables are the ONLY tables that are transaction safe, what methods do I use to incorporate a rollback for MariaDB tables?
MySQL cannot guarantee the contents of a RowSet after issuing an Update call (after having previously called EditRow). This means you should not try to modify the contents of a RowSet in a loop. Instead select just the single row you wish to modify.
“MySQL cannot guarantee the contents of a RowSet after issuing an Update call (after having previously called EditRow). This means you should not try to modify the contents of a RowSet in a loop. Instead select just the single row you wish to modify.”
I never use .edit() .update()
I write my full SQL sentences directly to avoid “unknown and undesired things occurring behind de scenes”.
I think it would be one way of trying to avoid the problems you are dealing with.
Ok. That makes sense Rick!
However, I am having trouble finding a “best practice” example of editing several columns of a table using DB.ExecuteSQL. The reason I was using RecordSet.Edit and RecordSet.Update is because it seems to be a streamlined method for editing several columns of a table.
Something by these lines. Nothing tested, even mistypes are possible, written just trying to show a basic idea
Var rows As RowSet = ... // let's skip the populating the rowset part
For Each row As DatabaseRow In rows // let's walk on the rowset
// Update a table
Var sql As String
sql = "UPDATE my_table SET my_field1=?, my_other_field=? WHERE ID=?"
db.ExecuteSQL(sql, row.Column("my_field1").StringValue+" more this: "+change, "the other value", row.Column("ID").IntegerValue)
Catch error As DatabaseException
MessageBox("DB Error: " + error.Message)
I found the ROLLBACK issue. After examining the structure of the tables in the database with phpMyAdmin, I discovered that the tables are of the MyISAM Storage Engine type, which does not support transactions. Basically, using ROLLBACK with MyISAM is useless.