Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Query about rollback() method

 
Sujoy Choudhury
Ranch Hand
Posts: 136
Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?


 
Scott Selikoff
author
Saloon Keeper
Posts: 4010
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Sujoy Choudhury
Ranch Hand
Posts: 136
Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4010
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 136
Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4010
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 136
Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4010
18
Eclipse IDE Flex Google Web Toolkit
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@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
Posts: 136
Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, I tried with savepoint API on Connection object.
But unfortunately that didn't work.

I didn't get time to debug this.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic