This week's book giveaway is in the OO, Patterns, UML and Refactoring forum.
We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes CallableStatement in loop Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "CallableStatement in loop" Watch "CallableStatement in loop" New topic
Author

CallableStatement in loop

Jason Bauer
Greenhorn

Joined: May 28, 2009
Posts: 3
Hi,
I'm wondering what the best practices are for using a CallableStatement and the prepareCall function in a loop that is very large. The way it was currently coded it ends with the amount of cursors being exceeded. Thus I moved the creation of the CallableStatement outside of the loop and this seems to work but I'm wondering that the best practices are ? Here is an example of the code , please let me know what I'm doing wrong ? Should I be closing "myCallable" after each executeUpdate and if so will that affect the rollback or commit ?

try{
CallableStatement myCallable;
myCallable = conn.prepareCall("{ ? = call MY_FUNCTION_CALL(?,?,?,?,?,?) }");

while loop{

myCallable.setString(2, 'some value');
myCallable.executeUpdate();
}
}
catch(Exception e){
connection rollback;
}
finally{
connection commit;
}


Thanks
Jason
Bhagat Singh Rawat
Ranch Hand

Joined: Apr 04, 2009
Posts: 93
Use executeBatch method.
Jason Bauer
Greenhorn

Joined: May 28, 2009
Posts: 3
Will "executeBatch" work even if there are 120,000 rows ?
Bhagat Singh Rawat
Ranch Hand

Joined: Apr 04, 2009
Posts: 93
Jason Bauer wrote:Will "executeBatch" work even if there are 120,000 rows ?


I believe it should work Please check with "how executeBatch method works in JDBC"
Jason Bauer
Greenhorn

Joined: May 28, 2009
Posts: 3
I tried it but I can't use it because we are using OUT variables in our Oracle function. Oh well , my code works as is today with the CallableStatement outside the loop. Unless anyone can tell me a reason to not do it the way it's currently coded I'll proceed.
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 43970
    
  33
This thread would sit better in the JDBC forum. Moving.
Bhagat Singh Rawat
Ranch Hand

Joined: Apr 04, 2009
Posts: 93
Jason Bauer wrote:Unless anyone can tell me a reason to not do it the way it's currently coded I'll proceed.


Performance issue I guess.
 
Have you checked out Aspose?
 
subject: CallableStatement in loop
 
It's not a secret anymore!