Using DBTools.J with the Oracle Driver: Objects Are Not Getting Released Correctly

Problem

When I create more than 50 Selector objects (or other DML object) using DBTools.J and the Oracle driver, I get the following error:

   ORA-01000: maximum open cursors exceeded

Cause

This is a known problem in Oracle's JDBC driver that gets propagated through DBTools.J. The following Java program will eventually throw an exception when executed, dependent on how many open cursors are allowed in the Oracle database:

    for (int i = 1; i<300; i++) {
        Connection cn2 = pool.getConnection();
        DBTable test = new DBTable("test3");
        Selector sel = new Selector();
        sel.addToSelectList(test.getColumn("f1"));
        sel.addToSelectList(test.getColumn("f2"));
        Reader rdr = sel.getReader ( cn2 ); 
        cn2.returnToPool(); 
    }

Action

The workaround to the Oracle driver is simple: each Statement is closed when it is no longer needed. This is actually recommended by Sun in their Java Series reference:

"It is recommended that Statement objects be closed explicitly when they are no longer needed, thereby freeing DBMS resources as soon as possible."

To implement this advice through DBTools.J, add the following line into the for loop:

    for (int i = 1; i<300; i++) {
        Connection cn2 = pool.getConnection();
        DBTable test = new DBTable("test3");
        Selector sel = new Selector();
        sel.addToSelectList(test.getColumn("f1"));
        sel.addToSelectList(test.getColumn("f2"));
        Reader rdr = sel.getReader ( cn2 );
        sel.close();         cn2.returnToPool();      }