I’m using the dhtmlxTree component with the Java dhtmlxConnector to connect to an Oracle database.
This configuration was working well until I started to load some fairly complex tree structures (nesting about 7 levels deep with large fan-out). In those cases the connector object started to report the following error:
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
at com.dhtmlx.connector.DBDataWrapper.query(DBDataWrapper.java:370)
at com.dhtmlx.connector.DBDataWrapper.select(DBDataWrapper.java:214)
…
I’ve seen these sorts of errors before and experience has taught me when dealing with Java and Oracle it’s a very good idea to explicitly close all database connections, statements, and result sets or you’re bound to get a visit from the cursors exceeded fairy.
In this particular case it looks like the offending line of code is in the public ConnectorResultSet query(String data) method of the DBDataWrapper.java class.
[code] Statement s = getStatement();
s.execute(data);
ResultSet r = s.getResultSet();
if (r!=null)
if (!r.first()){
r.close();
r=null;
}
return new ConnectorResultSet(r);[/code]
Initially I suspected the ReseltSet was being left open, however it is eventually closed by the public HashMap<String,String> get_next() method in the ConnectorResultSet.java class.
The statement however is opened and not closed. As the tree connector recursively calls the String render_set(ConnectorResultSet result) method in the TreeConnector.java class the open statements build up and eventually the cursors can be exceeded if the tree is large and complex enough.
I’ve managed to come up with a stop-gap solution that seems to avoid the problem by copying the result set into a ArrayList <HashMap<String,String>> and closing the result set and statement immediately. This of course involved plumbing the ArrayList <HashMap<String,String>> back instead of a ConnectorResultSet.
I thought I’d post this in case someone else runs into the issue. It would be helpful if future versions of the dhtmlxConnector avoided accumulating open cursors while building a tree.
Thanks
Bill