db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: temporary tables and connection pooling
Date Tue, 24 Jun 2008 13:39:33 GMT
Hi Bob,

I'm not aware of a master flush command in Derby. It may be useful to 
write your own flush() method which you can register as a Derby 
procedure and invoke at session boundaries. Depending on the Java 
support in the other databases you use, you may be able to register this 
method as a procedure in those databases too.

Hope this helps,

Bob Durie wrote:
> 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/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