• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

maximum open cursors exceeded

 
Ranch Hand
Posts: 413
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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

 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 413
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 413
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 413
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
reply
    Bookmark Topic Watch Topic
  • New Topic