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

Solution 1

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 - 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?

Solution 2

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

Solution 3

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

Solution 4

For MairaDB validation query is "select 1" .

View more solutions

Comments

[Danubian Sailor]

select 1 is also valid on postgresql

[Yuci]

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 <X64> (Build 7601: Service Pack 1) (Hypervisor)

[davidemm]

select 1 for Hive and Impala

[bugs_]

Thanks - I have just added it to list

[bugs_]

Thanks - I have just added it to list