SQL stored procedures are a convenient way of executing multiple or complex statements simultaneously. Procedures are useful for encapsulating logic when you need to select data from several tables at the same time without joining them and you want to improve performance, or for encapsulating logic within the database when you have several separate applications accessing the same database and you don't want to have to rewrite the logic in different programming languages and then separately maintain them if later changes become necessary.
For example if you had an application which you knew would be accessed both by your ColdFusion application and by an AS400 or iSeries application elsewhere, you can encapsulate some of your logic at the database so that both your ColdFusion application and the iSeries can use the same stored procedure and then if later changes are necessary they can be made only in the one stored procedure.
Although you can certainly just use the properties of the datasource object to execute your stored procedures, DataFaucet provides a stored procedure object to make this easier.
Example: (manual execution)
<cfset ds = request.DataFaucet.getDatasource() /> <cfstoredproc procedure="usp_ComplexProductSearch" datasource="#ds.getValue('datasource')#" username="#ds.getValue('usr')#" password="#ds.getValue('pwd')#"> <cfprocparam value="#form.categoryid#" cfsqltype="cf_sql_varchar" type="inout" variable="form.categoryid" /> <cfprocparam value="#form.searchphrase#" cfsqltype="cf_sql_varchar" /> <cfprocparam value="#form.pricemax#" cfsqltype="cf_sql_real" /> <cfprocresult resultset="1" name="qPopularProducts" maxrows="10" /> <cfprocresult resultset="2" name="qRecentProducts" maxrows="10" /> <cfprocresult resultset="3" name="qSearchResults" /> </cfstoredproc> <cfif len(trim(form.categoryid))> ... do something ... </cfif> <cfoutput query="qSearchResults"> ... display the results ... </cfoutput>
Here is the same stored procedure executed using the procedure object.
<cfscript> // get the datasource ds = request.DataFaucet.getDatasource(); // get a procedure object proc = ds.getProcedure().init( procedure = "usp_ComplexProductSearch" parameters = form, results = "PopularProducts, RecentProducts, SearchResults", maxrows = "10,10" // limit popular & recent to 10 records ); // execute the procedure and return the results struct q = proc.execute(); </cfscript> <cfif len(trim(proc.getParam("categoryid")))> ... do something ... </cfif> <cfoutput query="q.SearchResults"> ... display the results ... </cfoutput>
What you might notice in this example is that the parameters are passed into the procedure object as a structure (the form scope in this case) without any care to the order of the parameters. This is at least currently unique in the ColdFusion world, where execution of stored procedures with named parameters is still unavailable in the native language. (Despite what you may have heard, the dbvarname attribute still doesn't work in Adobe ColdFusion 8.) The DataFaucet system however is able to execute stored procedures with named parameters and uses this to streamline the procedure object.
Individual parameters may be individually or separately set via the param(name, value) method or fetched via the getParam(name) method.
The stored procedure object has the following properties:
|procedure||n/a||The stored procedure to execute|
|results||n/a||an array or comma delimited list of names for the desired result sets (queries) from the stored procedure|
|maxrows||n/a||an array or comma delimited list of maxrows for the desired result sets - any remaining result sets will default to -1 (no limit)|
|parameters||n/a||a structure containing parameters to use when executing the stored procedure|
|debug||false||boolean - enables the server's debugging for the executed procedure|
|nulldefaults||true||boolean - if true nulls are passed to the procedure for missing parameters, otherwise the procedure aborts at the first missing parameter and uses database defaults|
|null||n/a||a comma delimited list of parameters to explicitly set as null|
|returnCode||statusCode||the name of a variable to populate in the parameters attribute with the status code returned by the procedure - status code is not populated if this attribute is an empty string|
|blockFactor||1||1-100 - maps to the cfstoredproc tag's blockfactor attribute|