... that must have been the last time I used a stored procedure because I realized earlier today that the code for executing stored procedures via the SQL Abstraction layer was riddled with typos and other syntax errors, even though I remember having no problems with it the last time I used it. So apparently I managed to make a bunch of changes to it in the interim without testing them. (Actually I thought it was kinda funny and ironic that the first error in the sqlagent.cfc was on line 1492.)

Truth is I just generally don't find stored procedures very helpful. I suppose if I had some specific need to make a single trip to the database to get several bits of data at one time for say a console page or a stats page that featured several independent reports, then I might use it. Or if I had an application that I knew needed to connect to a database that would be accessed outside of ColdFusion and I wanted to share some of the business logic with the other application(s) sharing the database, then sure. But on a daily basis I generally find select statements more flexible and less work.

So tonight I went through and updated the storedprocedure.cfc so it works again. I was surprised how much work that involved... Though the better part of the few hours I spent actually was in a hair pulling session over this (pseudocode):

<cffunction name="executeStoredProcedure" access="public" output="false" returntype="struct">
<cfset var param = getParameters() />
<cfset var cfstoredproc = 0 />
<cfstoredproc name="#theproc#" datasource="#thedsn#">
<cfloop index="x" from="1" to="#arraylen(param)#">
<cfprocparam value="#param[x].content#" ... />
<cfreturn cfstoredproc.resultCode />

What happens when you attempt to execute this function is that the ColdFusion Server tells you that you're trying to dereference a variable of type java.lang.String as a structure with keys. What's even more frustrating is that it tells you that's happening not on the stored procedure line, but on the cfloop line where it's using the array. The reason why it took me a few hours to figure it out is because the error itself actually has absolutely nothing to do with the parameter array, the loop, the procparam tags or the attributes of the cfstoredproc tag. The error occurs because the cfstoredproc variable which I smartly var'ed as all variables should be (whether they're query names or cffile variables), was set originally to "0" instead of to structNew().

Normally as far as I know, ColdFusion gets rid of whatever variable was there ahead of time, so for example, if you declare cffile to return its structure to "myfile" it will overwrite the myfile variable if there was one. But apparently with cfstoredproc there's a bug that causes it to attempt to use the existing variable as a structure.

So with that mystery solved, assuming you use lots of stored procedures, enjoy not having to declare your procparam tags again. :) Here's the generic pseudocode for executing a procedure:

<!--- get the primary datasource --->
<cfset ds = request.tapi.getObject("datasource").init()>
<!--- get a procedure --->
<cfset proc = ds.getProcedure().init("myproc","result1,result2",attributes)>
<!--- get the results and use them --->
<cfdump var="#proc.execute()#">

The attributes structure is a combination of form and url scopes just like the old Fusebox technique, and the procedure object maps those to the parameters of the procedure, so whatever you put into the browser's address bar will become a parameter to the stored procedure. This is the named proc parameters that Macromedia / Adobe haven't been able to figure out like I mentioned in the Iron Man blog. Output parameters if you use them will then be reset in the procedure object's parameter values.

Related Blog Entries

BlogCFC was created by Raymond Camden. This blog is running version 5.5.006. | Protected by Akismet | Blog with WordPress