Credit for this find goes to Andrew Scott and his blog entry here. Essentially, he noticed that when using line breaks in a script based query along with bound parameters, he would get an error. Like so:
queryService = new query();
queryService.setDatasource("cfartgallery");
queryService.setSQL("
select artid, artname
from art
where mediaid =:mediaid
order by artid
");
queryService.addParam(name="mediaid", value=1, cfsqltype="CF_SQL_BIT");
result = queryService.execute();
</cfscript>
I wasn't able to replicate this until he pointed out that my test version had spaces in front of each line, not tabs. As soon as I switched to tabs, I got the error as well.
I reminded Andrew that the script based components are not encrypted. That means you can dig into the code and see what's going on. Out of all the script based components, the query one is probably the most complex. It does a lot of parsing on the SQL string. In there I found the bug. Part of the code that parses the string didn't check for tabs along with other white space.
I've written a fix for this and sent it along to ColdFusion Engineering. I've attached the CFC to this blog entry, but I'd recommend simply using spaces instead of tabs. No need to patch your server to be different from release for something so small.
Archived Comments
We have seen this as well in HQL. To solve it I just hit space before entering down.
Another fix is to have your where clause all on the one line.
Also, the param variable is it supposed to have a space before the variable?
": mediaid" should it be ":mediaid"?
I saw that in Andrew's code and was convinced it was the issue, but it is not. But I do think it is bad formatting. (Well, maybe not bad, I don't like it though. :P ) Going to edit it now.
Agreed it is bad and it was an oversight on my part, normally I would type = :paramName
I think Ben Nadel also found this bug a couple of years back :)
http://www.bennadel.com/blo...
Justin I actually raised it as a bug with Adobe during the pre-release, but as I have mainly been doing ORM work on CF9 I had forgotten all about it.
I guess it was one of those things that Adobe just didn't think was important enough to fix at the time of the beta program, like another bug I am having with ORM event handlers and shared hosting that still exists.
To be fair to Adobe (ok, I wear my bias proudly now ;) this is a pretty easy issue to get around. Given limited time/budget to fix bugs, I can see skipping this one.
Agreed Ray, even then I did what I do now and keep the where clause all on the one line. Just now and then I do forget...
Maybe they figured that everyone still fails to parameterise their queries so it wouldn't be much of an issue :) (Joke!)