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 »
10,640 Comments
69,065 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
InVision App - Prototyping Made Beautiful With Prototyping Tools Ben Nadel's Company - Epicenter Consulting Recent Blog Comments
Misty
Feb 12, 2012 at 3:37 AM
Learning ColdFusion 8: CFImage Part III - Watermarks And Transparency
Hi Ben, Just to ask currently it is placed bottom right corner, if i need to replace the same rendered image on the bottom left side or in the bottom center, how that can be calculated. bottom ce ... read »
Raphael Mobis
Feb 11, 2012 at 9:29 PM
Use jQuery's SlideDown() With Fixed-Width Elements To Prevent Jumping
I can't say how glad I am that I found your post. Thank you very much. ... read »
Bartezz
Feb 10, 2012 at 7:21 PM
jQuery AJAX Strips Script Tags And Inserts Them After Parent-Most Elements
Update! Instead of $(eval(options.insertAfter)).after(data['insertData']); I now use: var ajaxNode = document.createElement('span'); var parent = $(eval(options.insertAfter))[0].parentNode; ... read »
Bartezz
Feb 10, 2012 at 6:18 PM
jQuery AJAX Strips Script Tags And Inserts Them After Parent-Most Elements
encountered this same, what I consider, jQuery bug last week. I'm building a site in which I load some content via AJAX. This content contains Linkedin share button placeholders which Linkedin API ne ... read »
Drew Wells
Feb 10, 2012 at 11:30 AM
Cross-Origin Resource Sharing (CORS) AJAX Requests Between jQuery And Node.js
After you understand the concepts here, this is an awesome cheatsheet for enabling CORS in just about anything http://enable-cors.org/ ... read »
JM
Feb 10, 2012 at 9:10 AM
My Safari Browser SQLite Database Hello World Example
@Amy, Here is a very good tutorial on how to use JOIN: http://www.sqltutorial.org/sqljoin-innerjoin.aspx ... read »
Stefan Richter
Feb 10, 2012 at 4:42 AM
Building A Twitter-Inspired RESTful API Architecture In ColdFusion
This is great, very useful Ben. I spotted a small typo in the api.cgm listing: <cfthrow type="Unauthroized" /> Cheers Stefan ... read »
Hugh
Feb 9, 2012 at 10:35 PM
CFDirectory Filtering Uses Pipe Character For Multiple Filters (Thanks Steve Withington)
I was wondering if there would be a filter you could apply so that you got everything but what you included in the filter. As in show me all docs that are not a .pdf. ... read »