Setting a Column to Null With queryNew in ColdFusion
tl:dr; - use
javacast("null", "") or get a ColdFusion null some other way and you'll be fine
I've been on a bit of a refactoring & testing kick lately thanks to a few recent
episodes of the Working Code podcast (specifically
I recently set out to refactor a common function in the CMS platform we have
built at work and realized that I need to mock a query that I can use in my
test suites. We're using TestBox, so I have MockBox available, but
doesn't handle making a column null. So, what's a guy to do? Go to Slack and ask,
I got a reply from an outspoken chap from Europe whose name you've probably come across if you've spent any amount of time in the CFML world. He was kind enough to hit me with this snippet (the last 2 lines are mine):
records = queryNew(
[1, null(), "one"],
[2, "rua", null()],
[null(), "toru", "three"],
[4, "wha", "four"]
writeOutput("value: [#records.en#] isNull: [#isNull(records.en)#]");
writeOutput("isNull(null()): " & isNull(null()));
This example shows that returning an empty result from the
– a proper ColdFusion null as evidenced by the last line of output –
and giving that to
queryNew doesn't come back out as a null if you try to grab
it off the query.
OK, so does the fact that it's not a true null make any bit of difference for what
I'm trying to do? As a reminder, I'm trying to make a query that I can feed to
a function that runs a Query of Queries (QoQ – suppress your groans, please) and that
QoQ has a
WHERE column IS NULL check in it. So long as I can pacify the QoQ,
I'll be off to the next problem. So, let's check...
I'll add the following code to the snippet above and see what the result is.
<cfquery dbtype="query" name="filtered">
WHERE mi IS NULL
<cfdump var="#filtered#" label="filtered results" />
Well, it... works? My definition of "works" here is incredibly narrow and only accounts
for the one situation with which I'm concerned. I don't like the idea of having
to add a
null() function to a bunch of our tests, so I did a quick check and confirmed
javacast("null", "") instead of
null() when creating the array
for the data used by
queryNew functions in the same way.
Another observation I've made is that if you're lucky enough to have the column you want to be null as the last column, you can pass fewer data points than columns and it works itself out. Here's a separate but similar example showing some records where one individual hasn't logged in and thus has no value for the lastLogin column.
records = queryNew(
[1, "David", "2021-06-09 08:15:36"],
[2, "Jennifer", "2021-05-24 16:15:42"],
[3, "Elizabeth", "2019-12-04 19:49:14"],
In this example, the
lastLogin value for "Ronald" would be null enough for QoQ
WHERE lastLogin IS NULL and find the record.
If you'd like to play around with this, here's a link to the code on trycf.com.