Understanding null behaviour in Lucee and ColdFusion

[Vikas Patel]

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 --->

<cfquery name="qList" dbtype="query">

SELECT * from qNew

</cfquery>

<cfdump var="#qList#" />

<br/>

<br/>

<cfquery name="qList" dbtype="query">

SELECT * from qNew where name = ''

</cfquery>

<cfdump var="#qList#" />

<br/>

<br/>

<cfquery name="qList" dbtype="query">

SELECT * from qNew where name != ''

</cfquery>

<cfdump var="#qList#" />

<br/>

<br/>

<cfquery name="qList" dbtype="query">

SELECT * from qNew where name is null

</cfquery>

<cfdump var="#qList#" />

<br/>

<br/>

<cfquery name="qList" dbtype="query">

SELECT * from qNew where name is not null

</cfquery>

<cfdump var="#qList#" />

<br/>

<br/>

<cfabort />

[Michael Offner]

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

<cfset x=null>

<cfif x==null>x is null</cfif>

in your examle above you then can do

...

<cfset QuerySetCell(qNew, 'name', '')>

...

<cloop query="qNew">

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

</cfloop>

Micha

--
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+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/f4a2d0e1-ff72-4874-b871-e4fda27bf085%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Vikas Patel]

I've enabled setting for complete null support. But it has not effect to , since blank is still being considered as a null. '' and nullValue() shouldn't have different effect?