Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at TechCrunch Disrupt (New York, NY) with:

Testing For NULL Values In A ColdFusion Query Result Set

By Ben Nadel on
Tags: ColdFusion, SQL

As you all know, when you get values returned form the SQL server in a ColdFusion query object, NULL column values look and act just like empty strings. I have big issues with the use of NULL values in most data column (except for certain dates pretty much) but if you are one who for one reason or another likes the NULL value, here is a way to determine if your query column value is NULL or an empty string.

First, let's build a query that has both NULL values and empty values:

  • <cfquery name="qNullTest" datasource="...">
  • DECLARE
  • @hotties TABLE (
  • name VARCHAR( 30 ),
  • hotness FLOAT
  • )
  • ;
  •  
  • <!--- Insert values into our in-memory table. --->
  •  
  • INSERT INTO @hotties
  • ( name, hotness ) VALUES ( '', 9.0 );
  •  
  • INSERT INTO @hotties
  • ( name, hotness ) VALUES ( 'Sarah', 9.5 );
  •  
  • INSERT INTO @hotties
  • ( name, hotness ) VALUES ( 'Azure', 9.5 );
  •  
  • INSERT INTO @hotties
  • ( name, hotness ) VALUES ( NULL, NULL );
  •  
  • INSERT INTO @hotties
  • ( name, hotness ) VALUES ( 'Annie', 8.0 );
  •  
  • INSERT INTO @hotties
  • ( name, hotness ) VALUES ( NULL, NULL );
  •  
  •  
  • <!--- Get the values from our in-memory table. --->
  • SELECT
  • name,
  • hotness
  • FROM
  • @hotties
  • ;
  • </cfquery>

As you can see from above, our query result set should contain values that are both NULL and empty. And, you can also see by CFDumping it out, that ColdFusion views both of these values types as empty strings:


 
 
 

 
CFQuery Null And Empty Values  
 
 
 

Now, since ColdFusion views both of the these values types as empty strings, if you want to determine the NULL-ness of a value, you cannot use ColdFusion directly. Instead, you have to use Java to check the value. And the way we do that is to get Java to convert the value to a string:

  • <!--- Loop over the values in the query. --->
  • <cfloop query="qNullTest">
  •  
  • <!--- Get the string value for the NAME column. --->
  • <cfset REQUEST.NullNameTest = qNullTest.GetString( "name" ) />
  •  
  • <!--- Get the string value for the HOTNESS column. --->
  • <cfset REQUEST.NullHotnessTest = qNullTest.GetString( "hotness" ) />
  •  
  • Name :
  • #qNullTest.name# :
  • #YesNoFormat(
  • NOT StructKeyExists( REQUEST, "NullNameTest" )
  • )#<br />
  •  
  • Hotness :
  • #qNullTest.hotness# :
  • #YesNoFormat(
  • NOT StructKeyExists( REQUEST, "NullHotnessTest" )
  • )#<br />
  •  
  • <br />
  •  
  • </cfloop>

This gives us the output:

Name : : No
Hotness : 9 : No

Name : Sarah : No
Hotness : 9.5 : No

Name : Azure : No
Hotness : 9.5 : No

Name : : Yes
Hotness : : Yes

Name : Annie : No
Hotness : 8 : No

Name : : Yes
Hotness : : Yes

In the above output, the Yes and No values represent NULL values. As you can see, we correctly identified the 3rd and 5th iterations has having NULL values, but did NOT falsely identify the empty string in iteration one as a NULL value. Pretty slick right?!?

The way it works is that we are using the ResultSets's GetString() method to return the string version of the column value. This method returns the string if the value is present but returns NULL if the column value is NULL. And, as you hopefully know, a NULL value will destroy a variable in ColdFusion. Therefore, if the particular value test key (ex. NullNameTest) no longer exists in the REQUEST scope (it's original scope), we can be sure that the GetString() method returned a NULL value and NOT an empty string.

Again, I am strongly against NULL values in SQL, but if you need it, this is a fairly simple way to test for it.




Reader Comments

It is much easier to use ISNULL in your SQL statement

SELECT ISNULL(name,'No Name Entered') AS name,
ISNULL(hotness,'No Hotness Rank') AS hotness
FROM @hotties

Reply to this Comment

Gus,

Word up. I am huge fan of IsNull(). I agree with you - NULL values generally shouldn't make it to the ColdFusion side of things. But, some people want it.

Reply to this Comment

Ben:
I guess this comment belongs on your other entry relating to your dislike of NULLs in a DB, but since that article is older, I will place it here...
I take it you have never worked with Oracle then?
In Oracle, if a char or varchar field is set to an empty string, Oracle stores this value as NULL.
The use of NULL does make sense to me. For instance, if you have a website with user accounts, and you allow the user to enter data about themselves, then you have input fields on a form and some of them are not required. If the user does not put a value in a field, they are not saying that their answer to this question is a blank string, they are saying that they are chosing to not give an answer for that question. That is a perfect case where you should be storing a NULL instead of an empty string. Just my $0.02. Keep up the blogging!

Reply to this Comment

Ken,

I have not worked with Oracle. I pretty much only work in MS SQL at this point. And I see what you are saying, but I would take that example and just argue the other way... if some leaves a field blank, how can you as the programmer determine if the user meant to "exclude" the answer or to actually leave it blank (meaning to submit a blank field, NOT and empty field).

It really just becomes a judgment call.

But really, my feelings about NULL come from a convenience stand point rather than a philosophical "what is this data actually" standpoint. In my experience, having NULL values causes more overhead in "thought" processing.

Reply to this Comment

Ben:
I understand the convienence.
We just moved one of our legacy apps to Oracle from MS SQL Server, and I was amazed at the code I had to go through. The original programmer couldn't decide if he liked NULL or blank, so in the same field he would sometimes insert a null for an empty string and sometimes he would insert an empty string.
This meant that everwhere that field was referenced in a where statement to check if it was blank, there was something like this:
where ... and (colA is NULL OR colA = '')
What a PITA!!!

Reply to this Comment

Ha ha, sorry to hear you had to deal with that. I guess which ever way you go, the lesson is be consistent.

The worst though, to me, is fields that shouldn't contain null values that do sometimes. Things like boolean flag fields like "is_active" or something. That field shouldn't be null ever (in like 99.99999% of cases). It's not a user-entered data, it's a property of the data row. But sometimes people are very sloppy about their DB setup and allow NULL values.

Drives me bonkers :D

Reply to this Comment

I know it seems like a "duh" to us programmer-types, but the distinction between "no data" and "empty data" (or, for numeric fields, zero) is something that most non-propellerheads just don't get. Combine this with the fact that in Excel, you can't add two cells if one of them is blank, and you end up with people that love shotgunning zeros all over the place to get their formulas to work. (I got chastised the other day for a report "that's half-wrong, because the cells are blank!" and had to calmly explain that the blank cells meant "no data", not zero.)

(And here's a hint for all those arriving from Google: instead of =A1+B2, try =SUM(A1)+SUM(B2) or =SUM(A1,B2) - you'll get exactly what you want without polluting the data. You plebe.)

The AS/400 software my company uses for CRM does not use NULL anywhere, despite the fact that the underlying DB2 database can handle them just fine. Thus, there's no way to distinguish between something that has a blank name, and something that has no name.

But wait, it gets worse! For numeric fields, a zero is used to indicate both a zero and "no data". You have to look at the other fields in the table and try to infer which one is intended. For forecasting purposes, I have no way of telling between an item that we haven't yet made a forecast for, and one where we don't think we'll sell anything! And this is multi-million-dollar software!

Reply to this Comment

Rick,

I am in complete agreement that NULL values should be used anywhere where you actually need to distinguish a NULL from a different value (or where polluting the data as you say). For example, "date_last_login" MUST allow nulls because you can certainly set up a user records without them having logged in yet.

But, again, most of us don't have to deal with that sort of stuff most of the time. Take, for example, a product that has "discount_price" column. Now, maybe a given product doesn't have a discount price. From a processing stand point, I would argue that most of the time NULL in this column will precipitate the same action as "0" (zero).

But, the advantage that ZERO has in this case (even though NULL might be more appropriate from a philosophical stand point) is that you can do something like:

<cfif qProducts.discount_price>
.... apply discount price .....
</cfif>

If the discount_price was NULL, rather than zero, this would throw an error since "" cannot be evaluated to boolean (as we have all seen at one time or another I am sure).

So again, it's not that NULL is wrong. My argument is that is makes coding less readable / easy to write (IMO) and really adds very little benefit from what I can see.

Of course, we all code in our own ways.

Reply to this Comment

Mark,

Same thing I guess... of course, yours has the sweetness of being wrapped up in a UDF. I prefer StructKeyExists() rather than IsDefined(), but we are splitting hairs there.

It's totally funny, I actually posted comments on your entry back in September. And looking at the comments I posted, I think I did not quite understand what you were doing or how NULL values worked exactly behind the scenes :)

Well played my friend, well played.

Reply to this Comment

Java already has built in test for null value in a ResultSet/RowSet.

<cfquery name="query" result="queryResult" datasource="data"
>select
[fieldNormal]
,[fieldMayBeNull]
from [dbo].[records]<
/cfquery>

<cfloop index="index" from="1" to="#query.recordCount#">

<div>index: <cfoutput>#index#</cfoutput></div>

<div>fieldNormal: <cfoutput>#query["fieldNormal"][index]#</cfoutput></div>

<cfset query.absolute(index)/>
<cfset value=query.getObject("fieldMayBeNull")/>
<cfset null=query.wasNull()/>
<cfset query.first()/>
<div>fieldMayBeNull:
<cfif (not(null))>
<cfoutput>#value#</cfoutput>
<cfelse>
(null)
</cfif>
</div>

</cfloop>

Reply to this Comment

Or, if you prefer a query loop:

<cfloop query="query">
<div>index: <cfdump var="#query.currentRow#"/></div>

<div>fieldNormal: <cfdump var="#query["fieldNormal"]#"/></div>

<div>fieldMayBeNull:
<cfset value=query.getObject("fieldMayBeNull")/>
<cfset null=query.wasNull()/>
<cfif (not(null))>
<cfoutput>#String_encodeXml(toString(value))#</cfoutput>
<cfelse>
null
</cfif>
</div>
</cfloop>

Reply to this Comment

@Alex,

Cool stuff, I have seen the underlying Java features of the Query, but have not played around with them too much.

Reply to this Comment

Sorry, had an undefined UDF in there.

<cfloop query="query">
<div>index: <cfoutput>#currentRow#</cfoutput></div>

<div>fieldNormal: <cfoutput>#query["fieldNormal"][currentRow]#</cfoutput></div>

<div>fieldMayBeNull:
<cfset value=query.getObject("fieldMayBeNull")/>
<cfset null=query.wasNull()/>
<cfif (not(null))>
<cfoutput>#value#</cfoutput>
<cfelse>
null
</cfif>
</div>
</cfloop>

Reply to this Comment

To go UDF.

<cffunction name="Query_valueIsNull" output="no">
<cfargument name="query" type="query" required="true"/>
<cfargument name="row" type="numeric" required="true"/>
<cfargument name="column" type="string" required="true"/>
<cfset cacheRow=query.currentRow/>
<cfset query.absolute(row)/>
<cfset value=query.getObject(column)/>
<cfset valueIsNull=query.wasNull()/>
<cfset query.absolute(cacheRow)/>
<cfreturn valueIsNull/>
</cffunction>

Reply to this Comment

Another solution that I used to solve the problem without use Java syntax is:

<cffunction name="isQueryFieldNull" hint="is this null" access="private" returntype="boolean" output="false">
<cfargument name="query" hint="the current query" type="query" required="Yes">
<cfargument name="column" hint="Column Name" type="string" required="Yes">

<cfquery dbtype="query" name="rsIFNULLQuery">
SELECT COUNT(*) AS Conta FROM query WHERE #column# IS NULL
</cfquery>

<cfif rsIFNULLQuery.Conta gt 0>
<cfreturn true>
</cfif>

<cfreturn false>
</cffunction>

As you can see, I check the null value of one query column making a new filter (query of query) with where condition IS NULL.

This solution works fine.

Reply to this Comment

@Alessio,

That's a slick idea. Since query of queries handle NULL comparisons, that should work fine.

Reply to this Comment

Cool post Ben

I'm trying to do this:

<cfqueryparam value="#arguments.depositRequestedAmount#" cfsqltype="cf_sql_varchar" null="#IsNull(arguments.depositRequestedAmount)#">

However if arguments.depositRequestedAmount is null the cfsqltype check is still being done on the value. What am I missing?

Reply to this Comment

@Nikos,

Is this throwing an error? I've never actually tried to pass a NULL value to the value attribute. Is ColdFusion actually working with that?

Reply to this Comment

@Nikos,

Are you on ColdFusion 9? IsNull() was not a method until CF9 - just want to make sure we are on the same page about what is going on.

Reply to this Comment

cf9 is the one :)

Is alight I just did a work around like this

<cfqueryparam value="#arguments.rfRate#" cfsqltype="cf_sql_float" null="#arguments.rfCeilingRate eq ''#">,

Reply to this Comment

To get around the empty strings in an UPDATE statement I pretended the variable was a string by enclosing it in single quotes, CAST it to an integer, let it convert empty strings to zeroes, and used NULLIF to change the zeros to NULLs:

SET [Field1] = NULLIF(CAST('#Form.Field1#' as INT), 0)

It should work the same for CASTing as NVARCHAR.

Reply to this Comment

@Gus: It is much easier to use ISNULL in your SQL statement

@Gus, there is a problem with that as well (with my somewhat limited sql server knowledge). In my query, I check for null and if it is null, I assign a default value. But this doesn't fix my problem (if there is a null value, the display that I have gets messed up, the <td> in the column gets messed up).

To fix this I did the check in CF instead. I used Ben's getString() method to check if there was a null and if there was, I assign a default value. Problem solved.

-roger

Reply to this Comment

Here's a quick and dirty solution.

The code replaces an empty string with the word "Null" and outputs the query.

(readers beware, it's ugly.

  • <!---get total recordcount from Query--->
  • <cfloop list="#query.recordcount#" index="x">
  •  
  • <!---check to see if any NULL variables exist in the query--->
  • <cfif ISNULL ("#query.columnName[x]#") OR"#query.columnName[x]#" EQ " ">
  •  
  • <!---convert Query to a list (so we can use reReplace to find the empty strings) --->
  • <cfset list = #valueList(query.columnName)#>
  •  
  •  
  • <!--- search the list and replace any two commas that are side-by-side (this indicates an empty string within a valueList() output), and replace it with the word NULL surrounded with two commas (to replace the commas removed) --->
  • <cfset list = rereplace(#list#, ",,", ",NULL,", "ALL")>
  •  
  • <!--Throw the list into a loop, and output it--->
  • <cfloop list="#list#" index="y">
  • <cfinput name="output" value="#y# "><br> </cfloop>
  • </cfif>
  • </cfloop>

Reply to this Comment

@Aaron,

Regarding your use of isNull(), I cannot get isNull() to every return True for a query column value. What version of ColdFusion 9 are you using?

Reply to this Comment

Post A Comment

?
You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.