This week only. Pastebin PRO Accounts Christmas Special! Don't miss out!Want more features on Pastebin? Sign Up, it's FREE!
Guest

ColdFusion Experiment with Nested Query Objects

By: a guest on Feb 12th, 2014  |  syntax: None  |  size: 1.90 KB  |  views: 32  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. <!--- start by building a new query object --->
  2. <cfset masterQuery = QueryNew("ID,Name,SubQuery", "INTEGER,VARCHAR,OBJECT")>
  3.  
  4. <!--- add a row and some basic data --->
  5. <cfset QueryAddRow(masterQuery)>
  6. <cfset QuerySetCell(masterQuery, "ID",1)>
  7. <cfset QuerySetCell(masterQuery, "Name", "Dave")>
  8.    
  9. <!--- create a new sub query --->
  10. <cfset subQuery = QueryNew("TestColumn1,TestColumn2","VARCHAR,VARCHAR")>
  11.  
  12. <!--- lets add a few rows to the subQuery --->
  13. <cfloop from="1" to="3" index="b">
  14.        
  15.         <!--- new row --->
  16.         <cfset queryAddRow(subQuery)>
  17.        
  18.     <!--- subQuery data --->
  19.         <cfset QuerySetCell(subQuery,"TestColumn1","Hello World")>
  20.         <cfset QuerySetCell(subQuery,"TestColumn2","All Your Base are Belong to Us")>
  21.        
  22. </cfloop>
  23.  
  24. <!--- populate the master query column "subQuery" with the new query object we created --->
  25. <cfset QuerySetCell(masterQuery, "SubQuery", subQuery)>
  26.  
  27. <!--- dump the data so we can make sure it looks good --->
  28. <cfdump var="#masterQuery#">
  29.  
  30.  
  31. <!--- now loop through the query, and assign all of the columns in the query to the variables scope --->
  32. <cfloop list="#masterQuery.columnList#" index="a">
  33.  
  34.         <cfset "VARIABLES.#a#" = masterQuery["#a#"]>
  35.  
  36. </cfloop>
  37.  
  38.  
  39. <!--- this generates an error in CF 10 (maybe other versions) --->
  40. <cftry>
  41.  
  42.     <!--- try and run a QofQ --->
  43.     <cfquery dbtype="query" name="getSubQuery">
  44.     SELECT *
  45.     FROM subQuery
  46.     </cfquery>  
  47.    
  48.     <cfdump var="#getSubQuery#">
  49.  
  50.     <cfcatch type="any">
  51.         <cfdump var="ERROR: #cfcatch.Message#">
  52.     </cfcatch>
  53.  
  54. </cftry>
  55.  
  56. <!--- Now try using evaluate() instead.  This doesn't generate an error for some reason --->
  57. <cfloop list="#masterQuery.columnList#" index="a">
  58.  
  59.         <cfset "VARIABLES.#a#" = evaluate('masterQuery.#a#')>
  60.  
  61. </cfloop>
  62.  
  63. <!--- run the QofQ again --->
  64. <cfquery dbtype="query" name="getSubQuery">
  65. SELECT *
  66. FROM subQuery
  67. </cfquery>
  68.  
  69. <cfdump var="#getSubQuery#">
clone this paste RAW Paste Data