Win a copy of 97 Things Every Java Programmer Should Know this week in the Java in General forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Jeanne Boyarsky
  • Junilu Lacar
  • Henry Wong
Sheriffs:
  • Ron McLeod
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Frits Walraven
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • salvin francis
  • fred rosenberger

CallableStatement in loop

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 93
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Use executeBatch method.
 
Jason Bauer
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Will "executeBatch" work even if there are 120,000 rows ?
 
Bhagat Singh Rawat
Ranch Hand
Posts: 93
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Marshal
Posts: 69472
277
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This thread would sit better in the JDBC forum. Moving.
 
Bhagat Singh Rawat
Ranch Hand
Posts: 93
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.
 
Hug your destiny! And hug this tiny ad:
Devious Experiments for a Truly Passive Greenhouse!
https://www.kickstarter.com/projects/paulwheaton/greenhouse-1
    Bookmark Topic Watch Topic
  • New Topic