Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

maximum open cursors exceeded

 
Gaurav Chikara
Ranch Hand
Posts: 412
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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

 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34974
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Gaurav Chikara
Ranch Hand
Posts: 412
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 21416
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 412
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 21416
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 412
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic