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_)

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,

<cfstoredproc procedure= "sp_proc">

can be used as

spService.setProcedure("sp_proc");

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

See also

cfstoredprocFunction summary

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>

 

This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License  Twitter™ and Facebook posts are not covered under the terms of Creative Commons.

Legal Notices   |   Online Privacy Policy