What is a validationQuery with respect to databases and JNDI?

Question

I seem to be having the same exact problem as the poster of this original SO question.

The problem is, he answered his own question with the following comment:

I solved my problem, I added a validationQuery to my JNDI datasource.

Unfortunately, that doesn't mean anything to me, and doesn't help me solve my problem. So I ask: what is a "validationQuery" with respect to my database (Sybase) and the JNDI entry used to hold the server lookup info?

Thanks in advance.

Answer 1

The validation query is a query run by the data source to validate that a Connection is still open before returning it. Here's Tomcat's definition of a validation query; scroll down to the middle of section "4. Configure Tomcat's Resource Factory", looking for the "validationQuery" attribute.

EDIT

According to this answer, if the validation query fails, no error is thrown; the bad/closed connection is dropped and another connection is created to replace it.

Answer 2

It has no relevance to JNDI, but JDBC datasources.

The validation query is used to validate connections from the datasource connection pool, before those are handed-off to the clients. Usually these are low impact query such as "SELECT 1..." or "SELECT SYSDATE..."

Answer 3

I assume it is a query to test if DB connection works fine. Some sort of ping on the app-server level.

In the original question periodic pinging of the DB keeps the connection opened.