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.
SELECT staff_id,
col1 AS col1
col2 as col2
…
[position] as [position]—
FROM qTmp
WHERE col1 = …
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
is this a bug in the underlying levels of the Lucee engine and Java?
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?
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
On Mar 25, 2015, at 5:58 PM, Steve <@Steve1> 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 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.
SELECT staff_id,
col1 AS col1
col2 as col2
…
[position] as [position]
FROM qTmp
WHERE col1 = …
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
is this a bug in the underlying levels of the Lucee engine and Java?
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
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.
SteveOn Thursday, March 26, 2015 at 11:25:06 AM UTC+13, Steven Durette wrote:
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
On Mar 25, 2015, at 5:58 PM, Steve <st…@ginja-ninja.co.nz <javascript:>> 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 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.
SELECT staff_id,
col1 AS col1
col2 as col2
…
[position] as [position]
FROM qTmp
WHERE col1 = …
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
is this a bug in the underlying levels of the Lucee engine and Java?
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?
All of the following seem to work fine for me with Lucee 4.5.1, where
qTmp is either from MSSQL or MYSQL.
SELECT [position] AS [position] FROM qTmp
SELECT [position] FROM qTmp
SELECT position FROM qTmp
Have you tried stripping out the rest of your SELECT statement and
just querying that column?On 25 March 2015 at 21:58, Steve <@Steve1> 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 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.
SELECT staff_id,
col1 AS col1
col2 as col2
…
[position] as [position]
FROM qTmp
WHERE col1 = …
And generates an error "Unexpected token: POSITION in statement [ SELECT…
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.)
JochemOn Mar 25, 2015 10:58 PM, “Steve” wrote:
We are currently trying to migrate our website from Coldfusion to work on
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.
SELECT staff_id,
job_title AS job_title
FROM qTmp
GROUP BY staff_id, job_title
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.
SteveOn Friday, March 27, 2015 at 7:35:55 PM UTC+13, Jochem van Dieten wrote:
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.)