Cfstoredproc Abstraction Function

Since ColdFusion 8 doesn’t have a cfscript equivalent for cfstoredproc calls, I decided to create a abstraction cffunction called getStoredProc. It takes in a number of parameters and creates a cfstoredproc call with them. You can then call this function from within cfscript. No more opening and closing the cfscript tag. Additonally, this function uses arrays to store all necessary params for the cfstoredproc call. The ability to simply just create arrays for your cfstoredproc params certainly saves some time. No more line after line of:

<cfprocparam type="in" cfsqltype="cf_sql_varchar" variable="firstName" value="#firstName#">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" variable="lastName" value="#lastName#">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" variable="middleName" value="#middleName#">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" variable="addr1" value="#addr1#">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" variable="addr2" value="#addr2#">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" variable="city" value="#city#">

……And so on….
The Function- getStoredProc:

This is the function which accepts a number of parameters and makes a call to cfstoredproc.

<cffunction name="getStoredProc" access="public" returntype="any" hint="calls stored proc">
 <cfargument name="procName" type="string" required="yes">
 <cfargument name="resultName" type="array" required="yes">
 <cfargument name="varParams" type="struct" required="yes">
 <cfargument name="varParamsOneDir" type="string" required="yes" hint="'In' or 'Out' value sets all params to that direction">
 <cfargument name="varSameAsDbVar" type="boolean" required="no" default="true" hint="if true, sets variable to same value as dbVarName">
  var statusData = structNew();
  statusData['resultSetCnt'] = 0;
   if(structKeyExists(arguments, "resultName")){ //On my site, I am forcing the usage of result sets. So, this must always have at least 1 value. This method can be modified to remove that requirement if you wish.
    statusData['resultSetCnt'] = arrayLen(arguments.resultName);
   statusData['varParamsCnt'] = arrayLen(arguments.varParams.dbVarName);
    arraySet(arguments.varParams.varDirection, 1, statusData.varParamsCnt, arguments.varParamsOneDir);
 <cfstoredproc procedure="#arguments.procName#" datasource="#request.dataSource#" debug="yes" returncode="yes">
  <cfloop from="1" to="#statusData.resultSetCnt#" index="i">
   <cfprocresult name="#arguments.resultName[i]#" resultset="#i#"/>
  <cfloop index="i" from="1" to="#statusData.varParamsCnt#">
   <cfif arguments.varSameAsDbVar>
    <cfset arguments.varParams.varName[i] = arguments.varParams.dbVarName[i]>
   <cfprocparam type="#arguments.varParams.varDirection[i]#" cfsqltype="#arguments.varParams.dataType[i]#" variable="#arguments.varParams.varName[i]#" dbvarname="@#arguments.varParams.dbVarName[i]#" value="#arguments.varParams.varValue[i]#" null="no"/>
 <cfif statusData.resultSetCnt gt 0>
  <cfloop from="1" to="#statusData.resultSetCnt#" index="i">
   <cfif structKeyExists(variables, arguments.resultName[i])> <!--- Necessary check to have if some stored procs don't return any output, as those wont exist as objects. --->
    <cfset statusData[arguments.resultName[i]] = evaluate(arguments.resultName[i])>
 <cfreturn statusData>

Function Arguments:

The function accepts the following as parameters:

procNameName of the stored procedure to run. required.

resultNameAn array of result set names. required.

varParamsA struct containing cfprocparam values. Each item in this struct is an array, which enables you to specify multiple cfprocparam sets. Those arrays are: varName, varDirection, dataType, dbVarName, varValue. required.

varParamsOneDirIf all cfprocparams are one direction (IE: all are inputs), then you can use just set this param to the appropriate dirrection. Doing so saves code in your calling function. Rather than the below:
orgData[‘varParams’][‘varDirection’] = arrayNew(1);
orgData[‘varParams’][‘varDirection’] = “In”;
orgData[‘varParams’][‘varDirection’] = “In”;
orgData[‘varParams’][‘varDirection’] = “In”;
orgData[‘varParams’][‘varDirection’] = “In”;
You can simply create the varDirection array, and in your call to getStoredProc, pass in varParamsOneDir with a value of “In”. This will cause getStoredProc to do an arraySet on varDirection, setting all array elements to “In”. optional.

varSameAsDbVarThis is another time saver. If true, the variable array elements are set to the same values as the dbVarName elements. This is useful if your stored proc vars are named the same as your ColdFusion vars (in my opinion, for best practice, they should be). optional.

Calling getStoredProc:

<cffunction name="setRegister" access="public" output="false" returntype="struct" hint="set registration">
  var regData = structNew();
  // do some misc preliminary stuff such as form field processing, validation, etc. (not included)
  // set up the cfstoredproc call.
  regData['resultName'] = arrayNew(1);
  regData['varParams']['varName'] = arrayNew(1);
  regData['varParams']['varDirection'] = arrayNew(1);
  regData['varParams']['dbVarName'] = arrayNew(1);
  regData['varParams']['varValue'] = arrayNew(1);
  regData['varParams']['dataType'][1] = "int";
  regData['varParams']['dataType'][2] = "varchar(50)";
  regData['varParams']['dataType'][3] = "varchar(200)";
  regData['varParams']['dataType'][4] = "varchar(200)";
  regData['varParams']['dataType'][5] = "varchar(600)";
  regData['varParams']['dataType'][6] = "int";
  regData['varParams']['dataType'][7] = "varchar(MAX)";
  regData['varParams']['dataType'][8] = "varchar(MAX)";
  regData['varParams']['dbVarName'][1] = "u_ID";
  regData['varParams']['dbVarName'][2] = "u_password";
  regData['varParams']['dbVarName'][3] = "u_firstName";
  regData['varParams']['dbVarName'][4] = "u_lastName";
  regData['varParams']['dbVarName'][5] = "u_email";
  regData['varParams']['dbVarName'][6] = "rt_ID";
  regData['varParams']['dbVarName'][7] = "u_url";
  regData['varParams']['dbVarName'][8] = "u_biography";
  regData['varParams']['varValue'][1] = regData.u_ID;
  regData['varParams']['varValue'][2] = regData.u_password;
  regData['varParams']['varValue'][3] = regData.u_firstName;
  regData['varParams']['varValue'][4] = regData.u_lastName;
  regData['varParams']['varValue'][5] = regData.u_email;
  regData['varParams']['varValue'][6] = regData.rt_ID;
  regData['varParams']['varValue'][7] = regData.u_url;
  regData['varParams']['varValue'][8] = regData.u_biography;
  regData['resultName'][1] = "userResult";
  structAppend(regData, request.utils.getStoredProc('sp_setUserAccount', regData.resultName, regData.varParams, "In", true));
  //do some post call stuff (not included)...
 <cfreturn regData />

First, I create a struct containing values for all the attributes cfstoredproc expects. From there, it’s just a simple call. As a side note, if you are wondering about the reference to request.utils on the getStoredProc call, I put all createObjects in the request scope, in order to avoid doing multiple createObject calls to the same objects throughout the request. That’s it. Any input or suggestions, feel free!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s