What does Connection.isValid(time) actually do to check if connection if valid?

Question

Using OJDBC process does the isValid method use to check if the the connection is still alive? I'm trying to figure out what impact it could have on the database and how heavy this process is. e.g. does it request a column, or just ping the db with a couple bytes of data.

Ploughshare

Answer 1

Each vendor implements jdbc methods differently. For example Oracle's implementation is :

public boolean isValid(int var1) throws SQLException {
    return this.pingDatabase(var1) == 0;
}

And pingDatabase simply executes select x from dual :

int doPingDatabase() throws SQLException {
    Statement var1 = null;

    byte var3;
    try {
        var1 = this.createStatement();
        ((oracle.jdbc.OracleStatement)var1).defineColumnType(1, 12, 1);
        var1.executeQuery("SELECT \'x\' FROM DUAL");
        return 0;
    } catch (SQLException var7) {
        var3 = -1;
    } finally {
        if(var1 != null) {
            var1.close();
        }

    }

    return var3;
}

I believe that other vendors do something similar.

Polysyllable

I'm not questioning your information, but where did you the method from? – Ploughshare

The driver is public and since it is written in Java you can easily decompile it. – Polysyllable

Answer 2

In case of the Oracle JDBC thin driver, what isValid() does depends on the version of the driver and the Database. Starting in 11g, the JDBC thin driver uses OPING which is a very lightweight RPC (the smallest possible roundtrip). Prior to 11gR2 the driver was executing a SELECT query which is more expensive. Also of the database is pre-10g, then OPING isn't supported and the driver executes a SELECT query.

Carry