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.

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:

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:

  1. Create a ColdFusion page with the following content: 
    <html>
<head>
<title>The queryNew function</title>
</head>
<body>
<h2>QueryNew Example</h2>

<!--- create a query, specify data types for each column --->
<cfset qInstruments = queryNew("name, instrument, years_playing", 
"CF_SQL_VARCHAR, CF_SQL_VARCHAR, CF_SQL_INTEGER")>

<!--- 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.</p>
</cfoutput>

</body>
</html>
  2. Save the page as queryNew.cfm in the myapps directory under the web_root directory.
  3. Display queryNew.cfm in your browser

View comments in LiveDocs