Understanding null behaviour in Lucee and ColdFusion

vikaskanani · April 13, 2015, 4:30am

In Lucee, I don’t understand how null is handled in query. If I check check
with IS NULL condition, it works, but if I check with blank string, NULL
seems to be considered as a blank string. Is this a bug or is this an
expected behavior?

Code to test in ColdFusion and Lucee:

SELECT * from qNew

SELECT * from qNew where name = ''

SELECT * from qNew where name != ''

SELECT * from qNew where name is null

SELECT * from qNew where name is not null

micstriit · April 13, 2015, 7:16am

By default (more later) Lucee handles null values as empty strings what is
not the same…
in your examle you have no null value at all, your line should look like
this to contain a null value
<cfset QuerySetCell(qNew, ‘name’, nullValue())>
In the Lucee server admin under Settings->Language/Compiler you find a
setting “Null Support” by enabling this you have full null support in
Lucee, so you can do things like

<cfif x==null>x is null
in your examle above you then can do
…
<cfset QuerySetCell(qNew, ‘name’, ‘’)>
…

<cfif qNew.name == null>name is not set

MichaOn Mon, Apr 13, 2015 at 6:30 AM, Vikas Patel <@Vikas_Patel> wrote:

In Lucee, I don’t understand how null is handled in query. If I check
check with IS NULL condition, it works, but if I check with blank string,
NULL seems to be considered as a blank string. Is this a bug or is this an
expected behavior?

Code to test in ColdFusion and Lucee:

<cfset qNew = queryNew(“id,name”)>

<cfset QueryAddRow(qNew)>
<cfset QuerySetCell(qNew, ‘id’, 1)>
<cfset QuerySetCell(qNew, ‘name’, ‘Vikas’)>

<cfset QueryAddRow(qNew)>
<cfset QuerySetCell(qNew, ‘id’, 2)>
<cfset QuerySetCell(qNew, ‘name’, ‘’)>

<cfset QueryAddRow(qNew)>
<cfset QuerySetCell(qNew, ‘id’, 3)> <!-- Trying to keep name as null —>
SELECT * from qNew

SELECT * from qNew where name = ''

SELECT * from qNew where name != ''

SELECT * from qNew where name is null

SELECT * from qNew where name is not null

–
You received this message because you are subscribed to the Google Groups
“Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send an
email to lucee+unsubscribe@googlegroups.com.
To post to this group, send email to lucee@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/f4a2d0e1-ff72-4874-b871-e4fda27bf085%40googlegroups.com
https://groups.google.com/d/msgid/lucee/f4a2d0e1-ff72-4874-b871-e4fda27bf085%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.