Checking validity of database connection

[NeilV]

Hello,

I have an application that talks to an SQL Server database. My application works fine, but what i would like to do i have the ability to check if the connection to the database is valid before carrying out any queries on it.
Is there an easy way to test this? My application will be running on a wireless LAN, so I would like to keep network traffic to a minimum.

Thanks,

Neil

 

[sedj]

There are a few tests that can be performed on the Connection object -

getWarnings() (should be null)
isClosed() (should be false)

If these are null and false (respectivly) then the Connection should be OK. However this is not always the cse and, in reality the only real way to test a Connection is to perform some SQL against it.

 

[Diancecht]

A Select clause, for example, would do the trick. If you get data back, then you're connected.

Anyway, that doesn't guarantee that you're next query will work. IMHO, the only way to assure the result of a query is after the query is done.

Cheers.

Dian

 

[Dayton2]

I think that the most effecient method of testing database connectivity is to implement a few design ideas. first, pull out all of the code that has anything to do with a database, connection or otherwise, and put it in its own class/method. once you have done this, write very specific tests around the database class. This should actually be done first, in true test-first development style, but it is still effective to go back and write any missing tests.

this will be extremely helpful b/c anytime you implement new functionality, you can first run a test suite that will ensure a database connection is established.

(very simple example of test)
DatabaseClass conn = new DatabaseClass();
assertnotnull(conn);

Good Luck!!!!

 

[sedj]

Dayton2:
While I agree with having JDBC code organised into a well-thought-out manner, and using pooling, I'm not sure how it helps testing the validity of an actual Connection object.

No matter how OO or well-organised code is, there is still no real way of testing a Connection apart from the two methods I suggested (which only tell you so much). As Dian said, you only really know when you have a success, or failure - as is the case with all TCP/IP connections.

 

[NeilV]

Thanks for your help guys.
I think the only way to test is by success or failure of my queries.

As i am quite new to java could someone explain to me about connection pooling.. i have seen it mentioned before but dont understand what its all about.

Thanks

Neil

 

[sedj]

Connection Pooling is where you have an Object which holds connections (in its simplest form a Vector say). When your application wants a Connection it doesn't get one from the databse server, it gets one from the pool - thus reusing a connection object already there. When you close your Connection object, the pool does not disconnect the netwrok connection, but just returns it to the pool, ready for the next application use.

Tomcat and such app servers have in-built connection pools which you can use. Or we wrote our own, as we found Tomcat /JBoss pools not stable enough.

 

[NeilV]

Thanks for that sedj, do you know of any good resources for how a connection pool can be setup to talk to SQL server 2000? I have had a look on the web but have not found anything..yet.

Neil

 

[sedj]

Are you looking for a pool that runs within a servlet container or J2EE container (like Tomcat/Jboss/Weblogic etc) or for a standalone application that manages connections ?