CFCOMPONENT passing cfquery results to json

Hello everyone. I need some help with this Colfusion component.. I need to pass the values extracted form the cfqueries into the values in the cscript...

<cfcomponent>
<!--- I have the following queries --->
<cffunction name="GetNumbers">
<cfquery name="Fall2006" datasource="#db#" > 
      select sum(transfer_y1_2y_public) as y12y, sum(transfer_y2_2y_public) as y22y, sum(transfer_y3_2y_public) as y32y, sum(transfer_y4_2y_public) as y42y, sum(transfer_y5_2y_public) as y52y, sum(transfer_y6_2y_public) as y62y from transfer where transfer_entry_sector = <cfqueryparam cfsqltype="cf_sql_varchar" value="TWO_YEAR_PUBLIC">
      and transfer_year = <cfqueryparam cfsqltype="cf_sql_integer" value="2006">
</cfquery>

<cfquery name="Fall2008" datasource="#db#" > 
      select sum(transfer_y1_2y_public) as y12y, sum(transfer_y2_2y_public) as y22y, sum(transfer_y3_2y_public) as y32y, sum(transfer_y4_2y_public) as y42y, sum(transfer_y5_2y_public) as y52y, sum(transfer_y6_2y_public) as y62y from transfer where transfer_entry_sector = <cfqueryparam cfsqltype="cf_sql_varchar" value="TWO_YEAR_PUBLIC">
      and transfer_year = <cfqueryparam cfsqltype="cf_sql_integer" value="2008">
</cfquery>

<cfquery name="Fall2010" datasource="#db#" > 
      select sum(transfer_y1_2y_public) as y12y, sum(transfer_y2_2y_public) as y22y, sum(transfer_y3_2y_public) as y32y, sum(transfer_y4_2y_public) as y42y, sum(transfer_y5_2y_public) as y52y, sum(transfer_y6_2y_public) as y62y from transfer where transfer_entry_sector = <cfqueryparam cfsqltype="cf_sql_varchar" value="TWO_YEAR_PUBLIC">
      and transfer_year = <cfqueryparam cfsqltype="cf_sql_integer" value="2010">
</cfquery>

</cffunction>

<!--- I need to be able to use the result on the above query in the values below... --->
      <cfscript>
      {
            remote struct function getDataForJSVersion() returnformat="json" {
                  return {
                        "labels"    = "Y1,Y2,Y3,Y4,Y5,Y6",
                        "records"    = [
                              {
                                    "text"    ="Fall 2006",
                                    "values"=[1,2,4,8,6,7]
                              },{
                                    "text"    ="Fall 2008",
                                    "values"=[10,8,6,4,6,2]
                              },{
                                    "text"    ="Fall 2010",
                                    "values"=[1,3,9,3,1,7]
                              }
                        ]
                  };
            }
      }
      </cfscript>
</cfcomponent>
allan_leeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_agx_Commented:
Edit: Fix cfqueryparam

What's your DBMS?  There may be better options..

In terms of CF only, the queries look the same - except for the year. Just create a function accepts a #year# parameter and returns an array of values (Y1,Y2,....,Y6)

<cffunction name="getPublicTotals" returntype="array">
   <cfargument name="year" type="numeric" required="true">

   <cfquery name="local.qResults" datasource="#db#"> 
      SELECT sum(transfer_y1_2y_public) as Y1
           , sum(transfer_y2_2y_public) as Y2
           , sum(transfer_y3_2y_public) as Y3
           , sum(transfer_y4_2y_public) as Y4
           , sum(transfer_y5_2y_public) as Y5
           , sum(transfer_y6_2y_public) as Y6
      FROM  transfer 
      WHERE transfer_entry_sector = <cfqueryparam cfsqltype="cf_sql_varchar" value="TWO_YEAR_PUBLIC">
      AND   transfer_year = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.year#">
   </cfquery> 
   <cfset local.data = [ local.qResults.Y1
                         , local.qResults.Y2
                         , local.qResults.Y3
                         , local.qResults.Y4
                         , local.qResults.Y5
                         , local.qResults.Y6

	]>

   <cfreturn local.data>
</cffunction>

Open in new window


Then use that function to build the "records" array of structures

<cfscript>
    remote struct function getDataForJSVersion() returnformat="json" {
        // build array of structures for the desired range of years, ie. 2006 to 2010
        local.records = [];
        for (local.year = 2006; local.year <= 2010; local.year++) {
            arrayAppend(local.records, {"text" = "Fall "& local.year, "values"=getPublicTotals(local.year)});
        }
				
        return {"labels"    = "Y1,Y2,Y3,Y4,Y5,Y6", "records" = local.records };
    }
</cfscript>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
allan_leeAuthor Commented:
Hello agx, my DBMS is Microsoft SQL..   Your suggestion doesn't seem to work with what I'm trying to accomplish here.. I need the values from the Query to be passed on to the values inside the cfscript. Maybe this one will help explain what I'm trying to accomplish here...


<cfquery name="Fall2006" datasource="#application.settings.dsn#">
	select sum(transfer_y1_2y_public) as y12y, sum(transfer_y2_2y_public) as y22y, sum(transfer_y3_2y_public) as y32y, sum(transfer_y4_2y_public) as y42y, sum(transfer_y5_2y_public) as y52y, sum(transfer_y6_2y_public) as y62y from transfer where transfer_entry_sector = <cfqueryparam cfsqltype="cf_sql_varchar" value="TWO_YEAR_PUBLIC">
	and transfer_year = <cfqueryparam cfsqltype="cf_sql_integer" value="2006"> 
</cfquery> 

   <cfset data2006 = [ Fall2006.y12y
                         , Fall2006.y22y
                         , Fall2006.y32y
                         , Fall2006.y42y
                         , Fall2006.y52y
                         , Fall2006.y62y

	]>
	<cfset list2006 = ArrayToList(data2006)>

   <cfscript>
  remote struct function getDataForJSVersion() returnformat="json" {
      {
                  return {
                        "labels"    = "Y1,Y2,Y3,Y4,Y5,Y6",
                        "records"    = [
                              {
                                    "text"    ="Fall 2006",
                                    "values"=[list2006]   //I need the list2006 list populate this .. I can't seem to make it work
                              }
                        ]
                  };
            
      }
	  }
   </cfscript>

Open in new window

0
allan_leeAuthor Commented:
I tried
"values"=[writeDump(list2006, "false");]

Open in new window


and

 
"values"=[writeOutput(list2006);]

Open in new window


But no juice
0
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

allan_leeAuthor Commented:
So, it turned out that I was making it harder than I thought.. I was able to accomplish what I needed to do by doing this.,

<cfquery name="Fall2006" datasource="#application.settings.dsn#">
	select sum(transfer_y1_2y_public) as y12y, sum(transfer_y2_2y_public) as y22y, sum(transfer_y3_2y_public) as y32y, sum(transfer_y4_2y_public) as y42y, sum(transfer_y5_2y_public) as y52y, sum(transfer_y6_2y_public) as y62y from transfer where transfer_entry_sector = <cfqueryparam cfsqltype="cf_sql_varchar" value="TWO_YEAR_PUBLIC">
	and transfer_year = <cfqueryparam cfsqltype="cf_sql_integer" value="2006"> 
</cfquery> 

   <cfscript>
  remote struct function getDataForJSVersion() returnformat="json" {
      {
                  return {
                        "labels"    = "Y1,Y2,Y3,Y4,Y5,Y6",
                        "records"    = [
                              {
                                    "text"    ="Fall 2006",
                                    "values"=[Fall2006.y12y
                         , Fall2006.y22y
                         , Fall2006.y32y
                         , Fall2006.y42y
                         , Fall2006.y52y
                         , Fall2006.y62y] 
                              }
                        ]
                  };
            
      }
	  }
   </cfscript>

Open in new window

0
_agx_Commented:
That's basically what I suggested earlier. Only for a single year, instead of multiple like in the original post:

i.e An array of structures. Each structure represents a separate year, and the "values" are an array:

           "records"    = [
                              {
                                    "text"    ="Fall 2006",
                                    "values"=[1,2,4,8,6,7]
                              },{
                                    "text"    ="Fall 2008",
                                    "values"=[10,8,6,4,6,2]
                              },{
                                    "text"    ="Fall 2010",
                                    "values"=[1,3,9,3,1,7]
                              }

Isn't that what you need?
0
_agx_Commented:
Here's a runnable example. Notice except for the hard coded values ([1,2,3,4,5,6]), the structure of the JSON is the same as in the OP?

https://trycf.com/gist/be91588a68dc522c5c5ecba4b7602368/acf2016?theme=monokai

Results of Old and New Function
If you actually do need to include multiple years, I'd still recommend wrapping the query in a function. It's more flexible and avoids repeating the same SQL over and over:

<cfquery name="Fall2006" datasource="#db#" >  ....</cfquery>
<cfquery name="Fall2007" datasource="#db#" >  ....</cfquery>
<cfquery name="Fall2008" datasource="#db#" >   ....</cfquery>
<cfquery name="Fall2009" datasource="#db#" >   ....</cfquery>
...

Instead just call the custom function with whatever year is needed:

       <cfset result = getPublicTotals(2006)>
       ....
       <cfset result = getPublicTotals(2009)>
0
allan_leeAuthor Commented:
Yes.. your suggestion helped a lot.. So I will accept your answer....
0
_agx_Commented:
Glad it helped, but also thinking about making it more reusable :)  Do you need to construct the JSON for multiple years?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.