This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes benefit of PreparedStatement if closed due to error: maximum open cursors exceeded Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "benefit of PreparedStatement if closed due to error: maximum open cursors exceeded" Watch "benefit of PreparedStatement if closed due to error: maximum open cursors exceeded" New topic
Author

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

Allen Bandela
Ranch Hand

Joined: Feb 16, 2006
Posts: 128

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
Ranch Hand

Joined: Aug 21, 2004
Posts: 367
Is it in a loop or do you just don't close the Statement? Maybe this article will give you some information?

Why preparestatement


SCJP1.4
Allen Bandela
Ranch Hand

Joined: Feb 16, 2006
Posts: 128

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
Greenhorn

Joined: Mar 29, 2007
Posts: 5
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
Ranch Hand

Joined: Feb 16, 2006
Posts: 128

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
Ranch Hand

Joined: Feb 16, 2006
Posts: 128

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
Ranch Hand

Joined: May 29, 2004
Posts: 36
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30130
    
150

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: benefit of PreparedStatement if closed due to error: maximum open cursors exceeded
 
Similar Threads
jdbc
ORA-01000: maximum open cursors exceeded
ORA-01000 maximum open cursors exceeded
Problem updating batch records in Oracle through Java - URGENT
Maximum open cursors exceeded