Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Prepared Statement not closing cursor

 
Mars Mondal
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I have a method which returns a resultset. The problem is that the cursor remains open and it exceeds the total number of cursors allowed and errors out. What is wrong with the following code?


Note that the extraSQL is for adding some extra sql logic that specific calls may need.

Thanks a lot in advance for your help
 
Sudheer Bhat
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thats because, you are not closing your PreparedStatement and thus leaving the implicit cursor open in the DB.
 
Philip Grove
Ranch Hand
Posts: 68
Firefox Browser Java Netbeans IDE
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The problem will not be fixed by closing the PreparedStatement. The method returns a ResultSet and closing the Statement will also close the ResultSet. Likewise a close on a Connection will trigger a close on all Statements. So not closing objects in JDBC is dangerous, but closing them can be messy as well.

Unless you really need it to be a ResultSet you return, you are probably better off creating another object with the data and closing the Statement before the return. Returning JDBC objects can be really dangerous because once you reach that return statement you loose the reference to the Statement, but it still exists.
 
Mars Mondal
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Philip Grove wrote:The problem will not be fixed by closing the PreparedStatement. The method returns a ResultSet and closing the Statement will also close the ResultSet. Likewise a close on a Connection will trigger a close on all Statements. So not closing objects in JDBC is dangerous, but closing them can be messy as well.

Unless you really need it to be a ResultSet you return, you are probably better off creating another object with the data and closing the Statement before the return. Returning JDBC objects can be really dangerous because once you reach that return statement you loose the reference to the Statement, but it still exists.


Philip you are right this code was return by someone else and this was the way it was set up unfortunately. And yes it was being closed in the method where the result set was being returned. I think because of throwing JDBC objects we are getting closed statements error.
I think that changing the return type would solve the problem but it involves changing a lot of code written by someone else and I am working on that.
 
Mars Mondal
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We tried closing the ResultSet and PreparedStatement and passed a null but yet we got the Closed statement error.

The way I fixed it was using a Statement instead of a PreparedStatement. I am curious though

Why did this not lead to open cursor issues when we changed it to a Statement ? And why was the cursor still remaining open when we passed null and had closed the PreparedStatement in the method?
 
Paul Clapham
Sheriff
Posts: 21107
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In my opinion you should consider fixing that by creating the PreparedStatement only once, instead of re-creating it many times. That way you'll only have one open cursor at the database end.

Note that PreparedStatement is designed to be optimized by the database, so reusing it amortizes the optimization costs across the number of times you reuse it.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic