cfQueryParam bug of the day

Everyone uses cfQueryParam in all of their queries, right? RIGHT?

Did you know that if you have a query where all you change is the cfSqlType of a cfQueryParam, CFMX7 will silently go boom when you run that query? For example:

<cfquery datasource="dsn" name="FindPlace">
SELECT id
FROM places
WHERE (lng = <cfqueryparam cfsqltype="CF_SQL_DECIMAL" value="#Val(place.lng)#">)
  AND (lat = <cfqueryparam cfsqltype="CF_SQL_DECIMAL" value="#Val(place.lat)#">)
</cfquery>

Now let’s say that we figure out that using a Decimal type for a key probably isn’t the best design decision. Since we know that latitude and longitude are constrained to [-90,90] and [-180,180] respectively, and that 6 decimal places equates to something like 4 inches, we can optimize things a bit by converting the field into a premultiplied Integer and converting our query:

<cfquery datasource="dsn" name="FindPlace">
SELECT id
FROM places
WHERE (lng = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#Int(Val(place.lng)*1000000)#">)
  AND (lat = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#Int(Val(place.lat)*1000000)#">)
</cfquery>

But lo and behold, ColdFusion silently fails. Oh, the query will execute all right, and the page will keep going, but the query won’t exist. Any use of the “FindPlace” variable from then on out will bomb with a “variable does not exist”-type error.

Notice that we didn’t change anything other than the cfSqlType? I have to presume that means we’re running up against CF’s query compiler. I get the feeling that it’s compiling the query on the server for the first one, but then trying to run the second query using the first compiled query. The only way to fix it is to change the whitespace in the query to get CF to recompile it.

Arrrrrgggghhhh.

Published by

Rick Osborne

I am a web geek who has been doing this sort of thing entirely too long. I rant, I muse, I whine. That is, I am not at all atypical for my breed.