JDBC Connection Pooling and Fail-over

[Alex Collins]

The drivers I have can have issues, such as statements failing due to
schema changes and connections being lost due to timeout. I was
wondering if there's any driver that are basic and free that might
wrap the existing connection to provide these features?

 

[John B. Matthews]

The drivers I have can have issues, such as statements failing due to
schema changes and connections being lost due to timeout. I was
wondering if there's any driver that are basic and free that might
wrap the existing connection to provide these features?

Can you elaborate on this? The drivers I've used implement
ConnectionPoolDataSource*, which inherits methods to manage the
timeout. Moreover, I don't understand what a driver can do to
accommodate schema changes, other than report the failure of an
errant command.

*<http://java.sun.com/javase/6/docs/api/javax/sql/ConnectionPoolDataSource.html>

 

[Lew]

The drivers [presumably he means JDBC drivers] I have can have issues, such as statements failing due to
schema changes and connections being lost due to timeout. I was
wondering if there's any driver that are basic and free that might
wrap the existing connection to provide these features?

What drivers? What database?

Timeouts come from the database, typically - you have to configure the
DBMS to have a different timeout. The most a Java class can do is set
a DB-specific property to affect this, unless it breaks encapsulation
to dig into specific non-standard methods for a particular driver, but
drivers are not usually set up to support that..

Can you elaborate on this? The drivers I've used implement
ConnectionPoolDataSource*, which inherits methods to manage the
timeout.

The login timeout. That says nothing about connection timeout.

Moreover, I don't understand what a driver can do to accommodate
schema changes, other than report the failure of an errant command.

How in the world can one expect a statement to succeed if the schema
changes? Of course there's an error there! Duhh.

*<http://java.sun.com/javase/6/docs/api/javax/sql/ConnectionPoolDataSou...>

Nothing here addresses the OP's questions.

 

[John B. Matthews]

The drivers [presumably he means JDBC drivers] I have can have
issues, such as statements failing due to schema changes and
connections being lost due to timeout. I was wondering if there's
any driver that are basic and free that might wrap the existing
connection to provide these features?

What drivers? What database?

Timeouts come from the database, typically - you have to configure the
DBMS to have a different timeout. The most a Java class can do is set
a DB-specific property to affect this, unless it breaks encapsulation
to dig into specific non-standard methods for a particular driver, but
drivers are not usually set up to support that..

Can you elaborate on this? The drivers I've used implement
ConnectionPoolDataSource*, which inherits methods to manage the
timeout.

The login timeout. That says nothing about connection timeout.

Ah, I see.

Moreover, I don't understand what a driver can do to accommodate
schema changes, other than report the failure of an errant command.

How in the world can one expect a statement to succeed if the schema
changes? Of course there's an error there! Duhh.
[...]

Nothing here addresses the OP's questions.

Indeed, tuning timeouts and synchronizing schema changes are part of the
development cycle. It seems that a different [JDBC] driver is unlikely
to address the problems. Perhaps more details will be forthcoming.

 

[Martin Gregorie]

The drivers I have can have issues, such as statements failing due to
schema changes and connections being lost due to timeout. I was
wondering if there's any driver that are basic and free that might wrap
the existing connection to provide these features?

It sounds to me more like issues with your application. As others have
said, you haven't mentioned anything that can or should be handled by a
JDBC driver.

Are you handling SQL exceptions correctly? If you haven't written the
exception handler to deal with chained SQLExceptions and to display or
log the diagnostic information in each exception in the chain then you're
throwing away a lot of useful information.

 

[Alex Collins]

I have to be honest, I'm pretty new to JDBC. The driver I'm using is
for IBM Informix, and this will return a series of recognisable errors
when the schema changes or the connection to the database is lost.
Simply re-preparing or reconnecting and then executing again will
solve the issue, and this is something the caller need not even be
aware of.

If I had a driver which automatically re-prepares the SQL statement,
or reconnect to the database transparently to the caller. I was
thinking this is something that would not just apply to Informix, but
to any networked based drivers (pretty much all of them). I'd envisage
a class which you tell which driver to use. When you ask it execute
some SQL, it would attempt to do so using the driver, if that fails
due to a lost connection (for example) reconnect (perhaps by getting
another connection from the pool) and re-prepare the SQL. If it fails
a second time. then throw and exception.

Does this make it any clearer?

Alex

 

[EricF]

I have to be honest, I'm pretty new to JDBC. The driver I'm using is
for IBM Informix, and this will return a series of recognisable errors
when the schema changes or the connection to the database is lost.
Simply re-preparing or reconnecting and then executing again will
solve the issue, and this is something the caller need not even be
aware of.

If I had a driver which automatically re-prepares the SQL statement,
or reconnect to the database transparently to the caller. I was
thinking this is something that would not just apply to Informix, but
to any networked based drivers (pretty much all of them). I'd envisage
a class which you tell which driver to use. When you ask it execute
some SQL, it would attempt to do so using the driver, if that fails
due to a lost connection (for example) reconnect (perhaps by getting
another connection from the pool) and re-prepare the SQL. If it fails
a second time. then throw and exception.

Does this make it any clearer?

Alex

Here's some text from the Postgres datasource for JBoss:

-->
<new-connection-sql>select 1</new-connection-sql>

<!-- sql to call on an existing pooled connection when it is obtained
from pool. Can be anything, select 1 is valid for PostgreSQL
-->
<check-valid-connection-sql>select 1</check-valid-connection-sql>

So connection checking is presumably done in JBoss, but by the app server, not
a driver.

Eric

 

[Tom Anderson]

I have to be honest, I'm pretty new to JDBC. The driver I'm using is for
IBM Informix, and this will return a series of recognisable errors when
the schema changes or the connection to the database is lost. Simply
re-preparing or reconnecting and then executing again will solve the
issue, and this is something the caller need not even be aware of.

Sounds reasonable. I'm not sure why people were so aghast at your post.

Anyway, try:

http://commons.apache.org/dbcp/
http://proxool.sourceforge.net/
http://sourceforge.net/projects/c3p0/
http://www.source-code.biz/snippets/java/8.htm

Although i'm actually not sure these will do failover - i *think* they'll
pass the failure back to your code and leave it to get a fresh connection
and try again. Since fresh connections are really from a pool, this will
be cheap, but it's still awkward to code. With any luck, though, you'll be
alright - before returning a connection, the pool will validate it, and
this will weed out lost connections. I don't know if it will catch this
schema problem, though.

tom