aspose file tools*
The moose likes JDBC 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
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: 36514
    
  16
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: CallableStatement in loop
 
Similar Threads
StaleConnectionException
Difference between connection.commit and usertransaction.commit
Closing Statement object prior to committing
does Connection commit on close()?
Does a Connection commit on close?