This is a long, technical and geeky post – skip if you’re not into that!
Today I faced the problem of checking whether a given JDBC Connection is valid. For the sake of simplicity, let’s just say that “valid” means “it works” – you can successfully issue queries with it.
Java 6 introduced the isValid() method for just that. We’re on Java 5, however. There is isClosed(), which probably does two thirds of the job. But one can think of several more or less obscure cases where a Connection that was not formally closed would stop working.
Discussing with a coworker, we quickly came up with a promising approach: After ensuring the connection is not closed, issue a SQL statement and if no exceptions result, it apparently works. Now, ideally, we’d like the SQL statement to not depend on a particular database, and neither on specific database and table names. What we need is a generic query, that works on every SQL-compliant engine and doesn’t make any assumptions about the schema. The problem is that it’s hard to find out, for a given query, whether it’s pure SQL. And not only that: even if it is, that doesn’t mean that all vendors implemented this part of the standard!
For example, I browsed through the PostgreSQL documentation as I remembered it to be well-written and clearly laid out. I found a promising candidate for our generic query, the values statement:
VALUES computes a row value or set of row values specified by value expressions. It is most commonly used to generate a “constant table” within a larger command, but it can be used on its own. […]
A bare VALUES command: VALUES (1, 'one'), (2, 'two'), (3, 'three');
[…] VALUES conforms to the SQL standard, except that LIMIT and OFFSET are PostgreSQL extensions.
Nice! But, being suspicious, I tried it out on MySQL:
mysql> VALUES (1, 'one'), (2, 'two'), (3, 'three');
ERROR 1064 (42000): You have an error in your SQL syntax
Oops. (Oracle doesn’t do it either.) On the other hand, the Postgres docs shatter my hopes concerning a simple SELECT 1, or any arithmetic query:
PostgreSQL allows one to omit the FROM clause. […] Some other SQL databases cannot do this except by introducing a dummy one-row table from which to do the SELECT.
In fact, Oracle doesn’t accept such queries.
I couldn’t come up with a statement satisfying both criteria. For not depending on table names, you could issue a meta query like show tables. But these are not part of the SQL standard – show tables, for instance, is MySQL-specific.
As I didn’t manage to come up with such a query, I tried some pretty desperate things. Let’s explore some of the joys of SQL+JDBC.
What we want to test is not the database itself, but the connection to it. So, can’t we just send any bogus to the server, which will throw an exception in any case, and then distinguish between a SQL exception and a JDBC exception? Well, I was a bit mislead here initially by my Hibernate experience: JDBCException comes from Hibernate. In JDBC, everything that goes wrong throws a SQLException. Still, according to the API, we can distinguish the cases. Any exception related to the database is a SQLException, and it has
a “SQLstate” string, which follows either the XOPEN SQLstate conventions or the SQL:2003 conventions.
The subclass of
SQLExceptionthrown when the SQLState class value is ‘42‘. This indicates that the in-progress query has violated SQL syntax rules.
42 sounds awesome. Let’s check this on our Oracle database by sending the SQL statement “bogus”. Eh, what? SQLState is null. According to Oracle, that shouldn’t be the case. Table 1 in this article, for instance, lists some errors with example queries that should all lead to SQLState 42. However, it happened here using ojdbc14.jar.
OK, that was promising, but didn’t work out. Desperation led me to the next idea: if Java 6 provides this nice isValid(), someone must implement it, right? Let’s look at the source. However, the implementation is not provided by the Java SE, but by the JDBC vendors. And a quick look in the MySQL JDBC driver (Oracle’s is obviously closed source) revealed that they indeed use their own low-level interface to the database. Obviously, given the above mess.
After a couple hours, some of which some were in my free time as I got hooked and it really wasn’t that important for the project (remember, there’s isClosed()), I still don’t know how to check a JDBC connection in a database vendor- and schema-independent way. But it was a fun ride. For a very weird and nerdy definition of fun.