Efficient SQL test query or validation query that will work across all (or most) databases
connection-pooling, sql
Solution
The jOOQ manual's section about the `DUAL` table lists the following for jOOQ's `select(inline(1))` query:
-- Access
SELECT 1 FROM (SELECT count(*) dual FROM MSysResources) AS dual
-- BigQuery, CockroachDB, Exasol, H2, Ignite, MariaDB, MySQL, PostgreSQL,
-- Redshift, Snowflake, SQLite, SQL Server, Sybase ASE, Vertica
SELECT 1
-- MemSQL, Oracle
SELECT 1 FROM DUAL
-- CUBRID
SELECT 1 FROM db_root
-- Db2
SELECT 1 FROM SYSIBM.DUAL
-- Derby
SELECT 1 FROM SYSIBM.SYSDUMMY1
-- Firebird
SELECT 1 FROM RDB$DATABASE
-- HANA, Sybase SQL Anywhere
SELECT 1 FROM SYS.DUMMY
-- HSQLDB
SELECT 1 FROM (VALUES(1)) AS dual(dual)
-- Informix
SELECT 1 FROM (SELECT 1 AS dual FROM systables WHERE (tabid = 1)) AS dual
-- Ingres, Teradata
SELECT 1 FROM (SELECT 1 AS "dual") AS "dual"
Problem
Many database connection pooling libraries provide the ability to test their SQL connections for idleness. For example, the JDBC pooling library c3p0 has a property called `preferredTestQuery`, which gets executed on the connection at configured intervals. Similarly, Apache Commons DBCP has `validationQuery`. Many example queries I've seen are for MySQL and recommend using `SELECT 1;` as the value for the test query. However, this query doesn't work on some databases (e.g. HSQLDB, for which `SELECT 1` expects a `FROM` clause). Is there a database-agnostic query that's equivalently efficient but will work for all SQL databases? Edit: If there's not (which seems to be the case), can somebody suggest a set of SQL queries that will work for various database providers? My intention would be to programmatically determine a statement I can use based on my database provider configuration.