> Of course......here's an example of how to can an SP using CF.......
>
> <CFSTOREDPROC Procedure="<proc name>" DATASOURCE="<datasource>"
> RETURNCODE="Yes">
> <CFPROCPARAM TYPE="In" DBVARNAME="<dbvarname1>" CFSQLTYPE="<data type1>"
> VALUE="<value1>">
> <CFPROCPARAM TYPE="In" DBVARNAME="<dbvarname2>" CFSQLTYPE="<data type1>"
> VALUE="<value2>">
> <CFPROCRESULT name="results">
> </CFSTOREDPROC>
>
> <cfoutput query="results">.
> ...
> ...
> </cfoutput>
>
> You can also pass get the SP to pass back output params aswell as multiple
> resultsets.
We would love to be able to use ORACLE stored procedures to power a search facility on
our site, but we have been bitten by one of the MAJOR shortcomings of CFSTOREDPROC.
Whereas you can cache results sets using CFQUERY (CACHEWITHIN attribute), we don't
seem to be able to do this with CFSTOREDPROC.
We're trying to display the results of a large query using a "pager" . . . results
1-10 of 300, 11-20 of 300, next page, next page, etc etc. We've had to abandon the
stored procedure approach, because to display page 2 we have to execute the stored
procedure all over again and pull in the results with a REFCURSOR. If the results come
from a CFQUERY there's no problem, displaying page 2 takes advantage of the cached
results set and display is lightning fast without ever hitting the database.
Unless I'm wrong this is something you really need to take into account when
considering using stored procedures. If you want to be able to reuse the query results
you really should use CFQUERY.
-Darren
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
