Do you reuse JDBC connections? Then be aware.

Sometimes we want to reuse a JDBC connection because the time taken to create a new one can be too big. This is the case we use inside NextReports Designer . Someone connects to a data source and then he can:

• see tables, views, procedures
• see columns information 
• make some validations
• create and run queries, reports and charts

Because the same connection is reused time after time, it is not closed unless we disconnect from the data source.

If we make many operations on that data source, after some time we can see an error raised like "too many open cursors" . Any database has a property OPEN_CURSORS which specifies the maximum number of open cursors. If this is reached, the error is raised.

In NextReports Designer, this was seen when a chart per record was used inside a report in detail band and after some hundreds of records the charts where not generated anymore because the exception was raised.

When a connection is closed, all the cursors are also closed. But in our case we reuse the connection and no close is done. So how to close the open cursors in JDBC?

The answer resides in calling commit() on our connection. We must take care if autocommit is true. If it is true, calling commit() will raise an exception  so this commit() must be called only in cases when autocommit is false.