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:
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!!