I am posting the same question as i did not receive any reply to my earler post. Plz excuse me.
I am finding diff in rolling back some transactions. Actually in my application (ie in Servlet) i have 5 callable statements to execute 5 stored procedures. cstmt1,cstmt2,cstmt3,cstmt4,cstmt5 I have one connection object.
I have to execute these stored procedures in sequence. If any stored procedures returns non zero value i have to stop
execution and roll back all the transactions. If all the 5 stored procedures return 0 then i have to commit the transaction. i have used the following logic to do so. But the problem is, i am failing to rollback the tranaction. SP5 is returning non zero value, its displaying "error in SP5", but is not rolling back all the transactions. Plz let me know how to roll back all the transactions. Is there any mistake in my logic. ??
SP2 and SP3 are executed more than once and are included in for loop.
SP = Stored procedure.
Any help would be appreciated. Thanks in advance POOJA.
I would recommend something more like that. I think that simplifies your code. You will probably want to consider creating your own application exception rather than reusing SQLException as I did above, but I think it illustrates the point.
Also, make sure you have set Connection.setAutoCommit(false) prior to executing any of your SP's.
If you are still having problems, post your actual code and we'll take a look at it. [ July 06, 2004: Message edited by: Blake Minghelli ]
Blake Minghelli<br />SCWCD<br /> <br />"I'd put a quote here but I'm a non-conformist"
Joined: May 19, 2004
Thanks a lot for ur code, its very simple to use and i guess its more appropriate in my situation. But unfortunately the problem still exists, i.e the transaction is not getting rolled back. I guess there is some problem with my 5th stored procedure and not with the code. Its commiting some where in the 5th stored procedure.
I have tried to execute the stored procedures from TOAD and got the same result, ie transactions are getting commited though the 5th SP returns non zero value, hence deduced that there is a problem in 5th SP.
Thanks a lot for ur sample code which really helped me in making my code simple and readable. Thanks once again and i appreciate ur help. Pooja