cfstoredproc

Description

Executes stored procedures by an ODBC or native connection to a server database. It specifies database connection information and identifies the stored procedure.

Syntax

<cfstoredproc procedure = "procedure name"
  dataSource = "ds_name"
  dbType = "type"
  username = "username"
  password = "password"
  dbServer = "dbms"
  connectString = "connection string"
  dbName = "database name"
  blockFactor = "blocksize"
  provider = "COMProvider" 
  providerDSN = "datasource" 
  debug = "Yes" or "No"
  returnCode = "Yes" or "No">

Attributes

Attribute

Description

procedure
Required. The name of the stored procedure on the database server.

dataSource
Required for all dbType operations except dbType = "dynamic". The name of an ODBC or native data source that points to the database that contains the stored procedure.

dbType
Optional. The database driver type:

dynamic Connect to an ODBC data source that is not defined in the ColdFusion Administrator. When you use this attribute value, you must specify all the ODBC connection information using the connectstring attribute.

ODBC    (default) ODBC driver.

Oracle73    Oracle 7.3 native database driver. Using this option, the ColdFusion Server computer must have Oracle 7.3.4.0.0 (or greater) client software installed.

Oracle80    Oracle 8.0 native database driver. Using this option, the ColdFusion Server computer must have Oracle 8.0 (or greater) client software installed.

Sybase11    Sybase System 11 native database driver. Using this option, the ColdFusion Server computer must have Sybase 11.1.1 (or greater) client software installed. Sybase patch ebf 7729 is recommended.

OLEDB    OLE DB provider. If specified, this database provider overrides the driver type specified in the ColdFusion Administrator.

DB2    DB2 5.2 native database driver.

Informix73    Informix73 native database driver.

username
Optional. If specified, username overrides the username value specified in the data source setup.

password
Optional. If specified, password overrides the password value specified in the data source setup.

dbServer
Optional. For native database drivers, specifies the name of the database server computer. If specified, dbServer overrides the server specified in the data source.

connectString
Required for dbType = "dynamic"; optional for all others. The contents of a connection string to send to the ODBC server. If you are connecting to a data source defined in the ColdFusion Administrator, you can use this attribute to specify additional connection details or to override connection information specified in the Administrator. If you are dynamically connecting to a datasource by specifying dbType = "dynamic", the connection string must specify all required ODBC connection attributes.

dbName
Optional. The database name (Sybase System 11 driver only). If specified, dbName overrides the default database specified in the data source.

blockFactor
Optional. Specifies the maximum number of rows to fetch at a time from the server. The range is 1 (default) to 100. The ODBC driver may dynamically reduce the block factor at runtime.

provider
Optional. COM provider (OLE-DB only).

providerDSN
Optional. Data source name for the COM provider (OLE-DB only).

debug
Optional. Yes or No. Specifies whether debug info will be listed on each statement. Default is No.

returnCode
Optional. Yes or No. Specifies whether the tag populates cfstoredproc.statusCode with the status code returned by the stored procedure. Default is No.

Attribute	Description
procedure	Required. The name of the stored procedure on the database server.
dataSource	Required for all `dbType` operations except `dbType = "dynamic"`. The name of an ODBC or native data source that points to the database that contains the stored procedure.
dbType	Optional. The database driver type: `dynamic` Connect to an ODBC data source that is not defined in the ColdFusion Administrator. When you use this attribute value, you must specify all the ODBC connection information using the `connectstring` attribute. `ODBC` (default) ODBC driver. `Oracle73` Oracle 7.3 native database driver. Using this option, the ColdFusion Server computer must have Oracle 7.3.4.0.0 (or greater) client software installed. `Oracle80` Oracle 8.0 native database driver. Using this option, the ColdFusion Server computer must have Oracle 8.0 (or greater) client software installed. `Sybase11` Sybase System 11 native database driver. Using this option, the ColdFusion Server computer must have Sybase 11.1.1 (or greater) client software installed. Sybase patch ebf 7729 is recommended. `OLEDB` OLE DB provider. If specified, this database provider overrides the driver type specified in the ColdFusion Administrator. `DB2` DB2 5.2 native database driver. `Informix73` Informix73 native database driver.
username	Optional. If specified, `username` overrides the username value specified in the data source setup.
password	Optional. If specified, `password` overrides the password value specified in the data source setup.
dbServer	Optional. For native database drivers, specifies the name of the database server computer. If specified, `dbServer` overrides the server specified in the data source.
connectString	Required for `dbType = "dynamic"`; optional for all others. The contents of a connection string to send to the ODBC server. If you are connecting to a data source defined in the ColdFusion Administrator, you can use this attribute to specify additional connection details or to override connection information specified in the Administrator. If you are dynamically connecting to a datasource by specifying `dbType = "dynamic"`, the connection string must specify all required ODBC connection attributes.
dbName	Optional. The database name (Sybase System 11 driver only). If specified, `dbName` overrides the default database specified in the data source.
blockFactor	Optional. Specifies the maximum number of rows to fetch at a time from the server. The range is 1 (default) to 100. The ODBC driver may dynamically reduce the block factor at runtime.
provider	Optional. COM provider (OLE-DB only).
providerDSN	Optional. Data source name for the COM provider (OLE-DB only).
debug	Optional. Yes or No. Specifies whether debug info will be listed on each statement. Default is No.
returnCode	Optional. Yes or No. Specifies whether the tag populates `cfstoredproc`.`statusCode` with the status code returned by the stored procedure. Default is No.

Usage

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

If you set the returnCode parameter to "Yes", cfstoredproc sets a variable called cfstoredproc.statusCode, which indicates the status code for the stored procedure. Stored procedure status code values vary by DBMS. Refer to your DBMS-specific documentation for the meaning of individual status code values.

In addition to returning a status code, cfstoredproc sets a variable called cfstoredproc.ExecutionTime. This variable contains the number of milliseconds that it took the stored procedure to execute.

Stored procedures represent an advanced feature, found in high-end database management systems, such as Oracle 8 and Sybase. You should be familiar with stored procedures and their usage before implementing these tags. The following examples use a Sybase stored procedure; for an example of an Oracle 8 stored procedure, see cfprocparam.

Example

...
<!--- The following 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 tag --->
<cfstoredproc procedure = "foo_proc"
  dataSource = "MY_SYBASE_TEST"  username = "sa"
  password = ""  dbServer = "scup"  dbName = "pubs2"
  returnCode = "Yes"  debug = "Yes">
<!--- cfprocresult tags --->
<cfprocresult name = RS1>
<cfprocresult name = RS3 resultSet = 3>
<!--- cfprocparam tags --->
<cfprocparam type = "IN"
  CFSQLType = CF_SQL_INTEGER
    value = "1"  dbVarName = @param1>
    
<cfprocparam type = "OUT"  CFSQLType = CF_SQL_DATE
  variable = FOO dbVarName = @param2>
<!--- Close the cfstoredproc tag --->
</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>
...

LiveDocs comments are not longer enabled for ColdFusion 5.0. Please use one of the following resources instead.

Version 5.0