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

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?

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().

I think you want this...

null=yesNoFormat(NOT len(trim(local.myParam)))