• 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

CallableStatement in loop

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Use executeBatch method.
 
Jason Bauer
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 79177
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
You get good luck from rubbing the belly of a tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic