Skip to main content
J
jpcane
Participant
February 11, 2010
Answered

Query Reference Data and CFCs

  • February 11, 2010
  • 1 reply
  • 516 views

I would to run a  query once that returns a record set and pass that as reference data to functions in a ColdFusion component. Inside those functions, I'd take the query and run a query of queries for aggregate results among other things. Can this be done? I am getting an error basically saying the passed-in query is not a simple value where I use it in the FROM clause of the query.

It works fine when both queries are within the same function, but it's needless overhead to continually get the same ref data.

Example:

template.cfm

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

...

<cfobject name="lib" component="mycfc">

<cfinvoke component="#lib#" method="getQueryRefData" returnvariable="refData" />

<cfinvoke component="#lib#" method="getSum" returnvariable="sum" refdata="#refData#" />

...

mycfc.cfc

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

...

<cffunction name="getQueryRefData" access="public" returntype="query">
        <cfquery name="allData" datasource="myDatasource">
            SELECT * FROM answer
        </cfquery>

        <cfreturn allData>
</cffunction>

<cffunction name="getSum" access="public" returntype="numeric">

     <cfargument name="refData" type="query" required="yes">

     <cfquery name="deptSum" dbtype="query">
            SELECT COUNT(*) AS total, department
            FROM ARGUMENTS.refData <--- ERROR occurs here --->
            GROUP BY department
      </cfquery>

      <cfreturn Val(deptSum.total)>

</cffunction>

...

    This topic has been closed for replies.
    Correct answer BKBK
    You're right, I left them out in my original post. But they are in the actual code.

    No, that is indeed the problem. You have to leave the # signs out.  It has to be just ARGUMENTS.refData or refData, because it's the name of the query that follows "FROM", not the query object itself.

    1 reply

    D
    Dan_Bracuk
    Inspiring
    February 11, 2010

    You forgot the octothorps.

    J
    jpcaneAuthor
    Participant
    February 11, 2010

    You're right, I left them out in my original post. But they are in the actual code. The question remains. Anyone have suggestions? Thank you.

    D
    Dan_Bracuk
    Inspiring
    February 11, 2010

    This works when I run it.

    <cffunction name="q_of_q" returntype="query">
    <cfargument name="QueryIn" type="query">
    <cfquery name="QueryOut" dbtype="query">
    select count(*) thecount
    from arguments.queryin
    </cfquery>
    <cfreturn QueryOut>
    </cffunction>

    <cfquery name="x" datasource="burns">
    select 1 f1 from dual
    </cfquery>

    <cfset abc = q_of_q(Queryin = x)>
    <cfdump var="#abc#" label="abc">

    Try it and if it works, start modifying it until it either does what you want it to do, or crashes.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices