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:
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:
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>
1queryService.setSQL(
2"SELECT PARKNAME, REGION, STATE
3FROM Parks WHERE STATE = :state ORDER BY ParkName, State ");
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
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.
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.
Thanks the space works well.
S.
S.
