Temporary tables - _really_ close connections

[hoefel]

Hi *,

I would like to use temporary tables. But the problem is that due to the connection pool I cannot close the db connection to do the clean up. (I really mean close, not only mark them as closed)

In 95% of the cases I need to use the temporary tables I'm able to do the clean up by myself, but there are some circumstances I'm not and closing the db connection would be very handy.

Just for the background what I'm doing.
- I want to process several different SQL statements. Some of them create temporary tables.
- In some cases processing of these statements might get aborted and cannot be finished. So clean up work that is sceduled at the end cannot be executed.
- I'm using a UserTransaction from the SessionContext to get this all done with one single db connection
- I'm using MySQL 4.1 and JBoss 3.2.1

So, does anybody has any idea how to close a db connection that is managed in the connection pool. Or is there any way to somehow flush the connection to clean up the temporary tables?

Thanks

Matthias

[adrian.brock]

There is a flush operation on the pool in JMX.

This will close all connections in the pool
and close any curently checked out connections when they are
returned to the pool.

Regards,
Adrian

[markmatthews]

I'm not sure whether JBoss is using MysqlConnectionPoolDataSource, but if it is (or did), and you're using Connector/J 3.1.x (the feature I'm about to mention is implemented in the source tree, and available in nightly snapshots from http://downloads.mysql.com/snapshots.php), when you call getConnection() on the pooled connection, the entire MySQL server-side state (including temp-tables and session variables) is reset.

If you can get to the underlying 'mysql' JDBC connection from JBoss's connection pool wrappers, you could do the same by calling the 'resetServerState()' method.