db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bob Durie" <bob.du...@thirdbrigade.com>
Subject RE: temporary tables and connection pooling
Date Tue, 24 Jun 2008 17:44:58 GMT
Thanks for the comments!

I wouldn't even know where to begin to implement a master procedure to
do this work, efficiently anyways.  I could name my tables in a known
scheme, search the table lists, then drop if they exist, but that is
computationally expensive.  I think a better approach would be to do it
in my java persistence layer, whenever I fetch a temp table, record the
connection that got it, then when I give it back to the pool drop it by

As for the ConnectionPoolDataSource - I didn't even know this existed!!!
We are using physical/logical connections using
org.apache.commons.pool.impl.GenericObjectPool and after a first glance
it seems to be MUCH MORE feature rich, supporting testOnBorrow/Return,
idle timeouts, maxActive, etc..  I may give this other pooling scheme a
shot, but for fear its support may not be across the board for other
db's (we also use SQL Server and Oracle) I will likely have to go with
the track n slash approach.  PLEASE correct me if you think I'm wrong


-----Original Message-----
From: Kristian.Waagan@Sun.COM [mailto:Kristian.Waagan@Sun.COM] 
Sent: Tuesday, June 24, 2008 11:02 AM
To: Derby Discussion
Subject: Re: temporary tables and connection pooling

Bob Durie skrev:
> Hi,
> I have an application that uses JDBC, derby among one of the supported

> databases (I apologize that this is not 100% specific to derby!!). We 
> use apache commons "GenericObjectPool" to pool our connections, hence 
> they persist for a period of time.
> I want to add support for temporary tables into our persistence layer,

> but I'm getting stuck because of our pooling. We use a combination of 
> transactional and auto-commit db accesses, so using "ON COMMIT DELETE 
> ROWS" isn't really practical for the temp tables as the data won't be 
> there if we're in auto-commit.
> Is there some way to issue a command to derby to "flush" its temp 
> tables? Does anyone know of similar commands on other db's? I don't 
> see how I can get this to work unless I simply use a random temporary 
> table name for creation and then hope all the sessions eventually 
> flush out (otherwise they may not ever get cleaned up and just 
> accumulate forever...). The tables must be cleaned or guaranteed to
> exist, otherwise subsequent borrowed sessions MAY have had that same 
> table created before and still have old data.

Hello Bob,

Maybe I have misunderstood what you want to achieve, but if you are 
using Derby's ConnectionPoolDataSource, I believe temporary tables are 
flushed/deleted when you create a new logical connection.
The functionality is available inside Derby, but I don't think it is 
exposed to the end user (i.e. through JDBC or SQL).

Also note that I don't think the client driver supports this in a Derby 
release yet, but it has been fixed in the latest development version 
(and on the dev 10.4 branch).

Just to make it easy for you to determine if I have gotten this right, 
here's what I was thinking about.
ConnectionPoolDataSource cpds = ...
PooledConnection pc = cpds.getPooledConnection()
// Hand this one out to the end user.
Connection c1 = pc.getConnection()
// Create temporary tables here.
// When c1 is returned to the pool (or taken back forcibly), create a 
new logical connection for the next end user.
Connection c2 = pc.getConnection()
// All temporary tables created by c1 will be deleted here, even if they

share the physical connection pc

If you're pooling ordinary physical connections, this approach won't

> If anyone has thoughts or pointers on this subject I'd be very 
> interested - a found a few threads on the subject:
> http://osdir.com/ml/db.mysql.java/2003-10/msg00117.html
> Which seem to indicate my only recourse is to track 
> connectionid->temptablelist and purge/truncate/drop the tables when I 
> return the connection to the pool. I guess this will work but it seems

> laborious.
> Thanks for any help in advance!!
> Bob

View raw message