executeQuery() doesn't time out, blocks indefinitely on network failure

java-programmer, executeQuery() doesn't time out, blocks indefinitely on network failure I am developing an application that must gracefully recover from
network failures, however I noticed that executeQuery() never returns
or throws an exception for a time out when the network goes down (which
I simulated by unplugging my ethernet cable). The TCP connections of
all my pooled database connections remain open and dead even after
plugging the cable back in. Calling queryTimeout() explicitly has no
effect--executeQuery() stays blocked on network IO forever. Is there a
way to time out executeQuery(), or some way to stop it from never
returning?

I'm using version 10.2.0.1.0 of Oracle's JDBC driver with the 1.5 JDK.

Code:

import java.util.*;
import java.sql.*;
import oracle.jdbc.pool.*;

public class Oracle
{
private OracleDataSource oracleDataSource;

public Oracle() throws Exception
{
oracleDataSource = new OracleDataSource();
oracleDataSource.setURL("jdbc:oracle:oci:@dev");
oracleDataSource.setUser("dbp");
oracleDataSource.setPassword("dbp");
oracleDataSource.setConnectionCachingEnabled(true);
}

public void validate()
{
Connection c = null;
Statement s = null;
ResultSet r = null;

try
{
System.err.printf("connecting...\n");
c = oracleDataSource.getConnection();
System.err.printf("connected\n");
s = c.createStatement();
s.setQueryTimeout(1);

for (;;) {
System.err.printf("executing...\n");
r = s.executeQuery("select 1 from dual");
System.err.printf("executed\n");
r.close();
try { Thread.sleep(1000); } catch (Exception e)
{ }
}
}
catch (Exception e)
{
e.printStackTrace();
try { r.close(); } catch (Exception ee) { }
try { s.close(); } catch (Exception ee) { }
try { c.close(); } catch (Exception ee) { }
}
}

public static void main(String[] args)
{
Oracle oracle = null;

try
{
oracle = new Oracle();
oracle.validate();
}
catch (Exception e)
{
e.printStackTrace();
System.exit(1);
}
}
}

Output:
> javac -cp ./ojdbc.jar Oracle.java && java -cp .:./ojdbc.jar Oracle
connecting...
connected
executing...
executed
(ethernet cable unplugged...)
executing...
(i waited a very long time, and nothing happened...)

java-programmer >> executeQuery() doesn't time out, blocks indefinitely on network failure Hi. This is a known issue with all TCP socket clients. If you
wait up to 10 minutes or so, the TCP stack *will* tell the
driver that the socket has gone dead, and you'll get an
exception. The only ways to address this are to either alter
the OS's TCP timeout for your process, and/or try the driver's
Statement.setQueryTimeout() method. Some drivers have
implemented this well, others not so well.

Joe Weinstein at BEA Systems

java-programmer >> executeQuery() doesn't time out, blocks indefinitely on network failure Unfortunately Statement.setQueryTimeout() does not work (I'm using
10.2.0.1.0 of Oracle's JDBC driver with the 1.5 JDK), and
Statement.executeQuery() does not throw an exception after any amount
of time. If someone can run the sample code above, I'd be curious to
see what happens. Are there any software solutions that do not involve
changing the behavior of the TCP stack?

Thanks.

java-programmer >> executeQuery() doesn't time out, blocks indefinitely on network failure On Sun, 15 Jan 2006 20:50:42 -0800, cestreich wrote:

> Unfortunately Statement.setQueryTimeout() does not work (I'm using
> 10.2.0.1.0 of Oracle's JDBC driver with the 1.5 JDK), and
> Statement.executeQuery() does not throw an exception after any amount
> of time. If someone can run the sample code above, I'd be curious to
> see what happens. Are there any software solutions that do not involve
> changing the behavior of the TCP stack?
>
> Thanks.

For a small, simple application you can create a Runnable with a timeout
for each JDBC call. For a complex application I recommend you invest in an
application server with a good persistence and transaction manager.

Chuck