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
SCJP,SCWCD,SCBCD<br />If Opportunity doesn't knock then build the door
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.
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.
Joined: Jun 09, 2000
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 ]
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.
Joined: Jun 09, 2000
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