aspose file tools*
The moose likes JDBC and the fly likes Query about rollback() method Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Query about rollback() method" Watch "Query about rollback() method" New topic
Author

Query about rollback() method

Sujoy Choudhury
Ranch Hand

Joined: Sep 17, 2008
Posts: 136

Hello Everyone,

I am iterating over a ResultSet and my code snippet is like this.


Now, if there is an exception the rollback() is getting called. And after then in the while loop execution
it throws error as ResultSet is already closed.

Does that mean, I can't loop through a ResultSet rows after calling rollback().
Does rollback() closes all the JDBC resources?



Thanks and Regards,
~Sujoy
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

Are you setting auto commit off? In my experience, rollback() or commit() is usually the last call you make on a database before releasing the connection.

Do you know the cause of the exception? Is this a SQL exception? If you can avoid the exception by checking existing parameters, that is preferable then letting exceptions happen.


My Blog: Down Home Country Coding with Scott Selikoff
Sujoy Choudhury
Ranch Hand

Joined: Sep 17, 2008
Posts: 136

Hi Scott,
Yes, my connection auto commit is set to false.

But requirement is like this, that I have to process each row, execute a set of transaction in the application,
and if any of those fails, I have to rollback all and proceed to the next row of the ResultSet.

But I guess, I can't do this with an active ResultSet open.

May be I can iterate the ResultSet first, store the values in a list of value objects and then iterate over the list.

Any ideas how can I achieve this with an active ResultSet.
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

That's a common solution, to iterate over the results, store them in a Java Pojo, then handle each separately. The advantage is that you don't have a single result set open for the entire length of the transaction and it's generally easier to debug.
Sujoy Choudhury
Ranch Hand

Joined: Sep 17, 2008
Posts: 136

I found a solution, we can use two different connection here.
One will be for read only and other for updating the DB.

That solves the problem here.
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

Not a great solution for transactional-based environments where one connection might want to read the uncommitted data of the other connection. Also, it still has the issue that your maintaining a single connection for longer than you need to. Generally database resources are expensive and it's a good practice to not keep them open for longer periods of time when you don't need to.
Sujoy Choudhury
Ranch Hand

Joined: Sep 17, 2008
Posts: 136

Right. I understand your point.
But, this is a specific scenario where the process is kind of one time exercise.
And the read only connection holds the DB table which are always read only, no one is manipulating those tables.
The other connection is manipulating different set of tables.

This is just a situation based solution I would say.
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

I understand that your solution works but it's better in the long run to eliminate locking/concurrency issues if you can. The time you invest now may prevent problems in the future such as if you change one of the queries to a shared table.
Sudheer Bhat
Ranch Hand

Joined: Feb 22, 2011
Posts: 75
@Sujoy

Did you try the setSavepoint and rollback(Savepoint savepoint) API's in the connection interface? The savepoints allow you to rollback to a specified point in the transaction.

for eg.

start transaction t1.
some processing here.
establish a savepoint.
some more processing with a possibility of an exception
....
...
exception handler.
you can rollback to the established savepoint.

Though I have used savepoints a lot in PLSQL code, I have never used them in the JDBC. So I have no guarantee that it works or not.
Sujoy Choudhury
Ranch Hand

Joined: Sep 17, 2008
Posts: 136

Yes, I tried with savepoint API on Connection object.
But unfortunately that didn't work.

I didn't get time to debug this.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Query about rollback() method