Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at BFusion / BFLEX 2009 (Bloomington, Indiana) with: Peter Farrell and Matt Woodward

LOCAL Variables Scope Conflicts With ColdFusion Query of Queries

By Ben Nadel on
Tags: ColdFusion, SQL

I just helped someone on the House of Fusion CF-Talk list who was having trouble with his ColdFusion query of queries. Like some of us do, he was declaring a local variable, LOCAL, in a function and then declaring variables within it. This is done so that only one variable (LOCAL) has to be "var'ed" to enable many variables to be considered local to the function's memory space:

  • // Declare local scope.
  • var LOCAL = StructNew();
  •  
  • // These are now all local.
  • LOCAL.Foo = 1;
  • LOCAL.Bar = QueryNew( "id" );
  • LOCAL.Test = StructNew();

His problem was that he was doing a query of queries using a query stored in the LOCAL pseudo scope:

  • <cfquery name="qTest" dbtype="query">
  • SELECT
  • id,
  • name
  • FROM
  • LOCAL.Data
  • </cfquery>

This throws the error:

Query Of Queries syntax error.
Encountered "LOCAL.

This can be frustrating because if you change the name LOCAL to something like LOCAL2, it works just fine. The problem, while frustrating, has a very simple answer: LOCAL is a reserved word in SQL and ColdFusion Query of Query SQL. The solution: use the [ ] notation:

  • <cfquery name="qTest" dbtype="query">
  • SELECT
  • id,
  • name
  • FROM
  • [LOCAL].Data
  • </cfquery>


Looking For A New Job?

100% of job board revenue is donated to Kiva. Loans that change livesFind out more »

Reader Comments

You did NOT just save me hours of grief. :-) found your blog post on google and wanted to say thanks. I always ended up var'ing another variable to use in my QofQ. Thanks for the insight!

Tim

Reply to this Comment

Tim,

No worries dude. Please let me know if there IS anything I can do to save you hours of grief?

Reply to this Comment

Using structure in Query of queries is buggy to say the least:
Using your example as a reference.. the following fails. I can't find anyway in which to get this to work, so must be a bug

<cfscript>
var LOCAL = structnew();
LOCAL.Data = QueryNew( "id,name" );
LOCAL.Data2 = QueryNew( "id,name" );
LOCAL.Test = StructNew();
</cfscript>

<cfquery name="qTest" dbtype="query">
SELECT
id,
name
FROM
[LOCAL].Data ,[LOCAL].Data2
where
[LOCAL].Data.id =1
</cfquery>

Reply to this Comment

Phil,

The problem is in the WHERE clause. For some reason, it cannot handle the [LOCAL].Data.id line. However, if you take that away, and just have id = 1, it will parse correctly. However, it then throws an ambiguous column name (since neither ID nor NAME are prefixed and are available in both join tables).

And since you can't alias table names, you might be out of luck with this example. Sorry.

Reply to this Comment

would you guys please reduce using query of queries so much, and perhaps put a bit more time into sharpenning your sql join skills.

Reply to this Comment

@Anonymous,

Can you point out a place where you feel that a JOIN would be more appropriate than a query of queries?

Reply to this Comment

I ran into this issue recently and decided the quick fix at the time was to simply rename my LOCAL variable to LocalScope. Renaming LOCAL to fix a QoQ seemed odd to me at the time and it has been bugging me because I have not had the time to work on it.

I was about to ask you this very question, but I tried a quick search and there was the answer on the first result. I should have tried that earlier...

It's nice to be able to keep using LOCAL without worrying about query of queries inside functions. =)

Reply to this Comment

I love the idea of scoping all variables in a function inside a single "var" struct. However, if there's an evolving standard, then using a variable name without a naming conflict makes far more sense. If the name "local" creates a conflict in QoQ, why not change the variable name, not use a workaround.

The first two that sprung to mind were "func" and "my". They're both descriptive (enough) and they're short, which is nice considering the scope is prepended to all the variables local to the function.

Any thoughts on this? I guess it just bothers me that the "accepted" practice is one that includes an unnecessary bump in the road.

Reply to this Comment

@Adam,

At CFUNITED 2008, Adam Lehman actually said that in ColdFusion 9, functions would have an explicit LOCAL scope. So, it looks like they are endorsing this idea of "local" even though it causes issues in Query of Queries.

Reply to this Comment

What about the highly descriptive term localVar? It's only eight characters, 1 more than REQUEST, 1 less than VARIABLES and 3 less than APPLICATION

<cfset var localVar = structNew()>

Reply to this Comment

Since query of queries is so limited with functionallity and it takes a performance hit on your page, I opted to exclude it from my coding.
Instead sharpenning up my SQL has really taken away the need for QoQ. For example, get the rows and columns you'd like them on the first call to the SQL server. I know that is a long term fix but it's better in the long run.

Reply to this Comment

@Webexpertise,

Query of queries definitely do NOT run as fast as native SQL calls to the database. However, they can work quite synthetically. For example, let's say you have to return a report of Students and last 10 test scores. To do this you could start out:

- Return all students
- Return all recent test scores

Then, loop over the students and for each students, do a query of queries on the recent test scores to get the test scores for that given student.

This allows us to leverage the goodness of SQL when necessary, but we can then use query of queries to leverage the existing query in the page's memory space.

Reply to this Comment

Point taken, but in that case I would do one single SQL statment:

select top 50 student, score
from studentscores
order by id desc

that will give me a list of 50 students ordered from newest to lowest.

so then I can show them like this:
<cfoutput>

<cfif currentrecord lte 10>
<li><br>#student# #Score#</br></li>
<cfelse>
<li>#student# #Score#</li>
</cfoutput>

This will bold the top 10 last scores as well as give you the full list. And i didn't need the QoQ. and only one call to sql.

----

On the other hand if I wanted to add sorting to that SQL result by the provided columns in the query, it does make sense to use QoQ without a trip to the sql server.

Sorry but looping over a cfquery with a cfloop and on each iteration calling a query of queries to calculate the students test score looks like more processing on my server. And it looks like a workaround for something. Not to mention that if your original query was a large amount of rows it really takes a hit on performance.

One could then say: if performance is not at question then use QoQ.

I used to use QoQ a lot, then I was asked to do really complex reports requiring calculations based on many tables is when I realized that spending my time learning to make QoQ work for me was not as efficient time spent than learning sql to get the same results. (and faster)
But that's just my opinion. :)

Reply to this Comment

@Webexpertise,

The problem with that way is that in many reports that I have to run, I am getting a lot of additional data such as school contact information and district information and principal information. With too many joins, there gets to be a lot of data pulled down for each record that is not necessarily worth the transfer time.

I am not saying one way is better - I think its more of an art than a science to figuring out which way will be faster.

Most of where I use this is in reporting where time of execution is not necessarily mission critical.

Reply to this Comment

Ben,

Art vs. science, interesting comparison it reminds me of MAC vs PC.

I know you are a master at coldfusion, I do really appreciate all the articles you have ever provided, in many cases it has saved my week from falling short and I love your site for that. But let me quote you with all due respect on this: "With too many joins, there gets to be a lot of data pulled down for each record" I don't exactly see it that way, what I would do in that case is:

select top 50 students.firstname,students.lastname, school.name,school.location,ss.score
from students
join school on student.schoolid=school.id
join studentscores ss
order by ss.id desc

That outputs only 5 columns and 50 rows. The important note is not to use "select * from" when using joins for multiple tables therefore avoiding too much data out.

Usually what I do is create a function in a component i.e. students.cfc that returns this data in a query ready to use for any template or component. Having that in a component makes it even faster since it is already compiled code.

One good scenario where you'd be better off using joins and more specific SQL vs. QoQ is lets say you land on a job that used to have a full department of IT employees, such as a DBA and a couple of CF developers but now for some strange reason, perhaps the company was in financial trouble, now they only have you to do minor maintenance and some IT support. You will find out that a lot of the tables in SQL are normalized with 1NF or 2NF or so that is the job of the DBA, it will be very intuitive for someone who has been using joins to get what they need in their queries, vs. the programmer who relies in QoQ for everything, and will have a nightmare trying to match id's of the perhaps 4 tables needed to be called for a normalized database in any given report.

Reply to this Comment

@Webexpertise,

I am not sure where you are getting this "top 50" from? This is a report - it might have thousands of students that need to have scores returned. Sure it might only return 5 columns and 50 rows, but your SQL does not address the actual gesture of the sample report.

Reply to this Comment

@Webexpertise,

Using a limit or top on the SQL is nice; however, when you are reporting, this is generally not possible unless the report concerns a TOP X percentage of some sort. Sometimes, a client just wants to see all 8 thousands records :(

Reply to this Comment

True, but you got to limit it somewhere, what if its 700 billion rows in that report. You could cover the world on paper a few times, we will freeze to death from lack of heat from the sun catastrophic loss of species while making the world a lonelier and less interesting place. I would think twice before coding that report.

Reply to this Comment

@Webexpertise,

Trust me, I have already campaigned heavily *against* the reports that they want now.... I think we're on the same page :)

Reply to this Comment

I had a similar problem back in 07, not quite the same result, but the same cause. Here is my scenario in case it helps anyone.

http://russ.michaels.me.uk/index.cfm/2007/7/3/Query-of-Queries-quirks

Reply to this Comment

@Russ,

It looks like you are using "locals" in your example, rather than "local", to get around the reserved word issue, correct?

I think some of the issues you were experiencing with the comparison operators been since been resolved, thankfully!

Reply to this Comment

Hey Ben,

I recently started "following" you on Twitter. This blog finally helped me answer my question of why I always saw your "local" structs in other blogs (although I guess I could have easily figured the reason behind it). That struct makes sense, and you are less likely to need one of those "var" validator tools because there is less var variables to maintain.

Reply to this Comment

I was wondering why my comment was deleted? I posted a very elegant solution to the problem allowing local variables to be declared anywhere in a CF8 function using syntax similar to the var declaration. Was it because I expounded on it's usefulness, giving multiple examples?

I think this is very important as it is not documented anywhere on the web. It took me hours to work out the solution myself.

Please don't delete it, so others can benefit. I've removed the extraneous examples and include only the function and sample usage.

The function:

  • function var(somevar)
  • {
  • var oldlocal="";
  • getPageContext().popFunctionLocalScope();//Get rid of current local scope.
  • oldlocal=getPageContext().getActiveFunctionLocalScope();//Get old local scope.
  • if (ArrayLen(Arguments) GT 1)
  • {//Inject with value passed in.
  • oldlocal["#somevar#"]=Arguments[2];
  • }
  • else
  • {//Inject with default of empty string.
  • oldlocal["#somevar#"]=""; }
  • getPageContext().pushNewFunctionLocalScope();//Put a local scope for CF to pop when this function exits.
  • }

Usage:

  • function somefunc()
  • {
  • var("test");//Defaults to empty string.
  • var("test1",1);//Assign simple value.
  • for (var("i",1); i LTE maxval; i=i+1)
  • {
  • dosomething();
  • }
  • }

Reply to this Comment

@Jdyer,

Sorry if it was deleted. Some comments get flagged as spam based on a lot of different criteria (name, link, comment content, relevancy, etc.) - there are definitely false positives.

This is actually a very interesting approach. I never thought about toying with the active local scopes. I can actually see this being useful in other situations (such as when dealing with threads).

Reply to this Comment

One thing I've noticed when playing around with active function local scopes and popping them is that it can mess with the stack trace.

If an error occurs or if you purposely throw an error and you have popped function local scopes (in other words, it happens before you have restored the function local scope) then the stack trace gets screwed up indicating the error occurred on the line of the top level function call. To illustrate:

  • function somefunc()
  • {
  • someotherfunc();
  • }
  • function someotherfunc()
  • {
  • getPageContext().popFunctionLocalScope();
  • //error happens here
  • getPageContext().pushNewFunctionLocalScope();
  • }
  • somefunc();//Error will indicate this line.

To fix this, you should set a flag (or a counting semaphore if you plan recursion) in a shared scope such as Request so in the event of an error you can push some new FLSs to prevent this problem.

Reply to this Comment

Here is an update of the function with a counting semaphore in the Request scope and an error thrown if somevar is not a string.

  • //Allows creation of a local variable anywhere in a function.
  • function var(somevar)
  • {
  • var oldlocal="";
  • getPageContext().popFunctionLocalScope();//Get rid of new local scope.
  • if (IsDefined("Request.poppedFLSCount"))
  • { //We have to keep track of how many are popped in case of errors.
  • ++Request.poppedFLSCount;
  • }
  • else
  • {
  • Request.poppedFLSCount=1;
  • }
  • oldlocal=getPageContext().getActiveFunctionLocalScope();//Get old (now current) local scope.
  • if (IsValid("string",somevar))
  • {
  • if (ArrayLen(Arguments) GT 1)
  • {
  • oldlocal["#somevar#"]=Arguments[2];//Create new local variable with passed in value.
  • }
  • else
  • {
  • oldlocal["#somevar#"]="";//Create new local variable with default of an empty string.
  • }
  • }
  • else
  • {
  • throw(type="invalidParameter",message="Invalid Parameter",detail="The parameter somevar must be a string containing the name of the variable to be locally scoped.");
  • }
  • getPageContext().pushNewFunctionLocalScope();//Push another local scope for CF to pop.
  • --Request.poppedFLSCount;//Reduce count as we have created a new one.
  • }

Reply to this Comment

Here is the throw function so you can see how to correct the problem.

  • <cffunction name="throw" output="false" returnType="void" hint="CFML Throw wrapper">
  • <cfargument name="file" type="string" required="false" default="Unknown" hint="File throw is located in.">
  • <cfargument name="line" type="string" required="false" default="Unknown" hint="Line throw is located on.">
  • <cfargument name="func" type="string" required="false" default="Unknown" hint="Function throw is located in.">
  • <cfargument name="type" type="string" required="false" default="Application" hint="Type for Exception">
  • <cfargument name="message" type="string" required="false" default="" hint="Message for Exception">
  • <cfargument name="detail" type="string" required="false" default="" hint="Detail for Exception">
  • <cfargument name="errorCode" type="string" required="false" default="" hint="Error Code for Exception">
  • <cfargument name="extendedInfo" type="string" required="false" default="" hint="Extended Info for Exception">
  • <cfargument name="object" type="any" hint="Object for Exception">
  • <cfif IsDefined("Request.poppedFLSCount")>
  • <!--- We have to restore these for the stack trace. --->
  • <cfloop condition="Request.poppedFLSCount GT 0">
  • <cfset getPageContext().pushNewFunctionLocalScope()>
  • <cfset Request.poppedFLSCount=Request.poppedFLSCount-1>
  • </cfloop>
  • </cfif>
  • <cfset var("functxt")>
  • <cfif file EQ "Unknown" OR line EQ "Unknown" OR func EQ "Unknown">
  • <cfif type NEQ "getStackTrace">
  • <cfset caller=GetCallerStack()>
  • <cfif ArrayLen(caller) GTE 1>
  • <cfset file=caller[1]["template"]>
  • <cfset line=caller[1]["line"]>
  • <cfset functxt='in function #caller[1]["function"]# '>
  • </cfif>
  • <cfif file EQ "Unknown"><!--- Was thrown from outside a function. --->
  • <cfset caller=GetErrorLineAndFile()>
  • <cfset file=caller["template"]>
  • <cfset line=caller["line"]>
  • </cfif>
  • </cfif>
  • </cfif>
  • <cfif not isDefined("object")>
  • <cfthrow type="#type#" message="Error: #type#<BR/><BR/>#message#" detail="#detail#<BR/>Error occured on line #line# #functxt#of #file#" errorCode="#errorCode#" extendedInfo="#extendedInfo#">
  • <cfelse>
  • <cfthrow object="#object#">
  • </cfif>
  • </cffunction>

If anyone needs the getCallerStack function to make this all work, just say so and I'll add it to the conversation.

Reply to this Comment

If you are trying to debug some code and are not throwing your own error, then wrap this around code you suspect of creating the error.

  • try
  • {
  • //your code
  • }
  • catch(Any excpt)
  • {
  • if (Is(Defined(Request.poppedFLSCount))
  • {
  • while (Request.poppedFLSCount GT 0)
  • {
  • getPageContext().pushNewFunctionLocalScope();
  • --Request.poppedFLSCount;
  • }
  • }
  • rethrow();//wrapper for cfrethrow tag
  • }

You could also put the if and while loop in a function called restoreAllFunctionLocalScopes for convenience.

Reply to this Comment

I never cease to be amazed that almost EVERY random CF issue I come across lands me on your site.
Thank you for documenting your findings for the world.

Reply to this Comment

@Curt Self - Yup... you and the rest of the CF community. Adobe should be paying Ben, I reckon there'd be a lot less CF websites out there without him.

@Ben - Thanks for solving yet another issue. Previously I've been copying a Local variable to an unscoped variable in order to use in QOQ, which just seems ugly and inelegant.

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.