http://coldfusion.com logo
Join Slack
Powered by
does MockBox's querysim helper support `null` valu...
# box-products
m
does MockBox's querysim helper support 
null
values? I'm doing code like
Copy code
querysim("col1,col2,col3
foo | | bar")
c
@mithlond I stopped using querysim as it didn't offer me much advantage over just using 
queryNew()
and building an actual query from an array of structs.
m
gotcha. I've never used queryNew that much, but looking at the docs it's pretty simple/straightforward. I'll give that a go. thanks 🙂
c
I think querysim was written at a time when queryNew() wasn't as robust as it is in recent CF versions (2016/2018/2021).
s
are you using adobeCF or lucee?
m
ACF
s
what version are you on?
To my knowledge, unless you have enableNullSupport on (2018 and above) the query object will always return an 
[empty string]
in place of a 
null
whether its from the database or created in a 
queryNew
function. Just for clarity 
querySim
is just a decorator/wrapper around 
queryNew
to simplify creating a query object. https://github.com/Ortus-Solutions/TestBox/blob/059d40989d84428b673e688779be6fc428f3987c/system/MockBox.cfc#L821-L839
m
we're on 2018, and I'm pretty sure we haven't enabled null support (all our queries from the db come back with empty string whenever it's null). Now I'm second guessing myself though, because doing a QoQ with myCol IS NULL works for what comes from the db, but not for what came from querySim. But... it should just be empty strings in both cases
s
well now that part will work
Copy code
<cfscript>
news = queryNew([
    {"id":1,"title":"Dewey defeats Truman"},
    {"id":2,"title":javacast('null','')}
]);
filterNoNulls = queryExecute("select * from news where title is null",{},{dbtype="query"})
writeDump(filterNoNulls);
</cfscript>
m
so that returns 1 row for me, as expected (and similar to my app's code)
s
so point of clarification. the query object will store 
null
values (you can do it like i showed above) the query object will 
print
empty strings when looping over it
m
however, if I change out the 
is null
for 
= ''
it returns no rows 😬
aaaaah, ok
or when you get the value out to compare it in cf, also convert to empty string, I'm guessing
s
yes
m
but internally, as used by a QoQ, it remains a null, right (even w/ the null support flag off)
so that null flag just means "keep it as a null when it crosses over to CF land"
s
yes, as long as you pass it a 
javacast('null','')
. without 
enablenullsupport
you cannot just call 
null
in cf2018. Lucee does have a function 
nullvalue()
that will create a null value
m
ok, now this is making a lot more sense. thank you!
I did end up going with queryNew in my unit test, and omitting the key for the column I want to be null in one of the rows' structs made it be null/undefined/whatever as it produced the query, so my test and the real app are both happy now (as am I) 🙂
👏 1
b
I ran across the same question a year ago
s
nice! I forgot to mention the other way to generate null (as you did in your post)
Copy code
function null(){
    return;
}
5 Views
Open in Slack
PreviousNext
Powered by