my dog learned polymorphism*
The moose likes JDBC and the fly likes maximum open cursors exceeded Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "maximum open cursors exceeded" Watch "maximum open cursors exceeded" New topic
Author

maximum open cursors exceeded

Gaurav Chikara
Ranch Hand

Joined: Jun 09, 2000
Posts: 410
Dear All

I am using preparedstatement to add batch of inserts and use the following snippet of code to do my stuff
I am not using resultSet at all and still am getting the above error
Can any one of the omniscient guys can give me some hint as where I am going wrong
Caller Method




Calling method



SCJP,SCWCD,SCBCD<br />If Opportunity doesn't knock then build the door
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30123
    
150

Gaurav,
Which line throws that exception?

The biggest issue I see is that I can't find where the prepared statement or connection is being closed. This is a resource leak even if it isn't a result set.


[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
Gaurav Chikara
Ranch Hand

Joined: Jun 09, 2000
Posts: 410
Jeanne
I am calling the insert method inside a for loop.If you notice amongst input arguments of insert method are index and recordSize where index represents the iterationCount and recordSize is a limiting factor of for loop.
Besides this, I am passing preparedstatement also from that calling method.Therefore I am closing the preparedstatement once the for loop iterations are over.
Stacktrace is probably on line
pstmt.executeUpdate();

Another thing that is confusing me is that when I put a debug on PreparedStatement and see batch property it is always 1. And though I am using pstmt.addBatch() in my addBatchLogic method still preparedstatement is not storing any batch and inserts only 1000th record .And after few iterations I get the above error

I tried doing same with Statement object and it worked but performance was very slow and thus it defeated the purpose of Batch inserts.I am in kind of limbo currently not sure why preparedstatement is crying for batch operations and statement isn't.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

Originally posted by Gaurav Chikara:
I am calling the insert method inside a for loop.
So you create a new PreparedStatement for each iteration of the loop. And as Jeanne said, you don't ever close them. Seems to me that is why you run out of resources.
Gaurav Chikara
Ranch Hand

Joined: Jun 09, 2000
Posts: 410
Paul
Thanks for the reply
Inside the for loop I am checking that if it is first iteration then create PreparedStatement so for subsequent iterations same handle of preparedstatement is used. And after for loop is over I close the PreparedStatment in finally block
But problem is that I get this error inside for loop itself.
If posting the code will help please let me know
[ March 21, 2007: Message edited by: Gaurav Chikara ]
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

Originally posted by Gaurav Chikara:
And after for loop is over I close the PreparedStatment in finally block
So, after the for-loop is finished, you close the last PreparedStatement you created. You don't close the other ones.
Gaurav Chikara
Ranch Hand

Joined: Jun 09, 2000
Posts: 410
It seems I was using wrong method call of prepared statement as I was using execute and was supposed to use executeBatch.
Paul I was only using one preparedststment which I initialised only once and I was using same handle to addBatch and executeBatch
After loop was over I was closing same handle

This time It went well but performance is still slow as it only inserts 1000 records per minute .I was getting same performance with Statement object also.Do you have any idea what could be reason for it
Here is my code
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: maximum open cursors exceeded
 
Similar Threads
Conversion Blob to Byte Array
Conversion Blob to Byte Array
Transaction not Rolling Back
SQLException in iteration and loop doesn't continue
Error connect to db after several transaction