Efficient SQL test query that will work across all (or most) databases

Question

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.

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.

Answer 1

Unfortunately there is no SELECT statement that will always work regardless of database.

Most databases support:

SELECT 1

Some databases don't support this but have a table called DUAL that you can use when you don't need a table:

SELECT 1 FROM DUAL

MySQL also supports this for compatibility reasons, but not all databases do. A workaround for databases that don't support either of the above is to create a table called DUAL that contains a single row, then the above will work.

HSQLDB supports neither of the above, so you can either create the DUAL table or else use:

SELECT 1 FROM any_table_that_you_know_exists_in_your_database

Answer 2

select 1 would work in sql server, not sure about the others.

Use standard ansi sql to create a table and then query from that table.

Answer 3

After a little bit of research along with help from some of the answers here:

SELECT 1

• H2
• MySQL
• Microsoft SQL Server (according to NimChimpsky)
• PostgreSQL
• SQLite

---

SELECT 1 FROM DUAL

• Oracle

---

SELECT 1 FROM any_existing_table WHERE 1=0

• HSQLDB

Answer 4

This works with most databases:

select 1 as x from information_schema.tables where 1=0

It works at least with MySQL, H2, HSQLDB (since 2.0), PostgreSQL. It doesn't work with Apache Derby. All ANSI-SQL databases are supposed to support this metadata table (I have no idea why Apache Derby doesn't). The only problem is the table might not be accessible for some users.