The is the first in several posts I’m planning on doing regarding the little gotchas we stumbled across in migrating from Adobe Coldfusion to Railo. I want to point out here that it’s not shortcomings of Railo, more little differences, or our slightly sloppy code.

Those of you that work with CF and databases will no doubt use cfqueryparam. It’s a marvellous tag and we use it for every single variable in our queries. This includes things like the following:

<cfquery name="some_images">
    select
        *
    from
        image
    limit
        <cfqueryparam cfsqltype="cf_sql_numeric" value="#limit#" />
</cfquery>

And there’s the problem. You see, in CF the limit variable is probably a double (or something like that). In this case mysql is expecting an integer. So the query that’s run looks like this:

SELECT * FROM image LIMIT 10.0

which doesn’t make a lot of sense to mysql (apparently 10 != 10.0). The solution is simple, just replace cf_sql_numeric with cf_sql_integer. Like I say, this was our sloppy coding but this had always worked perfectly fine in CF so we’d never worried about it before.

So, use the correct datatypes in cfqueryparam. Easy.