RE: [dbcp]: Oracle "too many open cursors error" fix?

[JD Evora <jd...@saadian.com>]

> >
> > I've been using the dbcp BasicDataSourceFactory with Tomcat,
> > as a replacement for Tyrex brokenness.  I ran into some
> > trouble with it under heavy load: after running it for a
> > while I end up with an Oracle "too many open cursors error".
>
> This is a common problem with any connection pooling code.
>
> The problem has to do with ResultSets that are not closed.
>
> Conn.close() is used to return a connection to the pool.
> Perhaps the correct approach is to cache references to all
> ResultSets and such so that we can auto close them to give
> the same affect.
>
> This is something I've been meaning to add to Avalon's
> DataSource code, and I just never have gotten around to it.
>


 Seems be easy to fix. Or am I too optimistic??

 In PoolableConnection,  overload createStatement() to add the statement to
an ArrayList and in
 PoolableConnection.close() go through the ArrayList and close all
statements before return the connection to the pool.

 Something like:

  //New protected ArrayList with all the statements
  protected ArrayList _statements= null;


  // change the constructor
  public PoolableConnection(Connection conn, ObjectPool pool) {
        super(conn);
        _pool = pool;
        _statements = new ArrayList();
  }

  // Override createStatement()
  public Statement createStatement() throws SQLException {
    Statement statement = super.createStatement();
    _statements.add(statement);
    return statement;
  }

  // Add some code to close()
  public void close() throws SQLException {
   Iterator it =_statements.iterator();
   while (it.hasNext()){
     ((Statement) it.next()).close();
   }
   _statements.clear();

   //Actual close() code here.
  }



  Do you think that this should do the trick?

  Best regards
    JD Evora




--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>