Imagine a simple connection pool for java.sql.Connection. After the connection has been released back to the pool we have no idea if any transactions are open, any temporary tables created, etc.
Rather than manually checking if getAutoCommit() is false, and then seeing if we need to roll back, calling rollback(), etc I was hoping there would be a reset() function that does something similar to SQL Severs sp_resetconnection stored procedure but is not DBMS dependent. However looking at Connection's API it seems like there is not.
Does such a function exist?
There is not. In fact, even the SQL Server connection pool datasource class does not invoke the sp_resetconnection call, as it depends upon the application server (or other application managing the connection pool) to return the connection to a known state. See http://msdn.microsoft.com/en-us/library/ms378484(v=sql.90).aspx.
Various drivers, servers, etc. may have their own features, but I'm relatively certain that there is no non-proprietary or cross-database analogs to a Java method that would do what the sp_resetconnection proc does.