How to test connection validity ?

_Hugo_ · **Joined:** Thu Feb 02, 2006 9:39 am **Posts:** 19

Hi,

In my application, users could dynamically change DB connection parameters (like database host or database username).

Since openSession() never fails, I wanted to test the session validity after opening it. "session.isConnected()" and "session.isOpen()" seems to always return true even if I'm badly mis-configuring my database (to an inexistant host for instance).

So until now I used a dummy HQL quey ("select 1"), and see if this execution throw an Exception, or not. This works perfectly on MySQL and PostgreSQL but not on Oracle (I could not blame Oracle on that one because "select 1" is not ANSI SQL).

OK, how do you guys test your connection validity ? Any tips ?

--
Hugo

_Hugo_ · **Joined:** Thu Feb 02, 2006 9:39 am **Posts:** 19

Come on, don't be shy ;)

I'm sure I'm not the first guy in the universe that need to test DB connection through hibernate...

pepelnm · **Posted:** Mon Sep 11, 2006 7:04 am

try with:

Code:

boolean isConnected = session.isConnected() && ! session.connection().isClosed();

Please, don't forget to rate.

gilm · **Joined:** Sun Nov 21, 2004 5:21 am **Posts:** 14

in Hibernate2, hibernate.properties have a property "hibernate.dbcp.validationQuery" which is used exactly for that. the default value is "select 1 from dual".

it is not used in Hibernate3 however.

_Hugo_ · **Joined:** Thu Feb 02, 2006 9:39 am **Posts:** 19

Quote:

"select 1 from dual"

does not work on every DBMS. I already look for an universal SQL statement on newsgroup comp.databases, but such a query doesn't seems to exist...

and for the

Quote:

boolean isConnected = session.isConnected() && ! session.connection().isClosed();

it always returns true to me, even if I tried to connected to 0.0.0.0

CWitherow · **Posted:** Mon Sep 11, 2006 11:57 am

I assume you are using a different dialect for each db. So, can you extend each dialect class and create a "testing" sql statement that can be used for that DB?

pepelnm · **Posted:** Mon Sep 11, 2006 12:34 pm

I really doubt you have tried my suggestion.

Are you sure you copy it correctly?

Also you could try with session.getConnection().getMetaData()

_Hugo_ · **Joined:** Thu Feb 02, 2006 9:39 am **Posts:** 19

Quote:

I really doubt you have tried my suggestion.

You're perfectly right !

As I said, I tried "session.isConnected()" and "session.isOpen()" (which did not work as expected), but I mis-read your answer and did not see you use the underlying connection to make the "isClosed()" call. Sorry for the mistake :/

So thanks a lot !! It works perfectly now !

--
Hugo