Reserved words in QoQ

Today, a user of the Henkel CMS reported a bug in a handler that I wrote. Funny thing – it worked and still works on all CF5 servers we’re running, besides one CFMX that hosts sections needing the UTF-8 charset.

So, what happened?The error occured in the edit handler for the random image object on the Henkel Gateway pages (have a look at henkel.com – the changing image in the upper left corner). To prevent the users from adding too large images, I perform a filesize check using CFDIRECTORY.
I personally like the method of getting the needed information via QoQ, so here’s the code I used:

<cfset theArrayLen = arrayLen(attributes.images)>
<cfset caller.filesizes = arrayNew(1)>

<cfif theArrayLen>
<cfdirectory action=”LIST” directory=”#blahblah#/images” name=”imagedir”>

<!— get the images’ filesizes —>
<cfloop from=”1″ to=”#theArrayLen#” index=”img”>
<cfquery dbtype=”query” name=”getfilesize”>
select size from imagedir
where upper(name) = ‘#ucase(attributes.images[img])#’
</cfquery>
<cfset caller.filesizes[img] = getfilesize.thesize>
</cfloop>

<!— now that we have the directory contents, pass them to the caller for further usage—>
<cfset caller.imageDirContent = imagedir>
</cfif>

CFMX complained about the select “SIZE”, as “SIZE” is a reserved word in SQL. Unfortunately, CFDIRECTORY names its size column like that – there’s no chance to change the name. So I tried the following…

-Using a SQL alias
select size as theSize from imagedir … didn’t work

-Using quotes and / or brackets around “size”
select “size” as theSize from imagedir …didn’t work
select [size]… …guess what – didn’t work

The solution is simply to access the column by its number, as you would do it in an “order by” clause, e.g.

select blah as myBlah, blah2 as megaBlah
from theTable
order by 2

To sum it up:

<cfquery dbtype=”query” name=”getfilesize”>
select 5 as theSize from imagedir
where upper(name) = ‘#ucase(attributes.images[img])#’
</cfquery>

That actually works.
According to the SQL standards, CFMX does everthing right – it’s some kind of careless behaviour of CF5’s QoQ implementation.

Finally, here’s a list of reserverd words in QoQ:
<a href=”http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/express4.htm” target=”_blank”>Reserverd words in QoQ</a>