• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Database Reconnection mechansim at resultset level

 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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.
 
Ranch Hand
Posts: 2234
Eclipse IDE Firefox Browser Redhat
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 .

 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
"I know this defies the law of gravity... but I never studied law." -B. Bunny Defiant tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic