cfstoredproc

Description

Executes a stored procedure by a JDBC connection to a server database. It specifies database connection information and identifies the stored procedure.

Syntax

<cfstoredproc 
  procedure = "procedure name"
  dataSource = "ds_name"
  username = "username"
  password = "password"
  blockFactor = "blocksize"
  debug = "Yes" or "No"
  returnCode = "Yes" or "No">

History

New in ColdFusion MX: The connectString, dbName, dbServer, dbtype, provider and providerDSN attributes are deprecated. Do not use them. They do not work, and might cause an error, in releases later than ColdFusion 5. (ColdFusion uses Type 4 JDBC drivers.)

Attributes

Attribute Req/Opt Default Description

procedure
Required

Name of stored procedure on database server.

dataSource
Required

Name of data source that points to database that contains stored procedure.

username
Optional

Overrides username in data source setup.

password
Optional

Overrides password in data source setup.

blockFactor
Optional
1
Maximum number of rows to get at a time from server. Range is 1 to 100.

debug
Optional
No

Yes: Lists debug information on each statement

No

returnCode
Optional
No

Yes: Tag populates cfstoredproc.statusCode with status code returned by stored procedure.

No

Attribute	Req/Opt	Default	Description
procedure	Required		Name of stored procedure on database server.
dataSource	Required		Name of data source that points to database that contains stored procedure.
username	Optional		Overrides username in data source setup.
password	Optional		Overrides password in data source setup.
blockFactor	Optional	1	Maximum number of rows to get at a time from server. Range is 1 to 100.
debug	Optional	No	Yes: Lists debug information on each statement No
returnCode	Optional	No	Yes: Tag populates `cfstoredproc.statusCode` with status code returned by stored procedure. No

Usage

Within this tag, you code cfprocresult and cfprocparam tags as necessary.

If you set returnCode = "Yes", this tag sets the variable cfstoredproc.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 cfstoredproc.ExecutionTime, which contains the execution time of the stored procedure, in milliseconds.

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 stored procedure, see cfprocparam.

Example

<!--- 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>
--->

Version 6

Comments are no longer accepted for ColdFusion MX. ColdFusion 8 is the current version.

Comments

luckydoginLR said on Mar 27, 2003 at 5:18 PM :

I find that in CF MX, the CFSTOREDPROC must have a CFPROCPARAM for each and every element that is defined in the procedure that it refers to. Additionally, these CFPROCPARAMs must appear in the same order as the elements in the definition ColdFusion 5.0 did not require this. We have other developers that are migrating from 5.0 to MX, and they don't seem to have this handycap either. What is the truth on the matter? Thanks.

earnshavian said on Apr 7, 2003 at 4:12 PM :

There is no information on the benefits of setting debug="yes". How do I access the information it generates?

No screen name said on Jun 14, 2006 at 8:35 AM :

Hi,
I am using Coldfusion Ver 4.5.2 and found that while using CFSTOREDPROC, all the params in the definition of the Stored Proc must be listed as CFPROCPARAM and also in the same order, else it works unpredictably.
The alternative is using cfquery and using parameter=value pairs.

RSS feed | Send me an e-mail when comments are added to this page | Comment Report

Current page: http://livedocs.adobe.com/coldfusion/6/CFML_Reference/Tags-pt39.htm