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

Retinol

Does using validationQuery not degrade the performance? This query runs for every connection the application gets right? – Anility

@Anility It's usually only run once at startup I would imagine, unless you have some special circumstance – Grunt

Answer 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 - /* 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?

Retinol

select 1 is also valid on postgresql – Hindemith

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) – Indoxyl

select 1 for Hive and Impala – Diamond

Answer 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

Mccutcheon

Answer 3

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

Trot

Thanks - I have just added it to list – Retinol

Answer 4

For MairaDB validation query is "select 1" .

Outstretch

Thanks - I have just added it to list – Retinol