I am just facing a little problem:
I have to call 4 stored procedures in a sequence (within a single transaction). Commit should only be done in case all 4 stored procedure calls have been successful. In all other cases all already executed calls should be rolled back.
For example the first stored procedure deletes the content of a table and the second one inserts new values.
Is it possible to use the services startTransaction and Rollback/Commit of the JDBC Adapter?
I am just wondering how a rollback is done in case the table is already deleted and there is an implicit commit of the first stored procedure itself?
Does anyone have more information in this?
You can write your code in sucha a way that, if any of your Stored Procedures is failing and you terminate from the flow with an error, in that case, if you are not able to execute your flow completely, then the commit is automatically rolled back
The 4 SPs you need to call are using the same adapter connection?? If yes and if the connection has Transaction Type set as ‘Local_Transaction’ then you can start the transaction before first SP call and rollback in case of any failure OR commit if all SPs execute successfully.
But this can not be achieved if Transaction Type is ‘NO_TRANSACTION’
Thank you for this information.
One additional question–> What happens if the database makes an implicit commit after each stored procedure call?
I guess that in this case I cannot handle any additional explicit transaction on WM side. How can I determine whether there is an implicit transmit on database side?