Checking if DB Connection is active or not

[Venkata Narayana]

Hi,
You all may be knowing that Connection.isClosed() does not tells us
if the underying DB connection is active or not; it only checks if
Connection.close() had been previously called or not.
One sure shot way to find out this is by executing some dummy SELECT
query and catching it via SQLException.

This could be done in various DB's as follows:
SELECT * from 1 (MS SQL)
SELECT * from DUAL(Oracle)

My question is what if you use some other DB , which is not famous as
the above.
This could still be achieved by creating dummy table with one column
and querying it. One pitfall of doing this approach is we may not have
create permissions to create table. Even if we have permissions to
create table, you need to do the following, if you need to check DB
Connection every time.

a) Create Table
b) Use SELECT query
c) Drop table

You may ask me why we need to use drop table. This is because, we can
not create many tables and keep them alive if we were to check (DB
Conn) it for 100 times. One way is we can use IF NOT EXISTS along with
Create table. Unfortunately, this command is not supported by all DB
vendors. So, this is ruled out.

One more way of doing is writing simple stored procedure that returns
plain constant. Unfortunatley the syntax for Stored procedures is
different for different DB Vendors.

So, do we have a correct way of finding if DB connection is active,
that would work on all DB's ?

Fortunately, there is a way to do this.
We could use Connection.getMetaData().getTables(null,null,null, null).
We could use this way as this would surely get the number of tables
present at that moment. How many tables are present in a DB will not
be cached as this may change dynamically. One disadvantage of using
this approach is performance. What if a DB has 1000 tables, it tries to
get the names of 1000 tables and it is performance hit.

Is there a solution for this?. Yes, we can use getTables method by
invoking only against the SYSTEM table types. I am sure any DB will
not have many system tables.
So, our call would be,

Conn.getMetaData().getTables(null,null,null,new String[]{"SYSTEM
TABLE"});

The above statement is expected to give whether connection is active;
if connection is not active, then it throws SQLException. And best part
is it will work on all DB Drivers.

What if some JDBC driver does not implement the above getTables() call,
then we would get some AbstractMethodError that can be caught using
LinkageError. So, finally code for checking if connection is active or
not is as follows:

try {
ResultSet rs = conn.getMetaData().getTables(null,null,null,new
String[]{"SYSTEM TABLE"});
} catch (SQLException e) {
conn.close();// use try catch block here to catch SQLException for
Conn.close();
//call to open new DB connection.
getNewConnection();
}catch(LinkageError e){
conn.close();// use try catch block here to catch SQLException for
Conn.close();
//call to open new DB connection.
getNewConnection();
}
}

This limitation (if it can be called) is going to be fixed for JDBC
4.0 implemented drivers(if they implement it in right way).

Any comments on this would be appreciated.

Regards,
Venkata Narayana

[Venkata Narayana]

In the above one, please read it as SELECT 1 instead of SELECT * from 1

Venkata Narayana wrote:

Hi,
You all may be knowing that Connection.isClosed() does not tells us
if the underying DB connection is active or not; it only checks if
Connection.close() had been previously called or not.
One sure shot way to find out this is by executing some dummy SELECT
query and catching it via SQLException.

This could be done in various DB's as follows:
SELECT * from 1 (MS SQL)
SELECT * from DUAL(Oracle)

My question is what if you use some other DB , which is not famous as
the above.
This could still be achieved by creating dummy table with one column
and querying it. One pitfall of doing this approach is we may not have
create permissions to create table. Even if we have permissions to
create table, you need to do the following, if you need to check DB
Connection every time.

a) Create Table
b) Use SELECT query
c) Drop table

You may ask me why we need to use drop table. This is because, we can
not create many tables and keep them alive if we were to check (DB
Conn) it for 100 times. One way is we can use IF NOT EXISTS along with
Create table. Unfortunately, this command is not supported by all DB
vendors. So, this is ruled out.

One more way of doing is writing simple stored procedure that returns
plain constant. Unfortunatley the syntax for Stored procedures is
different for different DB Vendors.

So, do we have a correct way of finding if DB connection is active,
that would work on all DB's ?

Fortunately, there is a way to do this.
We could use Connection.getMetaData().getTables(null,null,null, null).
We could use this way as this would surely get the number of tables
present at that moment. How many tables are present in a DB will not
be cached as this may change dynamically. One disadvantage of using
this approach is performance. What if a DB has 1000 tables, it tries to
get the names of 1000 tables and it is performance hit.

Is there a solution for this?. Yes, we can use getTables method by
invoking only against the SYSTEM table types. I am sure any DB will
not have many system tables.
So, our call would be,

Conn.getMetaData().getTables(null,null,null,new String[]{"SYSTEM
TABLE"});

The above statement is expected to give whether connection is active;
if connection is not active, then it throws SQLException. And best part
is it will work on all DB Drivers.

What if some JDBC driver does not implement the above getTables() call,
then we would get some AbstractMethodError that can be caught using
LinkageError. So, finally code for checking if connection is active or
not is as follows:

try {
ResultSet rs = conn.getMetaData().getTables(null,null,null,new
String[]{"SYSTEM TABLE"});
} catch (SQLException e) {
conn.close();// use try catch block here to catch SQLException for
Conn.close();
//call to open new DB connection.
getNewConnection();
}catch(LinkageError e){
conn.close();// use try catch block here to catch SQLException for
Conn.close();
//call to open new DB connection.
getNewConnection();
}
}

This limitation (if it can be called) is going to be fixed for JDBC
4.0 implemented drivers(if they implement it in right way).

Any comments on this would be appreciated.

Regards,
Venkata Narayana

[joeNOSPAM]

Venkata Narayana wrote:

In the above one, please read it as SELECT 1 instead of SELECT * from 1

Venkata Narayana wrote:

Hi,
You all may be knowing that Connection.isClosed() does not tells us
if the underying DB connection is active or not; it only checks if
Connection.close() had been previously called or not.
One sure shot way to find out this is by executing some dummy SELECT
query and catching it via SQLException.

This could be done in various DB's as follows:
SELECT * from 1 (MS SQL)
SELECT * from DUAL(Oracle)

My question is what if you use some other DB , which is not famous as
the above.
This could still be achieved by creating dummy table with one column
and querying it. One pitfall of doing this approach is we may not have
create permissions to create table. Even if we have permissions to
create table, you need to do the following, if you need to check DB
Connection every time.

a) Create Table
b) Use SELECT query
c) Drop table

You may ask me why we need to use drop table. This is because, we can
not create many tables and keep them alive if we were to check (DB
Conn) it for 100 times. One way is we can use IF NOT EXISTS along with
Create table. Unfortunately, this command is not supported by all DB
vendors. So, this is ruled out.

One more way of doing is writing simple stored procedure that returns
plain constant. Unfortunatley the syntax for Stored procedures is
different for different DB Vendors.

So, do we have a correct way of finding if DB connection is active,
that would work on all DB's ?

Fortunately, there is a way to do this.
We could use Connection.getMetaData().getTables(null,null,null, null).
We could use this way as this would surely get the number of tables
present at that moment. How many tables are present in a DB will not
be cached as this may change dynamically. One disadvantage of using
this approach is performance. What if a DB has 1000 tables, it tries to
get the names of 1000 tables and it is performance hit.

Is there a solution for this?. Yes, we can use getTables method by
invoking only against the SYSTEM table types. I am sure any DB will
not have many system tables.
So, our call would be,

Conn.getMetaData().getTables(null,null,null,new String[]{"SYSTEM
TABLE"});

The above statement is expected to give whether connection is active;
if connection is not active, then it throws SQLException. And best part
is it will work on all DB Drivers.

What if some JDBC driver does not implement the above getTables() call,
then we would get some AbstractMethodError that can be caught using
LinkageError. So, finally code for checking if connection is active or
not is as follows:

try {
ResultSet rs = conn.getMetaData().getTables(null,null,null,new
String[]{"SYSTEM TABLE"});
} catch (SQLException e) {
conn.close();// use try catch block here to catch SQLException for
Conn.close();
//call to open new DB connection.
getNewConnection();
}catch(LinkageError e){
conn.close();// use try catch block here to catch SQLException for
Conn.close();
//call to open new DB connection.
getNewConnection();
}
}

This limitation (if it can be called) is going to be fixed for JDBC
4.0 implemented drivers(if they implement it in right way).

Any comments on this would be appreciated.

Regards,
Venkata Narayana

Whatever you do to test a connection, you want it fast and non-taxing
of DBMS resources, so even if you have table-create permissions, you
don't want to do that. The fast thing is a DBMS-specific query:

Sybase, MS: select 1
Oracle: begin null; end; or select 1 from dual
DB2: select 1 from sysdummy
etc.

You can always call DatabaseMetaData.getDatabaseProductVersion()
to figure out what DBMS-specific SQL to send.
If you really must be DBMS-neutral, you can call DatabaseMetaData
getTables() with arguments that define a non-existent table. The DBMS
will still have to look, but the search for a single table
'NONEXISTENT'
won't be too bad.
Lastly, note that whatever you use to test a connection, the
connection
may fail the very instant after your test succeeds, so your subsequent
code will have to be able to deal with a broken connection anyway. In
practice you would only want to test connections that had been sitting
idle for a significant period.

Joe Weinstein at BEA Systems