SQL reserved words in columns names and query of queries

[Steve]

We are currently trying to migrate our website from Coldfusion to work on Lucee and I found an issue where we have used had a column using a SQL reserved word as the name (in this case 'position') inside a query result that is then queried in CFML. We are using square brackets to escape the column name in Coldfusion but Lucee ignores them, some comments online mention this as an issue with railo and I am assuming this bug (if it is one) came with the fork.

Our code is something like this.

<cfset qTmp = functionName(arg)>
<cfquery name="qData" dbtype="query">
  SELECT staff_id,
 col1 AS col1
 col2 as col2
....
[position] as [position]
---
FROM qTmp
WHERE col1 = ...
</cfquery>

And generates an error "Unexpected token: POSITION in statement [ SELECT...

My way of getting around this was to return the query from the function as 'job_title' by setting the query in the function to use

[position] AS job_title

when querying SQL server and updating position in the above code to 'job_title'.

I have two questions

1. is this a bug in the underlying levels of the Lucee engine and Java?
2. I am expecting more issues like this throughout our site, is there a better (or another) way of solving this problem where we don't have to rename the columns in the original query?

Steve

[Steven Durette]

I would guess that is a Lucee/Railo problem because select [position] as [position] is perfectly legal tsql in MS SQL Server and works in ACF as a matter of fact I use that in Railo and it works. Maybe it is something introduced recently. 

Sent from my iPhone

--
You received this message because you are subscribed to the Google Groups "Lucee" group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/b3327da9-3d2f-488b-861f-673c77cbd986%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Steve]

Hi

I have found [position] as [position] is fine when querying the SQL server but when querying a query it generates an error that is causing us grief.

Steve

[Julian Halliwell]

All of the following seem to work fine for me with Lucee 4.5.1, where
qTmp is either from MSSQL or MYSQL.

<cfquery name="qData" dbtype="query">

SELECT [position] AS [position] FROM qTmp
</cfquery>

<cfquery name="qData" dbtype="query">

SELECT [position] FROM qTmp
</cfquery>

<cfquery name="qData" dbtype="query">

SELECT position FROM qTmp
</cfquery>

Have you tried stripping out the rest of your SELECT statement and
just querying that column?

[Jochem van Dieten]

On Mar 25, 2015 10:58 PM, "Steve" wrote:
>
> We are currently trying to migrate our website from Coldfusion to work on Lucee and I found an issue where we have used had a column using a SQL reserved word as the name (in this case 'position') inside a query result that is then queried in CFML. We are using square brackets to escape the column name in Coldfusion

Try using double quotes instead, that is the identifier quote according to the SQL Standard. (And it is wat more portable than that MS-ism.)

Jochem

[Steve]

Hi Thanks to the people who have left comments, I figure I should leave a comment here in case someone has a similar issue. Spent about a couple of hours yesterday playing with this and really struggled to consistently replicate a sepecfic error. Afterwards I have had to put it down to the fact we use a group by function in the query of queries when we have position as a column rather than just the position column as I first mentioned>

More or less for the query of a query I believe if you have the code below it will work but if you swap the usage of job_title for position it fails, it also seemed to mostly work when I removed the group by clause but used position as the column name.

<cfquery name="qData" dbtype="query">

    SELECT staff_id,
        job_title AS job_title                                                                    
    FROM qTmp                        
    GROUP BY staff_id, job_title
</cfquery>

I am running Lucee 4.5.1 on my Windows 7 VM, with the http request being forward to tomcat via AJP proxy module in Apache httpd but doubt any of that should have an impact for this.

Steve