Controlling database connections

Hello All,

I have looked around for an answer to this (which i believe it is no) but I thought I would shoot it out to the group and see if anybody had an idea.

When having to call a stored procedure in a loop, is there anyway I can make the database connections being handled outside the loop so it does not create a connection everytime I call the proc? I am basically looking for something like:

Open connection
<cfloop>
Call Proc
</cfloop>
close connection

When I am in this situation now, I have a to convert the proc to a cfquery, which get stored up in a var until about 100 are created. These 100 statements are then executed, and then it starts all over again. This operates a lot faster than the proc in the loop, but is much more painful to maintain.

Any Ideas?

thanks

Can you pass all the information needed in one <cfstoredproc> call? That is, get rid of the <cfloop>, and instead let the proc handle the flow control?

Alternatively, within the <cfstoredproc> (but right at the end) loop through your list of values (<cfloop list="#yourList#" index=i><cfprocparam ... value="#i#"></cfloop>). On the proc side, you'd have to allow for the maximum number of values that could potentially be passed to the proc (ie, for each possible variable in the proc, set a default value: @value1 INT = NULL,). After all possible variable default values are set, test the value of each. If it's not null, do something. If it's null, do nothing. A lot of copying and pasting....

I defer to r937 and kiteless on the feasibility of these approaches....

i have found that passing large strings of information (such as a list of keys) that need to be hashed out by the stored proc, does not work well it Oracle. As an example, in the past, I have tried to pass a string of keys into a proc and have it handle the looping. It would only work once I converted my numbers to strings (even with all vars and columns being IDed as numbers). This was very sloppy and would not allow me to handle certain things I needed to.

I also can't get put the loop on the procparam level due to the amount of processing (i am at times creating hundreds of unique update statements). I would truely hate to see the proc with a couple hundred variable statements!

Overall, I am not worried if the processing is done in CF or Oracle, more i was just trying to make life simple by removing the need for all the extra code. If i could control a database connection outside of the loop, i could open it once and then call my stored proc to my heart's content.

Use the loop to recreate the only part that changes in the procedure!

You need to use an index or query loop depending on what you are doing. And you should try doing this before you connect. Because making a loop just creates an array, that of which can be defined by your self.

<cfset array[1] = "value">
<cfset array[2] = "value">
<cfset array[3] = "value">

Your array then looks like a list value, value, value and then its moved to your procedure one piece at a time.


I dont know if i hit the spot, or even if i understood you, but let me know if it works. And if it doesnt even get close im sorry, but I couldnt understand you

CF also maintains database connections, this is an option in the configuration for the datasource.