DBCP - validationQuery for different Databases

Question

I use DBCP pool and I want use testOnBorrow and testOnReturn to test if connection is still valid.
Unfortunately I have to set property validationQuery to make it work.

Question: What value should be in validationQuery?

I know, that: validationQuery must be an SQL SELECT statement, that returns at least one row.

Problem is that we use various databases (DB2, Oracle, hsqldb).

Does using validationQuery not degrade the performance? This query runs for every connection the application gets right? — yaswanth, May 04 '17 at 06:40
@yaswanth It's usually only run once at startup I would imagine, unless you have some special circumstance — turbofood, Jan 14 '22 at 00:05

bugs_ · Accepted Answer · 2022-10-14T13:14:42.743

229

There is not only one validationQuery for all databases. On each database you have to use different validationQuery.

After few hours of googling and testing I have collected this table:

Database validationQuery notes

hsqldb - select 1 from INFORMATION_SCHEMA.SYSTEM_USERS
Oracle - select 1 from dual
DB2 - select 1 from sysibm.sysdummy1
mysql - /* ping */ select 1
microsoft SQL Server - select 1 (tested on SQL-Server 9.0, 10.5 [2008])
postgresql - select 1
ingres - select 1
derby - values 1
H2 - select 1
Firebird - select 1 from rdb$database
MariaDb - select 1
Informix - select 1 from systables
Hive - select 1
Impala - select 1

I wrote about it on my blog - validation query for various databases.

In advance there is an example of class, which return validationQuery according to JDBC driver.

Or does anybody have better solution?

edited Oct 14 '22 at 13:14

answered May 21 '12 at 11:21

bugs_

3,544
4
34
39

6

select 1 is also valid on postgresql – Danubian Sailor May 03 '13 at 14:01
1

`select 1` also works for Microsoft SQL Server 2014 - 12.0.2000.8 (X64), Feb 20 2014 20:04:26, Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor) – Yuci Jan 09 '17 at 17:23
1

`select 1` for Hive and Impala – davidemm Jan 02 '18 at 22:32

score 15 · Answer 2 · edited Jan 20 '16 at 13:26

15

For MySQL with the Connector/J driver, there's a lightweight validation query that just sends a ping to the server and returns a dummy result set. The validation query can be (or should start with) exactly the following string:

/* ping */

For more infor refer to the Validating Connections in the MySQL driver manual

edited Jan 20 '16 at 13:26

johan

518
6
18

answered Dec 26 '13 at 09:10

yossis

1,243
2
11
11

score 4 · Answer 3 · answered Jun 26 '15 at 22:06

4

For Informix, The validation query is, select 1 from systables

answered Jun 26 '15 at 22:06

Deepan Prabhu Babu

862
11
18

1

Thanks - I have just added it to list – bugs_ Mar 10 '21 at 07:50

score 1 · Answer 4 · answered Mar 08 '21 at 09:53

1

For MairaDB validation query is "select 1" .

answered Mar 08 '21 at 09:53

Amit P

467
6
20

Thanks - I have just added it to list – bugs_ Mar 10 '21 at 07:51

DBCP - validationQuery for different Databases

4 Answers4

Linked