Article

ColdFusion spatial query parse error using CFSCRIPT

I am ambisyntaxtrous.  I think it is a trait unique to (most) ColdFusion developers — a result of CFSCRIPT evolving over time to afford us most of the same functionality as our lovable tag-based syntax.  It started out as a convenience to write less code, not to mention being a bit easier on the eyes… I mean who can’t relate to feeling weird looking at the following?

<cfset arrayAppend(myArray, "something") />
<cfset myCustomFunction(myArray) />

What am I setting in the above?  It is much clearer to write:

<cfscript>
	arrayAppend(myArray, "something");
	myCustomFunction(myArray);
</cfscript>

Still, it’s hard to break old habits, and it has taken some getting used to making the switch.  What really got our gears rolling was moving to the FW/1 framework.  It just felt right to use tag syntax in our views and use script in our controllers, models and services.  The only part of this separation of syntax that felt awkward was querying, the cornerstone of server-side web programming.

Before CF11, we were using the new Query() syntax to execute queries in CFSCRIPT. The usage was definitely awkward but it got the job done, most of the time…

The problem

One of our applications uses a SQL Server spatial query to find all features that are at least partly within a rectangular space, or bounding box.  We use it to figure out what to display on an interactive map that the user can pan and zoom.  The client sends the four corners of the bounding box, and the query looks like this:

DECLARE @boundingbox geometry
SET @boundingbox = geometry::STGeomFromText('POLYGON((#local.bbox_west# #local.bbox_south#,#local.bbox_east#
#local.bbox_south#,#local.bbox_east# #local.bbox_north#,#local.bbox_west# #local.bbox_north#,#local.bbox_west#
#local.bbox_south#))',3857)

SELECT ID, GRIDCODE
FROM FEATURES
WHERE @boundingbox.STIntersects(GEOM) = 1

Using CFQUERY, the above works like a charm. But using CFSCRIPT’s new Query() syntax gives the following error:

Error Executing Database Query

Parameter ” not found in the list of parameters specified

We had this query in a CFC with many other functions and it was completely written in CFSCRIPT. We didn’t want to make a separate CFC because the function really belonged in the set of functions the CFC contained. We also didn’t want to rewrite the whole thing using tags to support this one problem child, which was called getFeatures(). Luckily, we came up with a temporary solution.

The workaround

The CFC was in the /services/ folder and was named models.cfc. In order to be able to call our getFeatures() method from models.cfc, we created a tag-based CFC called modelscfml.cfc. We used CFQUERY in there and then had models.cfc extend modelscfml.cfc:

component extends="services.modelscfml" { 

This worked swimmingly, but left a bad taste in our mouths. Then CF11 came out…

The solution

CF11 introduced queryExecute(), which is the best CFSCRIPT implementation for querying in ColdFusion to date. So we tried it out on our getFeatures() method, and it worked! Here is code representative of the final result:

public query function getFeatures(bbox, spatialTables, geotable) {
	arguments.bbox = listToArray(arguments.bbox);
	var bbox_west = arguments.bbox[1];
	var bbox_south = arguments.bbox[2];
	var bbox_east = arguments.bbox[3];
	var bbox_north = arguments.bbox[4];
	return queryExecute("
		DECLARE @boundingbox geometry
		SET @boundingbox = geometry::STGeomFromText('POLYGON((#local.bbox_west# #local.bbox_south#,#local.bbox_east# #local.bbox_south#,#local.bbox_east# #local.bbox_north#,#local.bbox_west# #local.bbox_north#,#local.bbox_west# #local.bbox_south#))',3857)

		SELECT	ID, GRIDCODE
		FROM	FEATURES
		WHERE	@boundingbox.STIntersects(GEOM) = 1
	");
}

So queryExecute() saves the day, but isn’t it odd that the same SQL fails using the new Query() syntax? If you want to run queries in CFSCRIPT but aren’t using CF11, just keep in mind the parser seems to work differently. At least you can use the workaround we used if you ever run into trouble.

Leave a Comment