ColdFusion Query Of Queries and local scope

Few days ago I was working with  QueryOfQuery and I faced one situation where I got stucked for few hour . After few Googling I found the silly mistake I was making.

I am just trying to generate the same situation with some sample code below.

<cfset local.qryGetArtists = queryNew("") />
<cfset local.qryGetSelctedArtist = queryNew("") />

<cfquery name="local.qryGetArtists" datasource="cfartgallery">
    SELECT ARTISTID, FIRSTNAME, LASTNAME, EMAIL, PHONE FROM ARTISTS
</cfquery>
<cfdump var="#local.qryGetArtists#">

<cfquery name="local.qryGetSelctedArtist" dbtype="query">
    SELECT * FROM local.qryGetArtists WHERE LOWER(LASTNAME) LIKE '%#lCase("Buntel")#%'
</cfquery>
<cfdump var="#local.qryGetSelctedArtist#">

In this code, the first dump give the query Object which contains the list of artist details and after that I am just filtering that query object using Query Of Queries(QoQ) and dumping that result.

For the second dump, I got one big error message:

"Query Of Queries syntax error. Encountered "local"

Where and Why We Got This Error?
The error is due to the local scope as per the error message then why this error. In ColdFusion there are some reserved keywords are there and we can't use that reserve keyword  inside Query Of Queries directly.

This is also mentioned in the ColdFusion Documentation here.

How To Solve This Issue?

The solution is escape the reserve keyword like this:

"SELECT * FROM [local].qryGetArtists WHERE LOWER(LASTNAME) LIKE '%#lCase("Buntel")#%'"


So, the final Code for the Query Of Queries will be like below.

<cfquery name="local.qryGetSelctedArtist" dbtype="query">
    SELECT * FROM [local].qryGetArtists WHERE LOWER(LASTNAME) LIKE '%#lCase("Buntel")#%'
</cfquery>
<cfdump var="#local.qryGetSelctedArtist#">

For more details about Query Of Queries go to the ColdFusion live document. (ColdFusion Query Of Queries)

Bug in script version of cfquery

We generally uses ColdFusion tags in our coding. But sometimes we prefer to use script version of that instead the tag. Also in ColdFusion 9.0.1 have a script version code for almost every tags.

In ColdFusion 9.0.1 the script version of cfquery has a bug as follows.

Let our table name is "USERS(USER_ID, FIRST_NAME, LAST_NAME, isDELETED)", which contains the users information.

If I want to get the data of a single user then I will write.

<cfscript>

variables.q = new query();
variables.q.setdatasource("TrackBug_New");
variables.q.setSql('SELECT
USERID,
FIRST_NAME,
LAST_NAME
FROM
USERS
WHERE
USER_ID = :userID
AND
isDELETED = 0');

//Set value in addParam equivalent to cfqueryparam
variables.q.addParam(name="userID", value="1", CFSQLTYPE="CF_SQL_INT");

//Executes the query and Extracts the result from the query object
variables.qry_getUser = variables.q.execute().getresult();

//To show the result
WriteDump(variables.qry_getUser);

</cfscript>

But unfortunately we will get the error message :

=============================================================
Error Executing Database Query
Parameter 'userID AND' not found in the list of parameters specified
=============================================================


Cause Of Problem:
While setting the variable ":userID" to the query string I have placed a line break after that variable and which is the culprit. If we will place a space character after the variable then it will not create any problem. Again for any other character other than space character will cause the problem, like if we replace tab instead of space then we will also face the same problem.

Moral:
When you are using any variable in the query string then always give a space character after the variable.


For this problem I have just wasted 2-3 hr. So I thought it will help you :).