Darren,
I have run into this problem as well. One way I delt with it, if you have enough ram,
is you can write the resultset to a session
variable and reference it that way....I guess it depends on the size of the resultset
and how many users will be making requests
to the search engine as to whether this will be of any use in your situation. You can
also code something into the Application.cfm
page that would look for a certain url or form variable associated with the search
results and if it wasn't there delete the
variable holding the recordset so you didn't tie up resources when the user was no
longer paging through the set.
Frederic
Darren Nickerson wrote:
>
> > 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
