|
|
Posted by Venkata Narayana on 08/28/06 04:35
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
Navigation:
[Reply to this message]
|