Andy Jarrett: Query scripting bug in Coldfusion 9

Loading Twitter

Query scripting bug in Coldfusion 9

I had a weird error this morning.

Named Sql parameter 'state ORDER' used in the query not found in the queryparams

Below is the code I had and it look fine to me:

view plain print about

1<cfscript>
2queryService = new query();
3queryService.setDatasource("cfdocexamples");
4queryService.setName("GetParks");
5queryService.addParam(name="state",value="MD",cfsqltype="VARCHAR");
6queryService.setSQL(
7"SELECT PARKNAME, REGION, STATE
8FROM Parks WHERE STATE = :state
9ORDER BY ParkName, State ");
10result = queryService.execute();
11writeDump( result.getResult() );
12</cfscript>

After staring at my code for a while and realising that doing that wouldn't fix anything I butchered played around with the syntax and found that after the WHERE clause you need to keep your SQL on one line. Making the following change to setSQL() fixed the problem:

view plain print about

1queryService.setSQL(
2"SELECT PARKNAME, REGION, STATE
3FROM Parks WHERE STATE = :state ORDER BY ParkName, State ");

I can't seem to find a reference as to how your SQL should be formatted but it seems the new line plays around with the deliminators and the way the named params are found.

I'm posting this now but will hopefully later find time to look in on query.cfc in the com/adobe/coldfusion folder under customtags to find the actual reason

Comments (1) | Print | Send | 1730 Views

If you like what you see on the website and/or this post has helped you out in some way please consider donating to help keep me in beer vodka. The donations are made through Paypal, which accepts almost any credit card or eCheck.

(Comment Moderation is enabled. Your comment will not appear until approved.)

[Add Comment] [Subscribe to Comments]

Posted By Rex @ 6/28/10 9:20 PM

I've seen this before. When you have a variable in the SQL, you need a space after it. This should work:

queryService.setSQL(
"SELECT PARKNAME, REGION, STATE
FROM Parks WHERE STATE = :state<space>
ORDER BY ParkName, State ");

Having the FROM and ORDER BY on one line works because of this space.

[Add Comment]