PreparedStatement/Connection/ResultSet problem

[dendenners]

Hey there,
I am running into a problem with a program I wrote that runs a select statement on an oracle database. The program reads in a file and runs a PreparedStatement on the database for each line using a substring on that line of the file as the bind variable.
The problem I'm having is that I'm running out of cursors on the database. In this particular case, there are 30000 lines to be read in (i.e. the preparedStatement must be run 30000 times) but the query only runs x times before falling over, where x is the number of cursors allowed in the database.
I'm closing the resultset each time I'm finished with it (and doing a resultSet = null as well into the bargain) I'm using the same Connection object for each preparedStatement.
I've tried using the same PreparedStatement for each query, and creating a new one each time. Does anyone have any idea why the cursors aren't being given back to oracle once the query has been executed? Thanks a lot

 

[wushutwist]

Are you also closing the statement? Are you closing both the statement and resultset in a finally block? This is important because it will ensure that they are closed regardless of error conditions.

Example:

PreparedStatement stmt = null;
ResultSet rs = null;
try {
// Do your stuff
}
finally {
  if (rs != null) { rs.close(); }
  if (stmt != null) { stmt.close(); }
}

 

[meadandale]

Sounds like you need to use a batch insert instead of individual inserts. This means that you only need to execute the statement once instead of 30k times. Look into the Statement.addBatch() method for JDBC 2.0.

 

[wushutwist]

The problem with batch job in JDBC is that alot of JDBC Drivers still do not fully support them. Make sure you check out your driver documentation first. Even so, your previous attempt should work if you are opening and closing your statements correctly. Most of the time when I see cursors not being closed it is because someone only closed the ResultSet, thinking that was enough.

 

[dendenners]

Yep, that was the problem. I wasn't closing the statement. Thanks