aspose file tools*
The moose likes JDBC and the fly likes Database Reconnection mechansim at resultset level Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Database Reconnection mechansim at resultset level" Watch "Database Reconnection mechansim at resultset level" New topic
Author

Database Reconnection mechansim at resultset level

Krishna Nazare
Greenhorn

Joined: Dec 10, 2010
Posts: 2
Hi,

We're working on providing auto-reconnection mechanism for our application that uses remote database for the backend.

Currently we've incorporated reconnection mechanism at Connection level and Statement level by introducing wrappers for these two classes.

While testing we found that the connection got lost while processing the Resultset.
We're looking at possible ways for providing reconnection and re-execution of the Query which could not be successfully processed due to loss of connection.

Is it a good idea to provide wrapper at ResultSet level and re-process. This appears somewhat risky as the connection may go away after processing many records.
Are there ways to handle losing connection at Resultset level.

It will be great if we can get some ideas, links that sheds some light about this.

thanks,
Krishna
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


While testing we found that the connection got lost while processing the Resultset.
We're looking at possible ways for providing reconnection and re-execution of the Query which could not be successfully processed due to loss of connection.

You might want to look at some of the DataSource implementations out there. The DataSource in JBoss for example will reconnect after a lost connection.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Ravi Kiran Va
Ranch Hand

Joined: Apr 18, 2009
Posts: 2234

Hi dont want to fear you much on this .
could you tell , what datasource you are using ?? Because when i have seen C3P0 Datasource , i have seen autoreconect option



I am sure that this option would be avialable for any other Major DataSource implentation vendors .


Save India From Corruption - Anna Hazare.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

While I'm not experienced with various DataSource implementations, based on what I know about databases I guess there is not an implementation that would allow to fully restore a recordset after connection was lost. Any implementation that would provide stateful restoration of ResultSets would be tightly coupled to a specific database (or databases) that provide features necessary to complete this feat.

For a start, a facility to reload the recordset as it existed when the query was first executed is needed. While eg. Oracle provides this option via the flashback feature, most databases don't. As a result, the recordset opened after reconnecting might contain different rows from the one that was processed before, so the data you process after a reconnect could be internally inconsistent.

You might be able to implement this for a suitable specific database. However, many details would have to be taken care of. It would probably take very much time and would be pretty hard to test. I'd say it is better to invest these resources into resolving the connection issues, so that there is no need to work around lost connections. Or, if the problem is isolated to one or several long-running queries, maybe these could be handled specifically, which would be much easier than a generic solution.

Of course, for the whole time we're ignoring the transaction state. With a lost connection your transaction is gone and cannot be recovered.
Krishna Nazare
Greenhorn

Joined: Dec 10, 2010
Posts: 2
Hi, We're working on Oracle database.
And we've incorporated reconnection mechanism on our own using wrappers in Connection and Statement level. We thought incorporating reconnection and re-execution of the failed statement is risky as the re-executed statement may give a different result set altogether. Also the earlier result set would be partially processed and copied locally, it makes difficult for implementation.

Thanks for your advice, we'll not change the ResultSet behavior.

Regards,
krishna
 
 
subject: Database Reconnection mechansim at resultset level