Auto-reconnect, connection pool connection validation query question

[Jonathan Eric Miller <je...@uchicago.edu>]

I'm in the process of attempting to use Derby with Tomcat and it's built-in 
connection pool. I'm wondering if Derby's JDBC driver as an "autoReconnect" 
property like MySQL has which will automatically reestablish a connection 
that has been broken? Failing that, I'm wondering if anyone has an idea for 
a simple query that could be used as a validation query to verify that the 
connection is valid. Tomcat's connection pool lets you specify a query that 
is tried on connections before it hands them out to you that way you know 
that the connection is valid (i.e. the connection hasn't been timed out by 
the server or otherwise dropped to to network failure and the like). For 
other DBMSes, I've used a simple query of "SELECT '1'". However, this 
doesn't appear to be a valid SQL statement in Derby. Has anyone else ran 
into this problem?

<Context>
<Resource name="jdbc/..."
auth="Container"
type="javax.sql.DataSource"
driverClassName="com.ibm.db2.jcc.DB2Driver"
url="jdbc:derby:net://localhost:1527/..."
username="..."
password="..."
maxActive="5"
maxIdle="5"
maxWait="60000"
validationQuery="SELECT '1'"
removeAbandoned="true"
removeAbandonedTimeout="60"
logAbandoned="true"/>
</Context>

Jon

Re: Auto-reconnect, connection pool connection validation query question

[Jonathan Eric Miller <je...@uchicago.edu>]

Thanks, that works like a charm. Actually, I'm surprised that the connection 
pool wouldn't in all cases check for broken connections by default. It seems 
like it should just be able to check the state of the socket locally if 
nothing else. It seems inefficient to have it perform an extra query each 
time.

Jon

----- Original Message ----- 
From: "Suavi Ali Demir" <de...@yahoo.com>
To: "Derby Discussion" <de...@db.apache.org>
Sent: Thursday, January 20, 2005 4:21 PM
Subject: Re: Auto-reconnect, connection pool connection validation query 
question


> values(1)
>
> This is valid derby sql.
>
> Regards,
> Ali

Re: Auto-reconnect, connection pool connection validation query question

[Suavi Ali Demir <de...@yahoo.com>]

values(1)

This is valid derby sql.

Regards,
Ali


--- Jonathan Eric Miller <je...@uchicago.edu>
wrote:

> I'm in the process of attempting to use Derby with
> Tomcat and it's built-in 
> connection pool. I'm wondering if Derby's JDBC
> driver as an "autoReconnect" 
> property like MySQL has which will automatically
> reestablish a connection 
> that has been broken? Failing that, I'm wondering if
> anyone has an idea for 
> a simple query that could be used as a validation
> query to verify that the 
> connection is valid. Tomcat's connection pool lets
> you specify a query that 
> is tried on connections before it hands them out to
> you that way you know 
> that the connection is valid (i.e. the connection
> hasn't been timed out by 
> the server or otherwise dropped to to network
> failure and the like). For 
> other DBMSes, I've used a simple query of "SELECT
> '1'". However, this 
> doesn't appear to be a valid SQL statement in Derby.
> Has anyone else ran 
> into this problem?
> 
> <Context>
> <Resource name="jdbc/..."
> auth="Container"
> type="javax.sql.DataSource"
> driverClassName="com.ibm.db2.jcc.DB2Driver"
> url="jdbc:derby:net://localhost:1527/..."
> username="..."
> password="..."
> maxActive="5"
> maxIdle="5"
> maxWait="60000"
> validationQuery="SELECT '1'"
> removeAbandoned="true"
> removeAbandonedTimeout="60"
> logAbandoned="true"/>
> </Context>
> 
> Jon
> 
>