benefit of PreparedStatement if closed due to error: maximum open cursors exceeded

[Allen Bandela]

Hi ranchers,

I get the error "maximum open cursors exceeded", after my program runs for some time. I understand that this is occuring because either the open cursors in the db is too less or I am supposed to be closing my prepared statement obects after each use.

Let me explain my requirement:

This code executes the same sql query thousands of times. So, I started using a Prepared Statement , to pre-compile the sql once, and dynamically run it with input values.

In this situation, if I close the PreparedStatement after each ps.executeQuery() stmt, would'nt the query be compiled again? when I use it the next time? Is really, closing the PreparedStatement the right thing to do? In that case, how is PreparedStatement better than an ordinary Statement.

If I'm not clear, please let me know.

Thanks

[Mathias Nilsson]

Is it in a loop or do you just don't close the Statement? Maybe this article will give you some information?

Why preparestatement

[Allen Bandela]

Its in a loop.

Closing the PreparedStatement within the loop, is not the right way to go. That is my understanding. When doing a google search, many suggest to close the ps. Yes, that would prevent the exception, but not solve the purpose. Unless, the same preparedstatement is used to execute the Query consecutively, its equivalent to using a Statement object.

Please somebody tell where I'm wrong.

Thanks

[Dave Michels]

The key in what you're doing is that you need to be sure that you close the ResultSet that is returned from each query after you execute it. You're correct in that you don't need to close the PreparedStatement after it's initially compiled (prepared). You are also correct in that you should only need to set the bind variables on subsequent loops and re-execute the statement...At least this is the *typical* behavior for most JDBC drivers (you didn't specify which you were using).

Each result set corresponds to an open cursor in the database, so when you loop through and execute your PreparedStatement repetitively w/o closing the ResultSet you leave an open cursor for each execution.

If you call the java.sql.ResultSet.close() method within your loop after you're finished retrieving the values, prior to re-execution, you should be able alliviate the open cursor issue that you're incurring.

[Allen Bandela]

Nice reply, thanks. I thought this was the cause of the problem, i.e not closing the result set. But, even after doing so, I am getting the same exception. I wonder why.

[Allen Bandela]

Also, the documentation for ResultSet says that it is automatically closed when the PS from which it was created is re-executed. So, this is definetly and obviously not the cause for the problem.

[Mike Rainville]

In my program, I solved the ORA-01000 "Too many cursors open" problem by remembering the prepared statements and result sets, and closing them after I was sure they were no longer needed. This was not a new problem. It happened using ojdbc14.jar with Java 5 and 6. I am now using JDK 6 update 6 (latest) with ojdbc6,jar on Oracle 10g R3.

Even though PreparedStatement may close when re-executed, I still had the problem, with at least
16 open cursors, and if there were uncommitted updates, there could have been be many more, and with other background activity on the database... (ours has max cursors set to 300)

I had many result sets that I could not close before they went out of scope. They were wrapped in table models and were frequently being replaced with a new result set. I had queries and updates, both using PreparedStatement's.

The solution was to save a reference to each statement or result set as it was created, in a Queue of mathcing type(ResultSet or PreparedStatement), and to iterate through the saved references, closing each one in turn. That appears to have eliminated the problem, and also improved performance.

For the update statements, I process the PreparedStatements queue, closing each one in turn immediately after commit. For the queries it's done just before I replace all 16 result sets. I used Doug Lea's thread safe Queue in my Swing application.
[ April 27, 2008: Message edited by: Mike Rainville ]

[Jeanne Boyarsky]

Originally posted by Sylvester James:
Unless, the same preparedstatement is used to execute the Query consecutively, its equivalent to using a Statement object.

I disagree. When you "re-prepare" a new PreparedStatement object with the same SQL string, the connection object will use that one as long as it is still in the cache.

That said, you shouldn't have to close the PreparedStatement each time.