21949

do I have to scope query output in Coldfusion?

Question:

If I'm a running a database query/stored procedure in Coldfusion, what is the proper way to reference fields returned from the query?

<cfstoredproc procedure="proc_select_extern" datasource="stokkers"> <cfprocparam type="in" value="#Session.Extern#" cfsqltype="cf_sql_varchar" maxlength="13"> <cfprocresult name="extern"> </cfstoredproc> <cfoutput query="extern"> <cfset variables.some = extern.foo> OR <cfset variables.some = foo> </cfouput>

Say <strong>extern</strong> includes foo, bar and foobar. Is it allowed and better to write:

extern.foo; extern.bar; extern.foobar;

because I'm running through a page and often find these "naked" variables a little confusing to follow:

foo; bar; foobar;

There is a lot of info on scopes and proper scoping but I have not found anything on query-output.

Thanks for clarification!

Answer1:

Some will tell you that it is good habitual practice to always scope because it keeps you from making scoping errors where it is really important.

Personally in views I like the approach of using cfoutput with a query and NOT having to scope - it's the equivalent of "WITH" in other languages. Since the query will always be evaluated before form and url scopes within a query driven cfoutput tag I do not see any issues with leaving off the scope in that instance. Keep in mind that in CFCs the "arguments" and local scope will both be preemptive - but that's not the best place for a query driven cfoutput - which is designed (ably designed) for convenient <em>display</em>.

But again.. others will tell you different (with some passion as well :) .

Answer2:

There are scoping issues with this if you do not fully scope your variables.

You'll get people saying that you wont encounter the issues enough to justify the extra typing, and it's not DRY, however because ColdFusion has a <a href="http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec09af4-7fdf.html" rel="nofollow">scope evaluation order</a> it's required if you have code that you want to work no matter the context.

By 'query loop' below I mean a cfloop or cfoutput with a query argument.

So you <em>could</em> use #columnname# within a query loop.

You <em>could</em> #queryName.columnName# inside or outside of a query loop.

You <em>should</em> #cfScope.queryName.columnName# in all cases.

Here's an example of things going wrong. Hopefully you'll never have to deal with code like this, but it serves to point out the issues with ColdFusion's extensive <a href="http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec09af4-7fdf.html" rel="nofollow">scope evaluation</a>.

<cfset testcfc = new Test().scopeTest()>

With

<cfcomponent output="false"> <cffunction name="scopeTest" access="public" output="true" returntype="void"> <cfargument name="Query" type="query" required="false" default="#QueryNew("xarguments")#"> <cfargument name="xlocal" type="string" required="false" default="This is not from a query; Arguments scope."> <cfset QueryAddRow(Arguments.Query, 1)> <cfset Arguments.Query["xarguments"][1] = "this is the arguments scope query"> <cfset local.Query = QueryNew("xlocal")> <cfset QueryAddRow(local.Query, 1)> <cfset local.Query["xlocal"][1] = "this is the local scope query"> <cfset Variables.Query = QueryNew("xVariables")> <cfset QueryAddRow(Variables.Query, 1)> <cfset Variables.Query["xVariables"][1] = "this is the variables scope query"> <cfset local.xlocal = "This is not from a query; local scope."> <cfloop query="Query"> <cfoutput>#xlocal#</cfoutput> </cfloop> <cfdump var="#Arguments#" label="Arguments"> <cfdump var="#local#" label="local"> <cfdump var="#variables#" label="Variables"> <cfabort> </cffunction> </cfcomponent>

The result of the output is <em>This is not from a query; Arguments scope.</em> Contrary to what <a href="http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec09af4-7fdf.html" rel="nofollow">the scope evaluation docs</a>, and what others would have you believe.

As others have suggested you can change the output line to read <cfoutput>#Query.xlocal#</cfoutput> but that doesn't help either. Instead you're told that the column doesn't exist. Changing it to <cfoutput>#Query.xarguments#</cfoutput> will show that it was using the Arguments version of Query instead of the local or Variables.

So how about:

<cfloop query="local.Query"> <cfoutput>#xlocal#</cfoutput> </cfloop>

Nope. Still not the desired result. Ok, so how about adding the query name to the output:

<cfloop query="local.Query"> <cfoutput>#Query.xlocal#</cfoutput> </cfloop>

Nope. Still not the desired result. If you want to make sure you get the right result you have to fully scope it all.

<cfloop query="local.Query"> <cfoutput>#local.Query.xlocal#</cfoutput> </cfloop>

This is way more typing than anyone wants to do, but is required if you want to make sure there aren't any nasty bugs lurking in your code.

Answer3:

I am one of those guys who would tell you that you should scope everything. It really makes it helpful when reading your own code and other code. I say "without a doubt, scope it out!"

Below is an example of how I typically do a query and then output the results.

<cfscript> Q = MyCFC.getCustomers(); if (! isQuery(Q) || Q.RecordCount == 0) { writeOutput("No records found."); } else { for (i = 1; i lte Q.RecordCount; i++) { VARIABLES.Customer = "#Q.FirstName[i]# #Q.LastName[i]#"; writeOutput(VARIABLES.Customer); writeOutput("<br>"); } } </cfscript>

Answer4:

This is a great discussion on when it is necessary and when it is valuable to scope.

I'd throw my two pennies in the direction of <strong><em>ALWAYS</em></strong> scoping if for nothing but <strong>clarity</strong> in or out of a CFC. If you have to, var q = ''; and then just reference q so you have a local scoped variable if you are lazy and believe DRY includes scoping.

I cannot tell you the number of systems and pages I have worked on that were so poorly scoped that I had to debug to figure out what came from where. Particularly in complex reports where group bys and secondary data is involved; it can get very confusing.

One peeve is where I have seen people use tricks (knowingly or not) where they default a param without scoping which would be assigned to the 'Variables' scope, and then in their code, they will intermix Form and URL, expecting one to overwrite the other, and/or Variables scoped values with no scoping so that the output would always find the default Variables version of the value....Of course, that code does not behave as they expected...UGH!

Even within a cfquery/cfoutput/cfloop with a query attribute I recommend scoping for clarity. Additionally, there is no reason cfouput/cfloop cannot be used inside a cfc/object.

Of course, there are occasions for very simple output I might drop the 'Variables' scope when outside a CFC for very basic output that has no other scopes mixed in the page, but I find this is rare.

For me it is just simpler to always be in the habit of correctly scoping your values.

I.e.:

<cfprocresult name="Local.qExtern">

and then:

<cfoutput query='Local.qExtern'> #Local.qExtern.szNameFirst# #Local.qExtern.szNameLast# </cfoutput>

To summarize, I posit 3 reasons for leaning towards always scoping except in the most simple of cases:

<strong>CLARITY & MAINTENANCE</strong>

Clarity for the next programmer and for yourself six months later looking at a bit of code is MUCH more valuable than the few keystrokes you save by not scoping. This is particularly true if your code is more complex than simply dumping a query while looping it and other logic or variables/values become interspersed in your output.

<strong>BLEEDING & LOCKING</strong>

Additionally, when in a CFC and using improperly scoped vars, you certainly run the risk of variable bleeding especially with the default scope going to the protected scope instead of the local scope. I have also seen severe performance degradation when multiple methods* and/or singleton methods access a common (protected) scope and need to lock that variable from the waiting methods/request that are also trying to use that name space (oh, yeah, and BLEEDING).

<strong>SPEED:</strong>

Lastly, I would suspect, as in JS and other languages, a scoped variable is more quickly found by the CF engine than an unscoped variable even if in the default scope.

*Footnote: I wanted to clarify the cases where this occurs. Poorly scoped singletons (utility classes cached to the Application, for instance) are the most likely case to cause unintended protected scope variable locking which can slow your system/request queue. Multiple methods using the same variable within the same request are cases where an un-varred variable is treated like a local scoped variable (recursive functions, methods calling other helper methods in the same object) and will typically result in bleeding (unintended re-use/manipulation of the same variable resulting in unexpected outcomes).

Recommend