• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Sensing Stale connection

 
Babji Reddy
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When using a Datasource for obtaining connection. Is there a way to know whether that connection is a valid (not a stale one) connection, without executing any sample test queries?
 
Reid M. Pinchback
Ranch Hand
Posts: 775
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
May vary with different JDBC drivers, but I don't believe the java.sql/javax.sql API gives you a clear hook for this. Connection pools that do this typically are configured with the query they will use to probe the database. I suppose you could take some kind of optimistic approach - assume nothing is stale until you get a SQLException, and then examine the specific sqlcode to see if suggests a problem with a stale connection.
[ February 01, 2006: Message edited by: Reid M. Pinchback ]
 
Babji Reddy
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In Webshpere App server, I noticed Oracle Datasource as a preTestSql parameter, which ensures that the connection obtained from pool is live one.
But on the other database (DB2) I am not able to find such information. I thought isClosed() will throw an exception if its a stale connection.
And yes you are right. I should really look into the Database driver documenation for an error code that relates to stale connection.
Thanks
 
Reid M. Pinchback
Ranch Hand
Posts: 775
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
On Oracle I think you'll find it is doing a "select 1 from dual" or something similar.
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Babji Reddy:
I thought isClosed() will throw an exception if its a stale connection.

No, isClosed() API documentation is clear and specific; isClosed() is only required to return true when Connection.close() has been called, and may 9or may not) return and Exception for a stale connection. The Javadocs say:


This method generally cannot be called to determine whether a connection to a database is valid or invalid. A typical client can determine that a connection is invalid by catching any exceptions that might be thrown when an operation is attempted.

"an operation" above is some other JDBC operation, such as attempting to prepare a statement.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic