Universal SELECT query for testing connectivity

Question

Our applications work with MySQL, MS SQL Server and Oracle DBs.

Our C3P0 configuration uses the preferredTestQuery option to test connectivity. Here is our Spring configuration

<b:bean id="phoenixDataSource" 
               class="com.mchange.v2.c3p0.ComboPooledDataSource"
               destroy-method="close">

            <b:property name="driverClass" value="${database.driver}"/>
            <b:property name="jdbcUrl" value="${database.connectionURL}"/>
            <b:property name="user" value="${database.user}"/>
            <b:property name="password" value="${database.password}"/>

            <b:property name="initialPoolSize"><b:value>${database.initialPoolSize:10}</b:value></b:property>
            <b:property name="minPoolSize"><b:value>${database.minPoolSize:1}</b:value></b:property>
            <b:property name="maxPoolSize"><b:value>${database.maxPoolSize:25}</b:value></b:property>
            <b:property name="acquireRetryAttempts"><b:value>${database.acquireRetryAttempts:10}</b:value></b:property>
            <b:property name="acquireIncrement"><b:value>${database.acquireIncrement:5}</b:value></b:property>
            <b:property name="idleConnectionTestPeriod"><b:value>${database.idleConnectionTestPeriod:60}</b:value></b:property>
            <b:property name="maxIdleTime"><b:value>${database.maxIdleTime:10800}</b:value></b:property>
            <b:property name="maxConnectionAge"><b:value>${database.maxConnectionAge:14400}</b:value></b:property>
            <b:property name="preferredTestQuery"><b:value>${database.preferredTestQuery:SELECT 1}</b:value></b:property>
            <b:property name="testConnectionOnCheckin"><b:value>${database.testConnectionOnCheckin:false}</b:value></b:property>
            <b:property name="testConnectionOnCheckout"><b:value>${database.testConnectionOnCheckout:false}</b:value></b:property>

        </b:bean>

SELECT 1 is no valid query for Oracle, but then SELECT 1 FROM DUAL is no valid query for SQL Server unless we create a DUAL object.

Very simple question: is there any SELECT or just harmless query that can be universally used on all platforms to test connectivity?

I can override the database.preferredTestQuery in a property file for Oracle installations but I still wonder if there is a viable universal solution.

[Edit] The connectivity check is made by C3P0 independently from my code. When the query fails (if debug logging is enabled) it writes an error in the logs. No code of mine uses that query, it's part of the C3P0 configuration because the facility checks by itself about the connection being established or not (as it is my understanding). I can do no if-else then. Currently it was feasible to override that query for Oracle installations only by configurations.

And, by the way, even if I could test on the dialect implementation via code, the question could be rephrased like "In order to test connectivity, can I run a universal query or do I have to run a harmless query that is specific to DBMS?" or something like that

Answer 1

harmless query that can be universally used on all platforms to test connectivity?

To just test connectivity? I think your SELECT * FROM DUAL should be fine in all the three databases to test connectivity. In SQL Server, you would get an error Invalid object name ‘dual’.. It means you are connected to the database, and it returned an error saying the table doesn't exist.

Since, if you are not connected, you would get a different error and not the standard error stack for table not exists.

Fortunately, if a table named dual is created in SQL Server, that would be well and good.

But I wonder, how difficult is it to have an IF-ELSE condition in your code to check the database connection and have different queries for different databases.

I think most of the applications which uses multiple database products in the backend, have some property defined to check the connection.