Catching and avoiding "must rollback" message

Classic Servoy
1

I have written my own database import function that uses a transaction in case anything goes wrong. It takes a tab-delimited text file and adds a record in the database for each line in the file. At the end of reading the import file, the user is asked whether to commit or not. However, if I try to import one or more duplicate primary key(s), all good records created after the first duplicate are saved in the database, anyway.

For example, if I have the following sequence of events…
Start Transaction
Import 60 good records.
Import 5 bad records with duplicate primary keys.
Import 40 good records.
Rollback Transaction…the last 40 good records will STILL be imported!

Now, this is not without feedback on the user’s side. I get an two error messages about each duplicate key: one with the value and column name, and one that says (to the best of my recollection): “SQL Exception: Must Rollback. Must Rollback.”

OK, so is Servoy telling me I have to rollback after attempting to duplicate a primary key in a transaction? I am not doing this because I do not know how to check for it. I am assuming this is why the last 40 records import even if the transaction is rolled back. But how do I check for the last error code? Does anyone have experience with this situation?

I am using MySQL 4 on Mac OS X 10.4 with Servoy 2.2.5.

2

That is definetely not a Servoy error but a mysql error you are quoting. Mysql is not a transactional database unless you replace it’s storage engine. Why don’t you use iAnywhere if you need transactional support?

3

What version of MySQL are you using? What storage engine are you using? You need at least MySQL 4.1.x and InnoDB storage engine to have full transactions support. Have a look at the HowTo I wrote some time ago: HOWTO: Servoy & MySQL miniHowTo - Classic Servoy - Servoy Community.

Anyway I think you will need to check the transaction status anyway, it looks like the sql error for the duplicate key auto rolls back the transaction so the next 40 inserts are written to the database, if you use a loop to import the records (and I guess you are) you could check if there’s a transaction active on the start of each loop iteration and if not stop the import and throw an error message.

4

Right. I understand all of this. I am definitely running MySQL with Transactional support; the tables are all InnoDB.

My question is how to check for the error on the transaction. What function do I use in Servoy?

5

You cannot check the error in the transaction but you can check if the transation is still alive and, presuming that if an error occurs the transaction is automatically rolled back, if it is alive no error occured.

Pseudo code:

// Loop trough the import file lines
for ( var i = 0 ; i < numberOfLines ; i++ )
{
     //Check if a transaction is active
     var hasTransaction = databaseManager.hasTransaction()
     if(!hasTransaction)
     {
         // The transaction was rolled back: we got an error!
         break;
      }
      else
      {
           ... import the line from the file ...
       }
}
6

You are a genius. That would be a great way to check. I put it into my code…

Unfortunately, Servoy doesn’t recognize that the transaction has been rolled back. Any other ideas?

7

The transation is rolled back at some point otherwise you wouldn’t get the last 40 records imported.
You have to discover when the transaction is rolled back; use the debugger and the admin pages to find it out and then you can place the check in the right spot in your code.