How can I assign a null value to a query param in ColdFusion?

Question

I have a database table with an int column that can also be null. Adding an int to the column is fine, but setting it back to null causes an error. Since ColdFusion does not support null values, I usually pass the value as an empty string:

local.myParam = "";

This causes an error however, that "" is not of type numeric.

post.addParam(name="parentId", cfsqltype="CF_SQL_INTEGER", value=arguments.parentId);

Any ideas how I can work around this limitation?

Answer 1

If you want the empty string to be sent as null, you can do something like this:

post.addParam(... null=len(trim(local.myParam)) ? false : true ...);

Which is to say, <cfqueryparam> and addparam support a null argument in addition to the others like name or cfsqltype. Setting null as true will provide the given value to the database as a proper null. For convenience, I'm using the ternary conditional operator to inline a true or false value. You could achieve the same thing, old-school, by using iif().