Pulling Data from StoredProcs using CFC's

[Genos_Free]

I have a question about selecting data from a stored proc.

I have used CFQuery to select data from storedProcs out of databases i.e.:

------------------------------------------------------------------------------------------------------------

<cffomponent displayname="data" hint="The file name is data.cfc">

<cffunction name="myFunctionName" hint="This is for an exaple on the discussion boards" returnType="query">

     <cfargument name="start" type="date" required="true">

     <cfargument name="end" type="date" required="true">

     <cfquery name="myDataSource" datasource="#dsn#" dbtype="ODBC">

          exec sp_MyStoredProc @start = '#ARGUMENTS.start#', @End="#ARGUMENTS.end#;"

     </cfquery>

     <cfreturn myDataSource>

</cffunction>

</cfcomponent>

The return on the presentation Page is this:

<cfinvoke component="data" method="myFunctionName" returnvariable="Var">

      <cfinvokeargument name="start" value="#form.start#">

      <cfinvokeargument name="end" value="#form.end#">

</cfinvoke>

<cfdump var="#Var#>

------------------------------------------------------------------------------------------------------------

The question I need answered is how do I return a select stored proc with the above logic.

This is what I have so far:

------------------------------------------------------------------------------------------------------------

<cffomponent displayname="data" hint="The file name is data.cfc">

<cffunction name="myFunctionName" hint="This is for an exaple on the discussion boards" returnType="query">

     <cfargument name="start" type="date" required="true">

     <cfargument name="end" type="date" required="true">

           <cfstoredproc procedure="myStoredProc" datasource="#dsn#">

                 <cfstoredprocparam   cfsqltype="CF_SQL_DATE" value="#ARGUMENTS.start#">

                 <cfstoredprocparam   cfsqltype="CF_SQL_DATE" value="#ARGUMENTS.end#">

                 <cfprocresult name="someData"

            </cfstoredproc>

     <cfreturn myDataSource>

</cffunction>

</cfcomponent>

------------------------------------------------------------------------------------------------------------

My questios are:

How do I query the Stored Procedure from the database and get results from it using this CFC function and do I use cfinvoke to pull the data for the display page.

Thanks in advance.

[Dan_Bracuk]

In what you have so far, the only thing really wrong is that you are not returning your procresult.  Not as wrong is the failure to use the keyword var to keep your variables local to the function.

[Genos_Free]

Ok great,

Then what syntex Do I use in the function to return that data.

Currently I am getting is that the function type is incorrect. I am a bit lost on this as I am searching the wqeb for this answer.

[-__cfSearching__-]

Then what syntex Do I use in the function to return that data.

I believe Dan is talking about the fact that the result name is  "someData", yet your function is returning a variable named "myDataSource".  Though maybe that is just psuedo code.

But whatever your result name, it should be VAR scoped at the top of your function to keep it local to the function

<cffunction ....>

    <cfargument ....>

    <cfargument ....>

    <cfset VAR yourResultVariable = "">

    ....etcetera

</cffunction>

[Genos_Free]

I am not getting what I need here.

Forget about the output being called Var.

Call it 'myDataOutput'

All I am looking for here is how to output the data to the display file using cfinvoke calling the method with somekind of return type.

[-__cfSearching__-]

Georgia_Geno wrote:

Forget about the output being called Var.

I think you are misunderstanding our comments. VAR is a scope, not a variable name.  In your original code you call a stored procedure and it returns a query:

<cfprocresult name="someData" ...>

That variable name (ie "someData") should be declared in the VAR scope at the top of your function, just after the cfarguments. Otherwise it is not local to the function, and can cause unexpected results in certain situations.

All I am looking for here is how to output the data to the display file using cfinvoke calling the method with somekind of return type.

Can you post what you have tried, ie your cfinvoke code?