Getting information about query results



Each time you query a database with the cfquery tag, you get the data (the recordset) and the query variables; together they make up the query object. The following table describes the query variables, which are sometimes called query properties:

Variable

Description

RecordCount

The total number of records returned by the query.

ColumnList

A comma-delimited list of the query columns, in alphabetical order.

SQL

The SQL statement executed.

Cached

Whether the query was cached.

SQLParameters

Ordered array of cfqueryparam values.

ExecutionTime

Cumulative time required to process the query, in milliseconds.

In your CFML code, use these variables as if they are columns in a database table. Use the result attribute to specify the name of the structure that ColdFusion populates with these variables. You then use that structure name to refer to the query variables as the following example shows:

Output information about the query on your page

  1. Edit emplist.cfm so that it appears as follows:

    <cfset Emp_ID = 1> 
<cfquery name="EmpList" datasource="cfdocexamples" result="tmpResult"> 
    SELECT FirstName, LastName, Salary, Contract 
    FROM Employee 
    WHERE Emp_ID = <cfqueryPARAM value = "#Emp_ID#" 
                        CFSQLType = "CF_SQL_INTEGER"> 
</cfquery> 
<cfoutput query="EmpList"> 
    #EmpList.FirstName#, #EmpList.LastName#, #EmpList.Salary#, #EmpList.Contract#<br>  
</cfoutput> <br>  
<cfoutput> 
    The query returned #tmpResult.RecordCount# records.<br> 
    The query columns are:#tmpResult.ColumnList#.<br> 
    The SQL is #tmpResult.SQL#.<br> 
    Whether the query was cached: #tmpResult.Cached#.<br>  
    Query execution time: #tmpResult.ExecutionTime#.<br> 
</cfoutput> 
<cfdump var="#tmpResult.SQLParameters#">

  2. Save the file and view it in your web browser:

    The number of employees now appears below the list of employees. If necessary, refresh your browser and scroll to see the RecordCount output.

Reviewing the code

You now display the number of records retrieved in the query. The following table describes the code and its function:

Code

Description

<cfoutput>

Displays what follows.

The query returned

Displays the text “The query returned”.

#EmpList.RecordCount#

Displays the number of records retrieved in the EmpList query.

records.

Displays the text “records.”

</cfoutput>

Ends the cfoutput block.

Query variable notes and considerations

When using query variables, keep in mind the following guidelines:

  • Reference the query variable within a cfoutput block so that ColdFusion outputs the query variable value to the page.

  • Surround the query variable reference with number signs (#) so that ColdFusion knows to replace the variable name with its current value.

  • Do not use the cfoutput tag query attribute when you output the RecordCount or ColumnList property. If you do, you get one copy of the output for each row. Instead, prefix the variable with the name of the query.