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.

Is this [tag:sybase-asa], [tag:sybase-ase], [tag:sybase-iq], or [tag:sqlanywhere]? Trying to clean up the [tag:sybase] tag... — Danny Beckett, Mar 15 '13 at 07:45

score 11 · Accepted Answer · edited May 23 '17 at 12:01

11

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.

edited May 23 '17 at 12:01

Community

1
1

answered Mar 13 '13 at 19:09

rgettman

176,041
30
275
357

Thanks @rgettman (+1) - so it's a query that I configure Sybase (the data source) to run prior to returning a connection. I assume it throws an exception that I can catch, or returns some error-value that I can use on the app-side to determine that a connection isn't available, and that I need to try again? Thanks again! – IAmYourFaja Mar 13 '13 at 19:16
And another thing: that article doesn't explain what happens when a connection isn't available? Throws an error? – IAmYourFaja Mar 13 '13 at 19:24

score 2 · Answer 2 · answered Mar 13 '13 at 19:09

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..."

answered Mar 13 '13 at 19:09

Ori Dar

18,687
5
58
72

Thanks @orid (+1) - please see my question under rgettman's answer - I have the same question for you! – IAmYourFaja Mar 13 '13 at 19:17

score 1 · Answer 3 · answered Mar 13 '13 at 19:05

1

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.

answered Mar 13 '13 at 19:05

WeMakeSoftware

9,039
5
34
52

Thanks @Funtik (+1) - please see my question under rgettman's answer - I have the same question for you! – IAmYourFaja Mar 13 '13 at 19:17

What is a validationQuery with respect to databases and JNDI?

3 Answers3

Linked