Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Stored Procedure Question

 
Jennifer Sohl
Ranch Hand
Posts: 455
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a bill of material maintenance program that I have implemented an error check into. Currently how it works, is it reads each part number that is listed on the bill and runs a stored procedure to get information about that part number to make sure there are no errors on it. The problem... some bills of material have 300+ part numbers on them. So that means I am calling my stored procedure 300+ times!
What I need to know, is how can I get information for all of the part numbers on the bill by running only one stored procedure?

I thought about creating an ArrayList of all the part numbers on the bill... but then I'm not really sure what to do with that. Is there any way I can pass that through a stored procedure?

Thanks for any help!
[ February 23, 2007: Message edited by: Jennifer Sohl ]
 
Niranjan Sarkar
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
One cheeky way of doing is by appending all the item codes into a String (separated by "~") and then pass it on as a VARCHAR2 in the Stored Procedure. Of course the stored Procedure needs to be changed for this.
 
Sol Mayer-Orn
Ranch Hand
Posts: 311
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
For simple cases, i'd go with the above reply - just good old strings (sometimes I would make it a comma-separated list, just ready for the sql 'IN' clause). Probably not 'cutting edge' in terms of design phylosophy, but quick and fun to implement.

More generally, you might use arrays, if your database *and* jdbc driver support it.
For example, if you use oracle (there must be nicer examples on the web, but this one should provide enough key-words for later google searches):
http://forums.oracle.com/forums/thread.jspa?threadID=379315

However, note: on some cases, the use of Arrays required us to take more care. When using just plain strings, the system seemed more tolerant towards old drivers, missing database patches, crazy security add-ons , conflicts with tomcat connection pools, etc.

So if you decide to use Arrays, i'd recommend you make sure they work on your production environment (taking into account DB version, drivers, connection pool utilities, security software, etc).
[ February 27, 2007: Message edited by: Sol Mam-Orn ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic