Skip to main content
Participating Frequently
September 25, 2009
Question

Pulling Data from StoredProcs using CFC's

  • September 25, 2009
  • 2 replies
  • 751 views

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#', @8978565="#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.

    This topic has been closed for replies.

    2 replies

    Participating Frequently
    September 25, 2009

    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.

    Inspiring
    September 25, 2009
    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>

    Inspiring
    September 25, 2009

    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.