Undefined Values In Manually-Built Query Are NULL

Posted July 31, 2006 at 3:07 PM

Tags: SQL, ColdFusion

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:

 Launch code in new window » Download code as text file »

  • <!--- 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:

 Launch code in new window » Download code as text file »

  • <!--- 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.

Download Code Snippet ZIP File

Comments (0)  |  Post Comment  |  Ask Ben  |  Permalink  |  Other Searches  |  Print Page





Reader Comments

There are no comments posted for this web log entry.


Post Comment  |  Ask Ben


Home   |   Web Log   |   ColdFusion   |   Projects   |   Resume   |   Job Form   |   Search   |   Contact
Epicenter Consulting - Custom Software Solutions for Business Evolution HostMySite.com - The Leader In ColdFusion Hosting