Efficient SQL test query or validation 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  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

User · Answer

How about  SELECT user     I use this before MySQL  H2 is OK  I don t know others

User · Answer

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

User · Answer

For Oracle the high performing query will be   select  X  from  lt your small table gt  where  lt primay key coulmn gt     lt some value gt    This is from a performance perspective

User · Answer

Assuming the OP wants a Java answer   As of JDBC3   Java 6 there s the isValid   method which should be used rather than inventing one s own method   The implementer of the driver is required to execute some sort of query against the database when this method id called  You - as a mere JDBC user - do not have to know or understand what this query is  All you have to do is to trust that the creator of the JDBC driver has done his her work properly

User · Answer

I use this one   select max table catalog  as x from information schema tables   to check connection and ability to run queries  with 1 row as result  for postgreSQL  MySQL and MSSQL

User · Answer

For tests using select count     it should be more efficient to use select count 1  because   can cause it to read all the column data

User · Answer

For MSSQL   This helped me determine if linked servers were alive   Using an Open Query connection and a TRY CATCH to put the results of the error to something useful   IF OBJECT ID  TEMPDB   TEST CONNECTION   IS NOT NULL DROP TABLE  TEST CONNECTION IF OBJECT ID  TEMPDB   RESULTSERROR   IS NOT NULL DROP TABLE  RESULTSERROR IF OBJECT ID  TEMPDB   RESULTSGOOD   IS NOT NULL DROP TABLE  RESULTSGOOD  DECLARE  LINKEDSERVER AS VARCHAR 25     SET  LINKEDSERVER    SERVER NAME GOES HERE  DECLARE  SQL AS VARCHAR MAX  DECLARE  OPENQUERY AS VARCHAR MAX   --IF OBJECT ID   dbo usp GetErrorInfo    P    IS NOT NULL DROP PROCEDURE usp GetErrorInfo    --GO    ---- Create procedure to retrieve error information    --CREATE PROCEDURE dbo usp GetErrorInfo   --AS   --SELECT      --    ERROR NUMBER   AS ErrorNumber   --     ERROR SEVERITY   AS ErrorSeverity   --     ERROR STATE   AS ErrorState   --     ERROR PROCEDURE   AS ErrorProcedure   --     ERROR LINE   AS ErrorLine   --     ERROR MESSAGE   AS Message    --GO     BEGIN TRY SET  SQL   SELECT 1      --SELECT  SQL SET  OPENQUERY    SELECT   INTO   TEST CONNECTION FROM OPENQUERY      LINKEDSERVER            SQL       --SELECT  OPENQUERY EXEC  OPENQUERY  SELECT   INTO  TEST CONNECTION FROM   TEST CONNECTION DROP TABLE   TEST CONNECTION --SELECT   FROM  TEST CONNECTION END TRY  BEGIN CATCH -- Execute error retrieval routine  IF OBJECT ID  dbo usp GetErrorInfo   IS NOT NULL -- IT WILL ALWAYS HAVE SOMTHING         BEGIN         CREATE TABLE  RESULTSERROR            ErrorNumber        INT           ErrorSeverity     INT           ErrorState        INT           ErrorProcedure    INT           ErrorLine         INT           Message           NVARCHAR MAX                     INSERT INTO  RESULTSERROR         EXECUTE dbo usp GetErrorInfo     END END CATCH  BEGIN      IF  Select ERRORNUMBER FROM  RESULTSERROR WHERE ERRORNUMBER    1038   IS NOT NULL -- 1038  FOR ME SHOWED A CONNECTION ATLEAST           SELECT          0  AS  ErrorNumber                    0 AS  ErrorSeverity              0 AS  ErrorState                 0 AS  ErrorProcedure             0 AS  ErrorLine                  CONCAT  CONNECTION IS UP ON     LINKEDSERVER  AS  Message                  ELSE          SELECT   FROM  RESULTSERROR END   docs microsoft com

User · Answer

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

User · Answer

Just found out the hard way that it is  SELECT 1 FROM DUAL   for MaxDB as well

User · Answer

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  Apache Derby  via daiscog   SELECT 1 FROM SYSIBM SYSDUMMY1  DB2  select count    from systables  Informix

User · Answer

I use  Select COUNT    As X From INFORMATION SCHEMA SYSTEM USERS Where 1 0   for hsqldb 1 8 0

User · Answer

I use this for Firebird  select 1 from RDB RELATION FIELDS rows 1

User · Answer

If your driver is JDBC 4 compliant  there is no need for a dedicated query to test connections  Instead  there is Connection isValid to test the connection   JDBC 4 is part of Java 6 from 2006 and you driver should support this by now   Famous connection pools  like HikariCP  still have a config parameter for specifying a test query but strongly discourage to use it      connectionTestQuery       If your driver supports JDBC4 we strongly   recommend not setting this property  This is for  legacy  databases   that do not support the JDBC4 Connection isValid   API  This is the   query that will be executed just before a connection is given to you   from the pool to validate that the connection to the database is still   alive  Again  try running the pool without this property  HikariCP   will log an error if your driver is not JDBC4 compliant to let you   know  Default  none

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

Examples related to sql

Examples related to connection-pooling