You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@commons.apache.org by "Phil Steitz (JIRA)" <ji...@apache.org> on 2007/12/08 04:51:44 UTC

[jira] Closed: (DBCP-247) ResultSet returned from Statement.getGeneratedKeys has non-delegating Connection

     [ https://issues.apache.org/jira/browse/DBCP-247?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Phil Steitz closed DBCP-247.
----------------------------

    Resolution: Duplicate

This is a duplicate of DBCP-11, which was fixed in r558332.

> ResultSet returned from Statement.getGeneratedKeys has non-delegating Connection
> --------------------------------------------------------------------------------
>
>                 Key: DBCP-247
>                 URL: https://issues.apache.org/jira/browse/DBCP-247
>             Project: Commons Dbcp
>          Issue Type: Bug
>    Affects Versions: 1.2.1
>         Environment: Ubuntu Linux 7.10, JDK 1.5, Tomcat 5.5, MySQL 5.0.45
>            Reporter: Shlomo Swidler
>            Priority: Minor
>
> We have a library that wraps a ResultSet and, when the client code calls ResultSet.close(), our library also closes the underlying Statement and Connection. This is very useful because it allows us to abstract out from the client code whether the Connection needs to be closed when you're done with the ResultSet (as when using DBCP) or not (as when running standalone, with classic MySQL JDBC driver connections).
> Here's a snippet of the library code:
> public class ResultSetWrapper {
>   private ResultSet rs;
>   public ResultSetWrapper(ResultSet rs) {
>     this.rs = rs;
>   }
>   public boolean next() throws SQLException {
>     return rs.next();
>   }
>   public int getInt(int fieldNum) throws SQLException {
>     return rs.getInt(fieldNum);
>   }
>   // ... etc methods delegating to the underlying ResultSet
>   public void close() throws SQLException {
>     Statement st = rs.getStatement();
>     Connection conn = st.getConnection();
>     try {
>       rs.close();
>     } finally {
>       try {
>         st.close();
>       } finally {
>         conn.close(); // Here is where the problem lies
>       }
>     }
>   }
> }
> Now, in order to test our code we are running with maxActive=1 and maxIdle=1, so we never actually use more than one JDBC connection.
> This works great when the ResultSetWrapper wraps a plain-old ResultSet returned from Statement.executeQuery().
> But when I wrap a ResultSet returned from Statement.getGeneratedKeys(), then the underlying JDBC connection is closed (at the "Here is where the problem lies" comment in ResultSetWrapper), not the DBCP DelegatingConnection. Here is an example:
> example database table:
> CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL);
> example test case:
> public class Test {
>   private DataSource dataSource = null;
>   public Test() {
>     // initialize the DataSource
>     // ...
>   }
>   public ResultSetWrapper wrapGeneratedKeysQuery() throws SQLException {
>     Connection conn = dataSource.getConnection();
>     Statement st = conn.createStatement();
>     st.execute("INSERT INTO test (name) VALUES ('foo')");
>     ResultSet rs = st.getGeneratedKeys();
>     return new ResultSetWrapper(rs);
>   }
>   public ResultSetWrapper wrapPlainQuery() throws SQLException {
>     Connection conn = dataSource.getConnection(); // Here is where we run out of connections in the pool
>     Statement st = conn.createStatement();
>     ResultSet rs = st.executeQuery("SELECT id FROM test LIMIT 1");
>     ResultSetWrapper rsw = new ResultSetWrapper(rs);
>     return rsw;
>   }
>   public static void test() throws Exception {
>     Test t = new Test();
>     ResultSetWrapper rsw = t.wrapGeneratedKeysQuery();
>     rsw.next();
>     rsw.getInt(1);
>     rsw.close(); // This closes the underlying JDBC connection
>     ResultSetWrapper rsw2 = t.wrapPlainQuery(); // throws an exception: DBCP connection pool is exhausted
>     rsw2.next();
>     rsw2.getInt(1);
>     rsw2.close();
>   }
> }
> The result: the DBCP connection is never returned to the pool. We run out of connections. wrapPlainQuery's getConnection fails because the pool (of 1 connection) has been exhausted. The statement rsw.close() closed the underlying JDBC connection and didn't return the DBCP connection to the pool.
> The workaround is to always carry around within the ResultSetWrapper the "original" Connection upon which the query was executed. But this should not be necessary.
> Sorry, but I can't tell what version of DBCP I'm running. It's the one included in the Tomcat 5.5 bundle I'm running.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.