Thoughts on Testing Database Connectivity

When using a script or a compiled program to test connectivity to a database, there are two general approaches – of which only one is preferred: 



** Test the Connection



** Test the Connection and Table or View access

 

The first method simply attempts to open a connection to the database and returns success or failure (or the error details).



The second method opens a connection AND attempts to read from a table or view and returns some result.

 

The second method is correct. The first method is bullshit.  Why?  Because it doesn't really test if the application can read the resources.  In fact, you could argue that if the application requires writing to the database, that the test should attempt to write to a table.  I've seen so many cases where someone tested the connection and was perplexed as to why their application would still not work.  The root cause in every one of those cases was permissions within the database environment which prevented the application context from performing the actions it expected to accomplish.

 

Testing database writes as part of a routine "test" verification can be messy, so I usually don't recommend that except for unique situations.  In most cases, just test a read operation against the tables or views and hopefully that will provide sufficient validation for your scripts or applications.  I'm not going to bother posting examples because the Internet is overflowing with plenty of examples already (if you search for them you will see what I mean).  In any case, I hope this helps.