ColdFusion 9.0 Resources |
cfstoredprocDescriptionExecutes a stored procedure in a server database. It specifies database connection information and identifies the stored procedure. Syntax<cfstoredproc dataSource = "data source name" procedure = "procedure name" cachedAfter = "date" cachedWithin = "time span" debug = "yes|no" blockFactor = "block size" password = "password" result = "result name" returnCode = "yes|no" username = "user name"> Note: You
can specify this tag's attributes in an attributeCollection attribute
whose value is a structure. Specify the structure name in the attributeCollection attribute
and use the tag’s attribute names as structure keys.
See alsocfinsert, cfqueryparam, cfprocparam, cfprocresult, cftransaction, cfquery, cfupdate; Optimizing database use in the Developing ColdFusion Applications HistoryColdFusion MX 7: Added the result attribute. ColdFusion MX: Deprecated the connectString, dbName, dbServer, dbtype, provider, and providerDSN attributes. They do not work, and might cause an error, in releases later than ColdFusion 5. (Releases starting with ColdFusion MX use Type 4 JDBC drivers.) Attributes
UsageUse this tag to call a database stored procedure. Within this tag, you code cfprocresult and cfprocparam tags as follows:
If you set returnCode = "Yes", this tag sets the variable prefix.statusCode, which holds the status code for a stored procedure. Status code values vary by DBMS. For the meaning of code values, see your DBMS documentation. This tag sets the variable prefix.ExecutionTime, which contains the execution time of the stored procedure, in milliseconds. The value of prefix is either cfstoredproc or the value specified by the result attribute, if it is set. The result attribute provides a way for stored procedures that are called from multiple pages, possibly at the same time, to avoid overwriting the results of one call with another. If you set the result attribute to myResult, for example, you would access ExecutionTime as myResult.ExecutionTime. Otherwise, you would access it as cfstoredproc.ExecutionTime. Before implementing this tag, ensure that you understand stored procedures and their usage. The following examples use a Sybase stored procedure; for an example of an Oracle 8 or 9 stored procedure, see cfprocparam. Example<cfset ds = "sqltst"> <!--- If submitting a new book, insert the record and display confirmation ---> <cfif isDefined("form.title")> <cfstoredproc procedure="Insert_Book" datasource="#ds#"> <cfprocparam cfsqltype="cf_sql_varchar" value="#form.title#"> <cfprocparam cfsqltype="cf_sql_numeric" value="#form.price#"> <cfprocparam cfsqltype="cf_sql_date" value="#form.price#"> <cfprocparam cfsqltype="cf_sql_numeric" type="out" variable="bookId"> </cfstoredproc> <cfoutput> <h3>'#form.title#' inserted into database.The ID is #bookId#.</h3> </cfoutput> </cfif> <cfform action="#CGI.SCRIPT_NAME#" method="post"> <h3>Insert a new book</h3> Title: <cfinput type="text" size="20" required="yes" name="title"/> <br/> Price: <cfinput type="text" size="20" required="yes" name="price" validate="float" /> <br/> Publish Date: <cfinput type="text" size="5" required="yes" name="publishDate" validate="date" /> <br/> <input type="submit" value="Insert Book"/> </cfform> <!--- This view-only example executes a Sybase stored procedure that returns three result sets, two of which we want. The stored procedure returns the status code and one output parameter, which we display. We use named notation for the parameters. ---> <!--- <cfstoredproc procedure = "foo_proc" dataSource = "MY_SYBASE_TEST" username = "sa" password = "" dbServer = "scup" dbName = "pubs2" returnCode = "Yes" debug = "Yes"> <cfprocresult name = RS1> <cfprocresult name = RS3 resultSet = 3> <cfprocparam type = "IN" CFSQLType = CF_SQL_INTEGER value = "1" dbVarName = @param1> <cfprocparam type = "OUT" CFSQLType = CF_SQL_DATE variable = FOO dbVarName = @param2> </cfstoredproc> ---> <!--- <cfoutput> The output param value: '#foo#'<br></cfoutput> <h3>The Results Information</h3> <cfoutput query = RS1>#name#,#DATE_COL#<br></cfoutput><p> <cfoutput> <hr> <p>Record Count: #RS1.recordCount# >p>Columns: #RS1.columnList# <hr> </cfoutput> <cfoutput query = RS3>#col1#,#col2#,#col3#<br> </cfoutput><p> <cfoutput> <hr> <p>Record Count: #RS3.recordCount# <p>Columns: #RS3.columnList# <hr> The return code for the stored procedure is: '#cfstoredproc.statusCode#'<br> </cfoutput> ---> |