storedproc

Description

Used to execute a stored procedure in a server database using CFScript. It specifies database connection information and identifies the stored procedure.

Syntax

Mode	Syntax
Creating the service	new storedProc() or createObject("component", "storedproc")
Initializing the attributes	Any one of the following: storedProcService=new storedproc(attribute-value_pair) storedprocService.setAttributes(attribute-value_pair) storedProcService.set_AttributeName_(attribute_value) storedProcService.execute(attribute-value_pair)
Executing the service action	storedProcService.execute(_attribute-value_pair_)

Mode

Syntax

Creating the service

new storedProc() or createObject("component", "storedproc")

Initializing the attributes

Any one of the following:

storedProcService=new storedproc(attribute-value_pair)
storedprocService.setAttributes(attribute-value_pair)
storedProcService.set_AttributeName_(attribute_value)
storedProcService.execute(attribute-value_pair)

Executing the service action

storedProcService.execute(_attribute-value_pair_)

Properties

datasource	procedure	debug	cachedafter
cachedwithin	blockfactor	password	result
returncode	username

All attributes supported by the tag cfstoredproc are supported as attribute-value pairs. For example,

can be used as

spService.setProcedure("sp_proc");

For details of the cfstoredproc tag attributes, see the Attributes section for cfstoredproc__.

History

ColdFusion 9: Added this function.

Methods

addParam

Description	Used to add cfprocparam tags.
Syntax	storedprocService.addParam(attribute-value pair)
Returns	Nothing
Arguments	All attributes supported by cfprocparam tag can be used as attribute-value pairs.

addProcResult

Description	Used to add cfprocresult tags to associate a query object with a result set returned by a stored procedure.
Syntax	storedprocService.addProcResult(attribute-value pair)
Returns	Nothing
Arguments	All attributes supported by the cfprocresult tag can be used as attribute-value pairs.

execute

Description

Used to execute a stored procedure.

Returns

A component on which the following methods can be invoked:
getProcResultSets(): To access result sets returned by the procedure.
getProcOutVariables(): To access OUT or INOUTvariables returned by the procedure.

Syntax

storedprocService.execute(attribute-value pair)

Arguments

All attributes supported by the cfstoredproc tag.

setAttributes

Description	Sets attributes for the storedproc function.
Returns	Nothing
Syntax	storedProcService.setAttributes (attribute-value pair)
Arguments	All attributes supported by the cfstoredproc tag.

getAttributes

Description	Gets attributes that were set for the storedproc function.
Returns	Returns a struct with all or some of the attribute values.
Syntax	storedProcService.get_Attributes_ (attributelist)
Arguments	A comma-separated list of attributes. If no list is specified, all defined attributes are returned.

clearAttributes

Description

Removes all attributes added for the storedProc function.

Returns

Nothing

Syntax

storedProcService.clearAttributes(attribute_list)

Arguments

A comma-separated list of attributes.
clearParams

Description

Removes cfprocparam tags added using the addParam method.

Returns

Nothing

Syntax

storedProcService.clearParams()

Arguments

None
clearProcResults

Description

Removes cfprocresult tags added using the addProcResults method.

Returns

Nothing

Syntax

storedProcService.clearProcResults()

Arguments

None
clear

Description

Removes all attributes and params that were added using the methods addProcResults and addParam.

Returns

Nothing

Syntax

storedProcService.clear()

Arguments

None

Usage

This function corresponds to the cfstoredproc tag. For usage details, refer to the Usage section for cfstoredproc.

Example

<cfscript>
//If submitting a new book, insert the record and display confirmation
if(isDefined("form.title"))
{
//create a new storedproc service
spService = new storedproc();
//set attributes using implicit setters
spService.setDatasource("books");
spService.setProcedure("Insert_Book");
//add procparams using addParam
spService.addParam(cfsqltype="cf_sql_varchar", type="in",value=form.title);
spService.addParam(cfsqltype="cf_sql_numeric",type="in",value=form.price);
spService.addParam(cfsqltype="cf_sql_date", type="in",value=form.publishDate);
spService.addParam(cfsqltype="cf_sql_numeric",type="out",variable="bookId");
//add procresults using addProcResult
spService.addProcResult(name="rs1",resultset=1);
//execute the stored procedure
result = spService.execute();
//getprocOutVariables() returns any OUT or INOUT varibles added using addParams()
bookId = result.getprocOutVariables().bookId;
//getProcResultSets() returns resultsets added using addProcresult()
listOfBooks = result.getProcResultSets().rs1;
WriteOutput("<h3>List of Books</h3>");
writeDump(listOfBooks);
//output data
WriteOutput("<h3>" & "'" & form.title & "'" & " inserted into database. The ID is " & bookId & ".</h3>");
}
</cfscript>
<cfform action="#CGI.SCRIPT_NAME#" method="post">
<h3>Insert a new book</h3>
<table>
<tr>
<td>Title:</td>
<td><cfinput type="text" size="20" required="yes" name="title"/></td>
</tr>
<tr>
<td>Price:</td>
<td><cfinput type="text" size="20" required="yes" name="price" validate="float" /></td>
</tr>
<tr>
<td>Publish Date:</td>
<td>
<cfinput type="datefield" name="publishdate" mask="mm/dd/yyyy" size="20" ></td>
</tr>
</table>
<input type="submit" value="Insert Book"/>
</cfform>

storedproc

Applies to

Description

Syntax

Properties

See also

History

Methods

Usage

Example

Description	Used to execute a stored procedure.
Returns	A component on which the following methods can be invoked:

Syntax	storedprocService.execute(attribute-value pair)
Arguments	All attributes supported by the cfstoredproc tag.

Description	Removes all attributes added for the storedProc function.
Returns	Nothing
Syntax	storedProcService.clearAttributes(attribute_list)
Arguments	A comma-separated list of attributes.

Description	Removes cfprocparam tags added using the addParam method.
Returns	Nothing
Syntax	storedProcService.clearParams()
Arguments	None

Description	Removes cfprocresult tags added using the addProcResults method.
Returns	Nothing
Syntax	storedProcService.clearProcResults()
Arguments	None

Description	Removes all attributes and params that were added using the methods addProcResults and addParam.
Returns	Nothing
Syntax	storedProcService.clear()
Arguments	None