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 the New York ColdFusion User Group (Dec. 2008) with:

Undefined Values In Manually-Built Query Are NULL

Posted by Ben Nadel
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!

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.