aspose file tools*
The moose likes JDBC and the fly likes Prepared Statement not closing cursor Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Prepared Statement not closing cursor" Watch "Prepared Statement not closing cursor" New topic
Author

Prepared Statement not closing cursor

Mars Mondal
Greenhorn

Joined: Jan 01, 2011
Posts: 14
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

Joined: Feb 22, 2011
Posts: 75
Thats because, you are not closing your PreparedStatement and thus leaving the implicit cursor open in the DB.
Philip Grove
Ranch Hand

Joined: Aug 18, 2009
Posts: 68

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

Joined: Jan 01, 2011
Posts: 14
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

Joined: Jan 01, 2011
Posts: 14
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
Bartender

Joined: Oct 14, 2005
Posts: 18167
    
    8

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Prepared Statement not closing cursor
 
Similar Threads
IsEntityRemovalAllowed
Comparing large BIGINT fields in MySQL on Windows
rs.next() always return true even no result is querried
SELECT * FROM TESTTABLE WHERE ID IN (?)
passing variable into sql query