External database error handling
Hi ,
Just started working with BPMN and BonitaSoft. I'm modeling a entities management process.
I call a sub-process with two tasks that insert data into two different mysql tables.
To deal with possible errors , if "Insert 2" fails i need to delete the data inserted in "Insert 1" and send a error message to the logged user.
I'm struggling to find a way to do that. I have tried to create a groovy script connector to get the ID (primary_key) of the "Insert 1" and after, if "Insert 2" fails then delete the data row with that ID. But I can not connect to the database in a groovy script connector.
If I use only a mySQL connector i'm not able to store the ID (primary_key) anywhere and if "Insert 2" fails I can't know the ID of the data row that i have to delete.
How should I handling this situation ?
Greetings, João
Comments
Thank you #haris.subasic.
I've tried to get the last inserted ID but this approach raises a problem. If there are multiple instances of the process running at one time i can get the wrong ID.
Example:
Instance1 : "First Insert" -> ID = 0 Instance2 : "First Insert" -> ID = 1 Instance1 : "Second Insert" -> here I need the ID of the "First Insert" (Instance1) but if I get the last inserted id it returns 1 because of the Instance2.
Is it possible to store in a variable the inserted data when I make a mysql connector to insert some data in a database ? If this was possible I would store the inserted ID in a variable and after , if I need to delete that data from database I would use the ID stored in the variable.
Yes, you are right, this might be a problem. MySQL documentation states that you get last inserted id per connection, which should avoid the problem you described. But you should test it, I am not sure about its behaviour. And yes, if you write your own connector, you could use JDBC PreparedStatement and use return_generated_keys that should contain the inserted id. Note that you can also consider using process instance ID as something you can put as a column in your database that could help you uniquely identify the data.
I tested the return_generated_keys() method and it works fine. Now i'm learning how to code my own conector.
First I thought that if there was an error, like the one that I described above, was necessary to undo all previous actions and repeat all the process from the begining. Now i'm think in a new approach that consists in trying to execute again the failed step, in order to do not loose all work done up to the failed step.
Which of the ways do you think is the best practice to handle this type of errors ?
Thank you for your help haris.subasic
OK, great, happy to hear that it works. Usually, we would retry to do the same task, without a need to delete and recreate a process instance. This will depend on the nature of the error. If this is something that you expect to be easilly fixed, it would make sense to retry. You can even imagine to have a timer and get back to reexecute the same task after certain period. If it is a technical error, you would reexecute. If it is a "business" error, you might need to update or delete and do again. Hope this helps
I'm struggling a little bit to understand how to handling properly all types of errors.
For example , if a email connector fail there are no way to know if it's a email server problem or if it's caused by an invalid email address. And I think that the two cases have different handling ways. If it is a email server error ( it can be down) there is no big deal because the email is stored in cache and when the server restarts it will be sent. In case of being caused by a invalid email address I should inform the user that something is wrong and ask to fix it.
But , as much as I know there is no way to differentiate the error types , or am I wrong ?
Can you give me an example when a timer could help me in order to handlind erros ?
Thank you, João