When doing a Query of Query using tag context you see:

<cfquery name="local.myQuery" dbtype="query">
SELECT *
FROM [local].myOtherQuery
WHERE Col1 = 'Chris'
</cfquery>

You would expect you could just do the same in CFScript under ColdFusion 9:

local.queryService = new query();
local.queryService.setName("myQuery");
local.queryService.setDBType("query");
local.objQueryResult = local.queryService.execute(sql="SELECT [Value] FROM [local].myOtherQuery WHERE Col1='Chris'");
local.queryResult= local.objQueryResult.getResult();

However this will produce an error:
coldfusion.sql.imq.imqException: Query Of Queries runtime error.
Table named local.myOtherQuery was not found in memory. The name is misspelled or the table is not defined.

Thanks to Jared Rypka-Hauer, I found a resolution to this lame error by using the setAttributes method:

local.queryService = new query();
local.queryService.setName("myQuery");
local.queryService.setDBType("query");
local.queryService.setAttributes(sourceQuery=local.myOtherQuery);
local.objQueryResult = local.queryService.execute(sql="SELECT [Value] FROM sourceQuery WHERE Col1='Chris'");
local.queryResult = local.objQueryResult.getResult();

This must be done because local.myOtherQuery is out of context at least until they release closures for ColdFusion.

If you would like to see the inner-works of this visit:
\CustomTags\com\adobe\coldfusion\query.cfc in your ColdFusion 9 install directory.

Update 7/7/2011:
Here’s an excellent blog from Ben Nadel also describing a solution to this issue:
http://www.bennadel.com/blog/2224-Performing-Query-Of-Queries-Using-ColdFusion-9-s-Query-cfc-Component.htm

Recently I had an issue with a variable that I needed to take a look into. Many times I just mail myself a Cfdump of the variable. Today I tried it completely Cfscript-wise and failed with a “Method writeDump with 1 arguments is not in class coldfusion.runtime.CFComponent.” error. This is what I tried:

local.testVar = 'test';

local.mailerService = new mail();
local.mailerService.setTo('bogus@domain.xyz');
local.mailerService.setFrom('bogus@domain.xyz');
local.mailerService.setSubject('Heres your var dump');
local.mailerService.setType('html');
local.mailerService.send(body=writeDump(local.testVar));

I even tried writing it to a variable (assuming this would fail):

local.testVar = 'test';
local.testDump = writeDump(local.testVar);

local.mailerService = new mail();
local.mailerService.setTo('bogus@domain.xyz');
local.mailerService.setFrom('bogus@domain.xyz');
local.mailerService.setSubject('Heres your var dump');
local.mailerService.setType('html');
local.mailerService.send(body=local.testDump);

But that just failed with the same error on the second line above.

I really couldn’t find anything on this in Google, but after manipulating my keywords for awhile, I ran across a blog with a code sample that seems to accomplish what I needed. The key was to run “savecontent” as save the contents off into a variable:

local.testVar = 'test';
savecontent variable="local.testDump" {
writeDump(local.testVar)
}

local.mailerService = new mail();
local.mailerService.setTo('bogus@domain.xyz');
local.mailerService.setFrom('bogus@domain.xyz');
local.mailerService.setSubject('Heres your var dump');
local.mailerService.setType('html');
local.mailerService.send(body=local.testDump);

Now before you say why would I dump a string, I was actually trying to dump a query result.

On a side-note, feel free to come to the Nebraska ColdFusion Users Group tonight and learn neat things like this!

If you’ve encountered an error “Query Of Queries syntax error. Encountered “local.” while running a Query of a Query in ColdFusion, thanks to Ben Nadel I have the solution.

<cfquery name="local.myQuery" dbtype="query">
SELECT [value]
FROM local.myQueryResults
WHERE [name] = 'Chris'
</cfquery>

Running the above code will error out due to “local” being a reserved keyword in SQL. The solution is to wrap “local” in brackets like “[local]”.

<cfquery name="local.myQuery" dbtype="query">
SELECT [value]
FROM [local].myQueryResults
WHERE [name] = 'Chris'
</cfquery>

Many of us are familiar with using:

SELECT @@IDENTITY AS ID

or in later versions of MSSQL:

SELECT SCOPE_IDENTITY() AS ID

in your SQL statement to return the last identity value that you just inserted into a SQL Table.

In ColdFusion 8 you could also use:

<cfquery datasource="#DSN#" result="myResult">
INSERT INTO MyTable (col1)
VALUES ('col1')
</cfquery>

<cfoutput>Inserted ID is: #myResult["IDENTITYCOL"]#</cfoutput>

(Use: IDENTITYCOL for SQL Server, ROWID for Oracle (is rowid not primary key), SYB_IDENTITY for Sybase, SERIAL_COL for Informix, GENERATED_KEY for MySQL)

However in ColdFusion 9 they standardized the variable name to GENERATEDKEY for all SQL environments.

<cfquery datasource="#DSN#" result="myResult">
INSERT INTO MyTable (col1)
VALUES ('col1')
</cfquery>

<!--- Tag based output --->
<cfoutput>Inserted ID is: #myResult["GENERATEDKEY"]#</cfoutput>
<!--- Script based output --->
insertedID = myResult.getPrefix().generatedkey

A good rule of thumb inside a ColdFusion Component (CFC) is to set BOTH the output attributes of cfcomponent and cffunction to false.

<cfcomponent output="false">
    <cffunction name="foo" output="false">
    </cffunction>
</cfcomponent>

Normally I would think that the cffunction tag would inherit the output attribute and set its output attribute to false as well. However this is not the case.

The output attribute for cfcomponent only controls output in the constructor area, or everything outside of the functions. The output attribute for a function controls output only inside that function.

The other point to be aware of is if the output attribute is on, then any expression inside the pound(#) sign will be evaluated. If the output attribute is set to false then any expression inside a pound(#) sign will not be evaluated unless surrounded by a <cfoutput> tag. Rarely is there a need to use the pound(#) signs inside a function anyway unless it’s being evaluated in an attribute such as in <cfmail> or <cfswitch>. (See Mark Kruger’s blog on this)

Thanks to Ray Camden and O’Reilly’s Web Devcenter for enlightening me on this one.

Here’s another related article dealing with memory issues regarding use of the output attribute: http://wiki.mxunit.org/display/default/How+cfcomponent+output%3Dtrue++can+affect+memory+consumption

Many times you need to process textarea field content in ColdFusion and add HTML line breaks so it actually displays correctly on screen or in an email. An easy way to accomplish this is to replace the character codes 10 and 13 with <br>:

Replace(textVar, "#Chr(10)##Chr(13)#", "<br>", "all")

This code takes:

Line 1
Line 2

Line 3

And turns it into:

Line 1<br>
Line 2<br>
<br>
Line 3

I just installed varScoper 1.30 CF Builder (version 1) extension on my machine, but found a few issues getting it to run.

1. Make sure you put the varScoper folder in your Document Root you defined in your Server Settings. On mine this is the directory for my localhost.
2. Modify the cfbuilder.cfm file, in the handlers directory, and add:

   <cfsetting showdebugoutput="false">
   

   to the beginning of the file. You may need to open NotePad with Administrative privileges I found out for some reason.

3. It appears the “Description” window in the “Extensions” tab is broken for this extension. There is no fix as of right now.

Thanks to Ray Camden for the help on this.

Thanks to Ray Camden, I learned something new today that was of great help.

Inside the Application.cfc, I always assumed the “this” and “application” scopes where the same. This is incorrect.

“Application” scope will only store name/value pairs. It has no impact on how the application behaves on the server level (built-in behavior).

<cfset application.name = value>

While “this” scope is used to change the behavior of the application itself.  For example: defining custom tag paths and the common datasource name.

<cfset this.name= value>

For the first time today I used ColdFusion’s new ternary operator. I learned about it last week as a alternative to the inline-IF (IIF()). No longer do I need to remember the quarks about the DE operator required in IIF.

A ternary operator takes three arguments: ((condition) ? trueStatement : falseStatement)

Example:

<tr class="#(currentrow mod 2) ? 'lightRow'  : 'darkRow'#">

This can also be done using inline if:

<tr class="#IIF(currentrow mod 2, DE('lightRow'), DE('darkRow')#">

Or using cfif:

<tr class="<cfif currentrow mod 2>lightRow<cfelse>darkRow</cfif>">

As you can see, you can use a simple inline operator without having to worry about IIF quirks or drawn out conditional statements. Remember this is introduced in ColdFusion 9.

Recently I was assigned a new client that uses mostly ColdFusion 9 for their applications. In the past the majority of my clients have not taken the opportunity to upgrade from version 8, and there has not really been much reason to that I’ve run into as well. However, this opportunity creates the needed effort to make sure I know the differences between 8 and 9, such as var scoping.

As you probably know the variables scope in a CFC is private to that CFC. In other words the object calling that component may not access this scope’s values until it is returned to it. This scope can be set using the “variables” prefix to the variable name. If you skip the scope, it will be assigned to the variables scope. The following code sets the same variable and will persist between calls to each method the component contains.

<cffunction>
    <cfset variables.var1 = "Example 1">
    <cfset var1 = "Example 2">
    <!--- resulting in both variables.var1 and var1 values set to "Example 2" --->
</cffunction>

Normally you would want to declare variables private to each method so that your values are never overwritten by other process. This is normally done by using the var keyword. However in ColdFusion 9 you may use the local scope instead. This local scope does not persist between calls to the component’s methods. For example the following two lines work the same (the var keyword would not be required in this instance if using CF9):

<cffunction>
    <cfset var var1="Example 1">
    <cfset local.var1="Example 2">
    <cfreturn local.var1> <!--- Returns "Example 2", returning var1 would result in an error as it does not exist --->
</cffunction>

The question now becomes “What if I var scoped a local struct to use in previous versions of ColdFusion (var local={})?” The answer is you are good to go. ColdFusion 9 will just create a struct named “local” in the local scope (local.local).  So if you are coding for CF9 there is no reason to var scope a local struct as you would just be creating an unused struct. Instead use the already existent local scope. But you don’t need to go back and re-factor your code to remove all the local var scopes as an empty struct will not cause any harm unless you where counting on local.local to be there in the past. The following example is backwards compatible in CF9.

<cffunction>
    <cfset var local = {}> <!--- In CF9 this will result in local.local, so leave this out --->
    <cfset local.var1 = "Example1">
    <cfreturn local.var1>
</cffunction>