|
|
Posted by joeNOSPAM@BEA.com on 08/28/06 15:35
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
Navigation:
[Reply to this message]
|