About record sets

Query of Queries is based on manipulating the record set, which you can create using the cfquery tag and other ways.

When you execute a database query, ColdFusion retrieves the data in a record set. In addition to presenting record set data to the user, you can manipulate this record set to improve your application's performance.

Because a record set contains rows (records) and columns (fields), you can think of it as a virtual database table, or as a spreadsheet. For example, the cfpop tag retrieves a record set in which each row is a message and each column is a message component, such as To, From, and Subject.

Referencing queries as objects

You can reference ColdFusion queries as objects by assigning a query to a variable, as follows:

<cfquery name = "query01"

  datasource = "myDNS"
  SELECT * FROM CUSTOMERS
</cfquery>
...
<cfset query02 = query01>

The query is not copied; both names point to the same record set data. Therefore, if you make changes to the table referenced in query01, the original query and the query object called query02 both reflect these changes. If you perform a copy with an array, the array is copied.

Creating a record set

You can perform a Query of Queries on any ColdFusion tag or function that generates a record set, including the following:

cfcollection
cfdirectory
cfftp
cfhttp
cfindex
cfldap
cfmail
cfpop
cfprocresult
cfquery (against a database or against another Query of Queries)
cfsearch
cfstoredproc
cfwddx
the queryNew( ); query function

Creating a record set with a function

In addition to creating a record set by using a cfquery or other CFML tags, you can create it with the queryNew() function.

To create a record set with the queryNew() function:

Create a ColdFusion page with the following content:

<html>
<head>
<title>The queryNew function</title>
</head>

<body>
<h2>QueryNew Example</h2>

<!--- create a query ---><cfset qInstruments = queryNew("name, instrument, years_playing")>

<!--- add rows --->
<cfset newrow  = queryaddrow(qInstruments, 3)>

<!--- set values in cells --->
<cfset temp = querysetcell(qInstruments, "name", "Thor", 1)>
<cfset temp = querysetcell(qInstruments, "instrument", "hammer", 1)>
<cfset temp = querysetcell(qInstruments, "years_playing", "1000", 1)>

<cfset temp = querysetcell(qInstruments, "name", "Bjorn", 2)>
<cfset temp = querysetcell(qInstruments, "instrument", "sitar", 2)>
<cfset temp = querysetcell(qInstruments, "years_playing", "24", 2)>

<cfset temp = querysetcell(qInstruments, "name", "Raoul", 3)>
<cfset temp = querysetcell(qInstruments, "instrument", "flute", 3)>
<cfset temp = querysetcell(qInstruments, "years_playing", "12", 3)>

<!--- output the query --->
<cfoutput query="qInstruments">
  <pre>#name#  #instrument# #years_playing#</pre>
</cfoutput>  

<h3>Individual record retrieval:</h3>
<cfoutput>
<p>#qInstruments.name[2]# has played #qInstruments.instrument[2]# for
#qInstruments.years_playing[2]# years.
</cfoutput>

</body>
</html>

Save the page as queryNew.cfm in the myapps directory under the web_root directory.
In your browser, enter the following URL to display the query results:
http://127.0.0.1/myapps/queryNew.cfm
The following figure shows how the output appears:

Note: When you create a record set, you can store in it complex objects, such as arrays and structures. However, you cannot use Query Of Queries on a record set that contains complex objects. For more information on Query of Queries, see "About Query of Queries".

Developing ColdFusion MX Applications with CFML
Using Query of Queries

About record sets

Referencing queries as objects

Creating a record set

Creating a record set with a function

To create a record set with the queryNew() function:

Comments