cfprocresult

Description

Associates a query object with a result set returned by a stored procedure. Other ColdFusion tags, such as cfoutput and cftable, use this query object to access the result set. This tag is nested within a cfstoredproc tag.

Syntax

<cfprocresult 
   name = "query_name"
   resultSet = "1-n" 
   maxRows = "maxrows">

Attributes

Attribute	Req/Opt	Default	Description
name	Required		Name for the query result set.
resultSet	Optional	1	Names one result set, if stored procedure returns more than one.
maxRows	Optional	-1 (All)	Maximum number of rows returned in result set.

Usage

To enable access to data returned by the stored procedure, specify one or more cfprocresult tags. If the stored procedure returns more than one result set, use the resultSet attribute to specify which of the stored procedure's result sets to return.

The resultSet attribute must be unique within the scope of the cfstoredproc tag. If you specify a result set twice, the second occurrence overwrites the first.

CFML supports Oracle 8 and 9 Reference Cursor type, which passes a parameter by reference. Parameters that are passed this way can be allocated and deallocated from memory within the execution of one application. To use reference cursors in packages or stored procedures, use the cfprocresult tag. This causes the ColdFusion JDBC database driver to put Oracle reference cursors into a result set. (You cannot use this method with Oracle's ThinClient JDBC drivers.)

Example

<!--- This example executes a Sybase stored procedure that returns three 
result sets, two of which we want. The stored procedure returns 
status code and one output parameter, which we display. We use 
named notation for 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>
...

Version 7

Comments

gherisson said on Sep 11, 2006 at 2:31 PM :

There is a change in this tag behavior since CF5.
If you perform multiple queries in the same store procedures with SQL Server, you may get only the first row in the resultset.
In other word RecordCount is not properly instantiated, and if you try to loop the resultset, you may get a wrong result (in my case the first row only).
To fix this, I had to set the maxrows parameter to a much higher value than the number of rows expected.
Let’s hope a patch fix it.

MU said on Apr 5, 2007 at 10:52 AM :

It gets worse: when multiple <cfprocresult...> statements are used, maxrows must be specified for every statement if it's used at least once. If you don't specify it for every statement, the same maxrows setting will be used for all result sets. Use maxrows="-1" to force the retrieval of the complete resultset.

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

Current page: http://livedocs.adobe.com/coldfusion/7/htmldocs/00000314.htm