thomas11

2008-05-14

How hard can it be to check a JDBC connection?

Filed under: tech — Tags: , , , — thomas11 @ 16:57

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.

Ouch, two different standards for SQLState. And a look into one of them shows that the states go into the hundreds. JDBC to the rescue: SQLSyntaxErrorException.

The subclass of SQLException thrown 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.

3 Comments

  1. In a webapp on Tomcat you can use
    the META-INF/context.xml
    to define your db connection like this:

    —–
    So If You do not work on Tomcat you probably will have a place where you define your Connectionensettings – and that would be the right place to put the Querrystring for testing the Connection e.g. “Select 1”
    Best regards Key.

    Comment by Key. — 2008-05-29 @ 17:40

  2. In a webapp on Tomcat you can use
    the META-INF/context.xml
    to define your db connection like this:


    <Resource
    username=”foo”
    password=”psw”
    url=”jdbc:mysql://server/db?autoReconnectForPools=true”
    name=”jdbc/somename”
    driverClassName=”com.mysql.jdbc.Driver”
    maxActive=”8″
    maxIdle=”4″
    maxWait=”4000″
    validationQuery=”SELECT 1″
    testOnBorrow=”true”
    type=”javax.sql.DataSource”
    auth=”Container”
    />

    —–
    So If You do not work on Tomcat you probably will have a place where you define your Connectionensettings – and that would be the right place to put the Querrystring for testing the Connection e.g. “Select 1”
    Best regards Key.

    Comment by Key. — 2008-05-29 @ 17:41

  3. Thanks, Key. Your suggestion is on a different level than what my post is about: you’re discussing where to put a given query. My problem was to come up with a suitable query in the first place. I still don’t know a really generic one – your example “select 1” does not work with all SQL servers out there.

    Cheers, Thomas

    Comment by thomas11 — 2008-05-29 @ 20:22


RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Blog at WordPress.com.

%d bloggers like this: