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.setAttributeName(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.

History

ColdFusion 9: Added this function.

Methods

  • addParam

    Description

    Used to addcfprocparam 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 INOUT variables 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.getAttributes (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 cfprocparamtags 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>