About recordsets



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

When you execute a database query, Adobe ColdFusion retrieves the data in a recordset. In addition to presenting recordset data to the user, you can manipulate this recordset to improve the performance of your application.

Because a recordset 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 recordset in which each row is a message and each column is a message component, such as To, From, and Subject.

Creating a recordset

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

Creating a recordset with the QueryNew() function

In addition to creating a recordset by using a cfquery or other CFML tags, you can create it 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