cfquery ‘result’ attribute caveat

A great feature of <cfquery> which was introduced in CFMX7 is the addition of the ‘result’ attribute. When used, a developer can access a Structure of query result variables including the following keys:

  • Cache
  • ColumnList
  • ExecutionTime
  • RecordCount
  • SQL
  • SQLParameters

You can read more about the ‘result’ attribute in a previous post or at Live docs.

One thing I’ve come across is that the structure returned (by using the result attribute) is created where the query is executed and the value of ‘result’ references this struct in the local variable scope.

So an inline query followed by a reference to the ‘result’ struct would run fine:

<cfquery name="qData" dsn="myDSN" result="stResult">
	SELECT	*
	FROM	MyTable
</cfquery>

<cfoutput>
	#stResult.sql#
</cfoutput>

But a problem exists when you have a query in a Component that is being returned by a method call. In this case the structure won’t exist in the calling templates ‘local’ scope and an exception will be thrown.

stResult exception

The simple way around this is to declare the ‘result’ attribute in the request scope:

<cfquery name="qData" dsn="myDSN" result="request.stResult">
	SELECT	*
	FROM	MyTable
</cfquery>

Now you can reference the structure inside the calling template using the request scope:

<cfoutput>
	#request.stResult.sql#
	#request.stResult.cached
	etc
</cfoutput>
Post a Comment or Leave a Trackback

7 Comments

  1. April 10, 2007 at 2:37 pm | Permalink

    —– In the CFC cfquery tag

    ———————-

    This is just an example, you could certainly set a result name for each function/cfquery which would persist in the object instance.

    The other option would be to combine the returned result and query as a deep structure

  2. April 10, 2007 at 2:40 pm | Permalink

    bummer, there was some nice example code, but the blog stripped out the comments rather than encode the output. oh well. The gist of it was to use result=”this.result” which sets a property of the cfc which persists with the object instance.

  3. July 6, 2007 at 3:47 pm | Permalink

    Nice find – works like a charm – thanks.

  4. December 18, 2007 at 2:01 pm | Permalink

    Another tip, when you’re viewing the SQL, wrap it in pre tags to format it as it was typed in the cfquery tags.

  5. December 18, 2007 at 6:39 pm | Permalink

    Nice tip Adrian, thanks

  6. Allen
    August 5, 2008 at 10:54 pm | Permalink

    If you’re putting variables set in a method to the request scope, you’re code is no longer loosely coupled. You’re essentially using the method as a glorified CFINCLUDE. You should be returning the result structure just as you would return the name query.

  7. August 6, 2008 at 12:08 am | Permalink

    @Allen – agreed for production ready code which is when I really don’t want the “result” attribute.

    My data access objects typically return a “query” object, not a struct.

    So using the request scope in this example is handy for development purposes only.

    Good point though.

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*