5

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?

Mohamad
  • 34,731
  • 32
  • 140
  • 219

2 Answers2

8

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

Ken Redler
  • 23,863
  • 8
  • 57
  • 69
  • thank you. Is null it's own argument here... so addParam(name="foo", null=len(...), cfsqltype="CF_SQL_INTEGER", value=arguments.parentId) ?? – Mohamad Feb 15 '11 at 16:39
  • @Mohamad, yes, that's how it works. If the null argument evaluates to false, the rest of the `addParam()` works just as if it weren't there. If it's true, then the value is ignored in favor of null. – Ken Redler Feb 15 '11 at 16:56
  • you might want to edit the answer to add a NOT condition, so: null=(! Len(arguments.foo)) ? true : false – Mohamad Feb 15 '11 at 18:00
  • @Mohamad, yes, I noticed that problem too. I switched the true and false rather than using `!` or `not`. – Ken Redler Feb 15 '11 at 18:14
  • This answer doesn't match @Mohamad 's suggestion made on Feb 15 '11 at 18:00. It is still missing the ! in the expression. – HPWD Oct 19 '17 at 17:20
4

I think you want this...

null=yesNoFormat(NOT len(trim(local.myParam)))
Richard Herbert
  • 616
  • 3
  • 5