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

sql connection-pooling
Rob Hruska picture Rob Hruska · Sep 8, 2010 · Viewed 123.4k times · Source

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.

Answer

Rob Hruska picture Rob Hruska · Sep 8, 2010

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

or

SELECT 1 FROM INFORMATION_SCHEMA.SYSTEM_USERS

or

CALL NOW()

  • HSQLDB (tested with version 1.8.0.10)

    Note: I tried using a WHERE 1=0 clause on the second query, but it didn't work as a value for Apache Commons DBCP's validationQuery, since the query doesn't return any rows

VALUES 1 or SELECT 1 FROM SYSIBM.SYSDUMMY1

SELECT 1 FROM SYSIBM.SYSDUMMY1

  • DB2

select count(*) from systables

  • Informix

Related questions

Should I set max pool size in database connection string? What happens if I don't?

This is my database connection string. I did not set max pool size until now. public static string srConnectionString = "server=localhost;database=mydb;uid=sa;pwd=mypw;"; So currently how many connections does my application support? What is the correct …

Read More
What happens to an uncommitted transaction when the connection is closed?

Are they rolled back immediately? Are they rolled back after some period of time? Are they left in an uncommitted state? Is the behavior the same if connection pooling is used and the connections are simply reset?

Read More
How to force a SqlConnection to physically close, while using connection pooling?

I understand that if I instantiate a SqlConnection object, I am really grabbing a connection from a connection pool. When I call Open(), it will open the connection. If I call the Close() or Dispose() method on that SqlConnection object, …

Read More