Undefined Values In Manually-Built Query Are NULL

Posted July 31, 2006 at 3:07 PM 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.


You Might Also Be Interested In:



Reader Comments

jyoseph
Sep 7, 2010 at 12:06 PM // reply »
20 Comments
212 Points

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!


Ben Nadel
Sep 7, 2010 at 3:10 PM // reply »
11,314 Comments
76,113 Points

@Jyoseph,

Ha ha, always glad to hear this stuff comes in handy, even if 4 years old :)


Post A Comment

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.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:
Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>







  • Help Wanted - Find Your Next ColdFusion Job
Ben Nadel's Company - Epicenter Consulting Recent Blog Comments
Alesei Narkevitch
Jun 19, 2013 at 11:31 PM
Directive Link, $observe, And $watch Functions Execute Inside An AngularJS Context
@Ben, bunch to learn indeed, but thats fun part : ) ... read »
Burdock-roots
Jun 19, 2013 at 10:41 PM
Referencing ColdFusion Query Columns In A Loop Using Both Array And Dot Notation
Burdock-roots Are you going fat day by day? You need to be good for your family and make some money too. So we bring for you a best product that helps you to be more energetic every day. You will b ... read »
Diego Vilar
Jun 19, 2013 at 9:52 PM
Working With Inherited Collections In AngularJS
I recognize the applicability of your solution, and how easy it makes to share data across multiple views or even "submodules" of rather simple application. But it seems to me that it creat ... read »
Ben Nadel
Jun 19, 2013 at 9:38 PM
Directive Link, $observe, And $watch Functions Execute Inside An AngularJS Context
@Alesei, Glad you like it. Even after working with AngularJS for months, I still get a bunch of unexpected, "$digest is already in progress". So hard to debug sometimes! ... read »
Ben Nadel
Jun 19, 2013 at 9:36 PM
Working With Inherited Collections In AngularJS
@Mike, The relationship of $scope values is definitely an interesting thing! But it's not simple - it really forces you to understand prototypal inheritance, which is not at all a simple topic! Gla ... read »
Ben Nadel
Jun 19, 2013 at 9:35 PM
Experimenting With The Amazon Simple Storage Service (S3) API Using ColdFusion
@Joe, Oh, super interesting! I had only thought to url-encode the signature; but I think that's because the S3 docs actually have a special NOTE telling you to do so. It would have never occurred t ... read »
Ben Nadel
Jun 19, 2013 at 9:32 PM
Experimenting With The Amazon Simple Storage Service (S3) API Using ColdFusion
@Richard, Glad you like! Hopefully I'll have some more interesting stuff coming. This morning, I blogged a bit more about generating the pre-signed, query string authenticated URLs; but, then deeme ... read »
Ben Nadel
Jun 19, 2013 at 9:31 PM
Filter vs. ngHide With ngRepeat In AngularJS
@Mike, Honestly, in the majority of cases, I would say there isn't going to be a difference. Both approaches have trade-offs. If you use the filter, then you have fewer DOM elements and fewer $scop ... read »
InVision App - Prototyping Made Beautiful With Prototyping Tools