0

I'm trying to set a connection timeout for a project that uses Eclipse's Data Tools Platform. The purpose is to prevent waiting if a database connection takes too long to establish. The project supports several database types.

If the project used plain java.sql, I could set the timeout as follows:

final int TIMEOUT_SECONDS = 5;
DriverManager.setLoginTimeout(TIMEOUT_SECONDS);
Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

I tried to look for something similar in org.eclipse.datatools.connectivity classes, such as IConnectionProfile. So far, I have been unable to locate a specific method like DriverManager.setLoginTimeout.

Both java.sql and org.eclipse.datatools.connectivity support adding properties to set connection information:

java.sql

Properties properties = new Properties();
properties.setProperty("user", connectionInfo.username);
properties.setProperty("password", connectionInfo.password);
String connectionTimeoutProperty = getConnectionTimeoutProperty();      // depends on the database vendor
String connectionTimeoutValue = getConnectionTimeoutPropertyValue();    // The value in seconds or milliseconds - also depends on the vendor
properties.setProperty(connectionTimeoutProperty, connectionTimeoutValue);
connection = DriverManager.getConnection(jdbcUrl, properties);

org.eclipse.database.connectivity

Properties properties = new Properties();
// Similar property setup as above.
ProfileManager.getInstance().createProfile(profileName, "Auto Generated", "org.eclipse.datatools.connectivity.db.generic.connectionProfile", properties, "", false);
return ProfileManager.getInstance().getProfileByName(profileName);

Using properties, I've been able to set a timeout for most of the databases. For instance, Oracle supports a property named oracle.net.CONNECT_TIMEOUT expressed in milliseconds, whereas Microsoft SQL Server suports a property named loginTimeout expressed in seconds.

I have yet to find values for Sybase, Teradata, and Netezza.

Question 1: Is there a simpler mechanism to set a login timeout for the Data Tools Platform? Question 2: If there is no better solution, is there a way to set the timeout for Sybase, Teradata, and Netezza? I can handle properties with different names and value types.

HeroSteve
  • 163
  • 1
  • 6

1 Answers1

0

After some research, I discovered that creating an IConnectionProfile actually uses java.sql.DriverManager under the covers. If I set DriverManager.setLoginTimeout before creating the connection profile, the timeout is recognized.

However, I also learned that setLoginTimeout is not guaranteed to be recognized by all drivers. For instance, Informix, Netezza, and PostgreSQL did not use the time set by setLoginTimeout. For my use case, those databases did not apply, so I just used setLoginTimeout.

While I didn't use it for my solution, it is possible to use properties to set the login timeout (see original post). However, the property name and value type (seconds or milliseconds) are driver dependent.

Following are the results for individual databases I tested.

Here are the results of my tests using using setLoginTimeout with a low value (2 seconds) and a URL guaranteed to timeout:

  • DB2 UDB - works
  • DB2 zOS - works
  • HIVE - works
  • INFORMIX - does not work - uses its own timeout (20+ seconds)
  • NETEZZA - does not work - uses its own timeout (20+ seconds)
  • ORACLE - works (about 2-3 seconds slow)
  • POSTGRES - does not work - uses its own timeout 10+ sec
  • SQL_SERVER - works
  • SYBASE - works
  • TERADATA - works

Here are the test results using properties (the property name and value type are in parentheses):

  • DB2 UDB (loginTimeout, seconds) - works
  • DB2 zOS (loginTimeout, seconds) - works
  • HIVE (loginTimeout, seconds) - works
  • INFORMIX (INFORMIXCONTIME, seconds) - works
  • ORACLE (oracle.net.CONNECT_TIMEOUT, milliseconds) - works (about 2-3 seconds slow)
  • POSTGRES - no property found
  • NETEZZA (loginTimeout, seconds) - works if the property is in the URL instead of a separate property.
  • SQL_SERVER (loginTimeout, seconds) - works
  • SYBASE - no property found
  • TERADATA - no property found
HeroSteve
  • 163
  • 1
  • 6