How can I assign null to a query parameter in ColdFusion?

Question

I have a database table with an int column, which can also be null. Adding an int to a 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 leads to an error, however, that "" does not have a numeric number.

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

Any ideas how I can get 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 ...); 

Say <cfqueryparam> and addparam support the null argument in addition to others, such as name or cfsqltype . Setting null to true will provide the given value to the database as null zero. For convenience, I use the ternary conditional operator to insert a true or false value. You can achieve the same as in the old school, using iif() .

Answer 2

I think you want this ...

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