Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
Ben Nadel at cf.Objective() 2010 (Minneapolis, MN) with: Doug Hughes and Ezra Parker and Dan Wilson and John Mason and Jason Dean and Luis Majano and Mark Mandel and Brian Kotek and Wil Genovese and Rob Brooks-Bilson and Andy Matthews and Simeon Bateman and Ray Camden and Chris Rockett and Joe Bernard and Dan Skaggs and Byron Raines and Barney Boisvert and Simon Free and Steve 'Cutter' Blades and Seth Bienek and Katie Bienek and Jeff Coughlin
Ben Nadel at cf.Objective() 2010 (Minneapolis, MN) with: Doug Hughes@doughughes ) , Ezra Parker , Dan Wilson@DanWilson ) , John Mason@john_mason_ ) , Jason Dean@JasonPDean ) , Luis Majano@lmajano ) , Mark Mandel@Neurotic ) , Brian Kotek , Wil Genovese@wilgeno ) , Rob Brooks-Bilson@styggiti ) , Andy Matthews@commadelimited ) , Simeon Bateman@simBateman ) , Ray Camden@cfjedimaster ) , Chris Rockett ( @RockettMan ) , Joe Bernard@JEBernard ) , Dan Skaggs@dskaggs ) , Byron Raines ( @byronraines ) , Barney Boisvert@barneyb ) , Simon Free@simonfree ) , Steve 'Cutter' Blades@cutterbl ) , Seth Bienek@sethbienek ) , Katie Bienek@KatieBienek ) , and Jeff Coughlin@jeffcoughlin )

Undefined Values In Manually-Built Query Are NULL

By Ben Nadel on
Tags: ColdFusion, SQL

A while back, I had blogged about how if you build a query from scratch in ColdFusion using the QueryNew() and QueryAddRow() and do NOT define some of the cell values, errors will be thrown when you try to use the LIKE directive as well as the UPPER() and LOWER() functions. After some further thought, I wanted to know what is in that cell value. My gut instinct said NULL, but ColdFusion doesn't really like NULLs... so it was time for testing - yeah, boy!

To test this, I built the following query:

  • <!--- Create a test query. --->
  • <cfset qGirls = QueryNew( "id, name, hot" ) />
  •  
  • <cfset QueryAddRow( qGirls ) />
  • <cfset qGirls[ "id" ][ qGirls.RecordCount ] = "1" />
  • <cfset qGirls[ "name" ][ qGirls.RecordCount ] = "vivenzio, sarah" />
  • <cfset qGirls[ "hot" ][ qGirls.RecordCount ] = "yes" />
  •  
  • <cfset QueryAddRow( qGirls ) />
  • <cfset qGirls[ "id" ][ qGirls.RecordCount ] = "2" />
  • <cfset qGirls[ "name" ][ qGirls.RecordCount ] = "cooper, anna" />
  • <!--- !!! -- NO HOT VALUE SET -- !!! --->
  •  
  • <cfset QueryAddRow( qGirls ) />
  • <cfset qGirls[ "id" ][ qGirls.RecordCount ] = "3" />
  • <cfset qGirls[ "name" ][ qGirls.RecordCount ] = "thomas, ashley" />
  • <cfset qGirls[ "hot" ][ qGirls.RecordCount ] = "yes" />

Notice that in the second row, I do NOT set a value in the "hot" column. Then, I tried doing a ColdFusion query of queries for NULL and NOT NULL values:

  • <!--- Query for entries where hot is NULL. --->
  • <cfquery name="qNullGirls" dbtype="query">
  • SELECT
  • *
  • FROM
  • qGirls
  • WHERE
  • hot IS NULL
  • </cfquery>
  •  
  • <!--- Query for entries where hot iS NOT NULL. --->
  • <cfquery name="qNotNullGirls" dbtype="query">
  • SELECT
  • *
  • FROM
  • qGirls
  • WHERE
  • hot IS NOT NULL
  • </cfquery>

My hunch was correct! The first query, qNullGirls, returned the second record where the "hot" column was not explicitly defined. The second query, qNotNullGirls, returned the first and third records where all column values were explicitly defined.




Reader Comments

Once again, you helped me huge. I keep finding super old blog entries of yours on Google and they always seem to solve the problem I'm working on. You are da man!