I'm using the JTDS driver to communicate with the SQL Server and I'm having trouble managing the connection loss. I am not able to check if connection is dropped. I will detail my problem:
This is my method that creates the connection:
public synchronized Connection NewConnection (ConnectionParameters params) {
try {
String url = "";
if (params.getTipoBanco (). equals ("MySQL")) {
if (! mySqlDriverLoaded)
return null;
url = "jdbc:mysql://"+params.getIpServidor()+":"+params.getPorta()+"/"+params.getNomeBanco()+"?user="+params.getUsuario()+"&password="+params.getSenha();
} else if (params.getTipoBanco (). equals ("SQL Server")) {
if (! sqlServerDriverLoaded)
return null;
url = "jdbc:jtds:sqlserver://"+params.getIpServidor()+":"+params.getPorta()+"/"+params.getNomeBanco()+";user="+params.getUsuario()+";password="+params.getSenha();
} else {
throw new IllegalArgumentException("Type of database invalid data");
}
return DriverManager.getConnection(url);
} Catch (Exception e) {
e.printStackTrace ();
message = "Error in connection:" + e.getMessage ();
return null;
}
}
If the server is offline in creating the connection, you can check because a SQLException will be thrown. The problem lies in the connection drop after it is successfully performed:
Connection connSqlserver = ConnectionFactory.getInstance().newConnection(paramSqlserver);
PreparedStatement stSqlServer = connSqlserver.prepareStatement("select * from operator");
ResultSet rsSqlserver = stSqlserver.executeQuery();
If the connection is dropped before or during the executeQuery(), the application gets locked in the method expecting the connection back. No exception is thrown.
I've tried to use the following means to verify the connection drop:
1
DriverManager.setLoginTimeout(x);
2
statement.setQueryTimeout(x);
3 loginTimeout=x parameter in the connection string
With none of these got success.
If you can give me some more guidance, will be grateful.
Thank you!
FYI, LoginTimeout sets how long you wait to establish a connection. Once connected it has no effect. It looks like the jTDS drive implements socketTimeout which I think should cover your condition.
They also implement a socketKeepAlive. I am not familiar with how they implement it but I assume the poll the connection to either keep it open and\or return an exception if it is dropped. I don't think jTDS offers this but some connection failover functionality might be useful as well.