What is the best strategy to determine that the DBMS...
5 posts in topic
Flat View  Flat View

Posted By:   Rakesh_Sahu
Posted On:   Monday, November 20, 2000 06:21 AM

What is the best strategy to determine that the DBMS is up and that the Connections from my pool are still viable before handing them out?

Re: What is the best strategy to determine that the DBMS...

Posted By:   Anonymous  
Posted On:   Friday, March 30, 2001 11:09 PM

There were a number of responses to this question. I am including a distillation of these plus some additional information to consider.

It should be obvious that: A) Some statement that requires a database engine response must be executed. B) That any interaction with the DBMS engine will take some time and therefore slow down application responsiveness. C) That the validity of the Connection can only be guaranteed as of the time of the last check, meaning that the class using the method must still check for exceptions.

With these in mind, my own conclusions are that the application should handle any problems with invalid Connections using a standard exception handler for this case and that connection pools should have a returnInvalidConnection method to insure that the bad Connection is not checked out again.

For those that still want to validate a Connection from within the pool, I did some testing. One often sees Connection.getMetaData() used for validation. However, some production
quality drivers report zero time spent in this method, even over a 56K modem. This implies to me that those drivers just check for a DatabaseMetaData object existence and return; clearly there is not a trip to the DBMS server and back in zero time. I also considered just issuing a Connection.commit(), but I suspect that there are drivers smart enough to know when no transactions are pending and therefore just swallow the call for efficiency.

In the end, I have concluded that, for certainty across drivers, a query probably must be issued. One possibility would be to create a table with no rows at pool initialization time and perform a SELECT COUNT(*) on it for validation. COUNT() is generally optimized for SELECTs without search criteria. jGuru would be happy to receive feedback from anyone who has
tested timings on other methods guaranteed to force a response from the DBMS engine.

swarraj kulkarni, Modha Kumar, Harikrishna Neerkaje and Ari Manninen also contributed to this answer.

Re: What is the best strategy to determine that the...

Posted By:   Ari_Manninen  
Posted On:   Sunday, March 4, 2001 02:44 AM

Connection.isClosed() method tells if the driver has detected the connection being closed.

There's no way to quarantee that a connection were still open after passing it to application. Usually, it doesn't make sense making exhaustive forehand checking, because:

- it takes time and resources

- the connection can drop just after checking it

Don't take connection for granted, but handle exceptions correctly. If an SQL operation fails due dropped connection, you can catch the exception and e.g. refresh the connection and retry the operation.

Re: What is the best strategy to determine that the...

Posted By:   Harikrishna_Neerkaje  
Posted On:   Monday, February 5, 2001 08:44 PM

public boolean validate () {
try {
catch (SQLException e) {
return false;
return true;

I use this method to check if the dbms is still up and whether the connection is still viable to be used.

Re: What is the best strategy to determine that the...

Posted By:   Modha_Kumar  
Posted On:   Tuesday, November 28, 2000 11:34 AM

Safe strategy would be to do a inexpensive call to the database before handing out the connection. If the call like say get date using the connection failed then get another connection (new) and give it. Or make periodic walkthrough of the entire pool and replace faulty connection.

Re: What is the best strategy to determine that the...

Posted By:   swarraj_kulkarni  
Posted On:   Sunday, November 26, 2000 06:47 AM

There is no way to check that Database is up and running unless you fire a query to talk to the Database. So in your application in the init method, say, you can fire a select query and see whether u get an exception or result. If u get result it means the DB is up and running.

To check if the connection in the pool object is live, u can use the Connection pool code available on the internet from different sites. Or use following strategy

  • Use SQL_EXPIRES attribute to allow the Database to clear the broken connections

  • Fire a default query to fetch the metadata information everytime before you return the connection object to the caller

  • Write your own connection pool class in which you work with the MyConnection object which maintains the Database connection object as well as the life time allowed for that connection and everytime u return the connection, check that the (connection_born_time - current_time)is still less than allowed_life. Yo can have more flexibility in this appraoch. Most of tha available code has this approach
  • About | Sitemap | Contact