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 temporary tables and connection pooling
Date Tue, 24 Jun 2008 13:19:44 GMT
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.

 

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/thr
ead/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

 

 


Mime
View raw message