File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes unable to roll back storedprocedures,, Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "unable to roll back storedprocedures,, " Watch "unable to roll back storedprocedures,, " New topic

unable to roll back storedprocedures,,

Pooja Agarwal
Ranch Hand

Joined: May 19, 2004
Posts: 37
Hai All,

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.
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
Blake Minghelli
Ranch Hand

Joined: Sep 13, 2002
Posts: 331

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"
Pooja Agarwal
Ranch Hand

Joined: May 19, 2004
Posts: 37
Hai Blake,

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.
I agree. Here's the link:
subject: unable to roll back storedprocedures,,
It's not a secret anymore!