Andy Jarrett // Code. Develop. Create.

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:

queryService = new query();
queryService.setDatasource("cfdocexamples");
queryService.setName("GetParks");
queryService.addParam(name="state",value="MD",cfsqltype="VARCHAR");
queryService.setSQL(
"SELECT PARKNAME, REGION, STATE
FROM Parks WHERE STATE = :state
ORDER BY ParkName, State ");
result = queryService.execute();
writeDump( result.getResult() );
After staring at my code for a while and realising that doing that wouldn't fix anything Ibutchered played around with the syntax and found that after the WHERE clause you need to keep your SQL on one line. Makingthe following change to setSQL() fixed the problem:queryService.setSQL("SELECT PARKNAME, REGION, STATEFROM 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 aroundwith 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


I’m here, learning and working away. If you liked this content and want to keep me going, consider buying me a coffee. Your support keeps this site running and the coffee brewing! ☕️