db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <Kristian.Waa...@Sun.COM>
Subject Re: temporary tables and connection pooling
Date Tue, 24 Jun 2008 15:02:06 GMT
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 not 
> 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 work.

> If anyone has thoughts or pointers on this subject I’d be very 
> interested – a found a few threads on the subject:
> http://groups.google.com/group/comp.databases.informix/browse_thread/thread/159846150631624e
> 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