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 (May. 2009) with: Abraham Lloyd and Peter Bell and Gert Franz

ColdFusion Query of Queries: Uses NULL Values, Returns Empty Strings

Posted by Ben Nadel
Tags: ColdFusion, SQL

There was a discussion going on over a House of Fusion about NULL values in a ColdFusion query of queries. Some guy was having trouble with a function IFNULL(). His problem is that IFNULL() is not allowed in ColdFusion query of queries. But, the thread got me thinking about NULL values.

I know that if you return a NULL value in a ColdFusion query, it outputs as an empty string. I also know that NULL values are not used in ColdFusion and actually destroy the variable they are assigned to (if, for example, they are returned from a Java method call). So, I set up some experimentation to see how ColdFusion query of queries handle NULL values.

This is the initial query that I set up from the database:

  • <!--- Query for test values from database. --->
  • <cfquery name="qNullQuery" datasource="#REQUEST.DSN.Source#" username="#REQUEST.DSN.Username#" password="#REQUEST.DSN.Password#">
  • SELECT
  • <!--- Here we want to get an actual null value. --->
  • (
  • NULL
  • ) AS null_value,
  •  
  • <!---
  • Here we want to get an empty string, which is what the NULL
  • value gets evaluated to on the ColdFusion side of things.
  • --->
  • (
  • ''
  • ) AS empty_string
  • ;
  • </cfquery>

This returns a single record which as a NULL value and an empty string. I then tried to run various query on that query:

  • <!--- Do a query of queries for NULL values. --->
  • <cfquery name="qTestNULL" dbtype="query">
  • SELECT
  • *
  • FROM
  • qNullQuery
  • WHERE
  • null_value IS NULL
  • </cfquery>
  •  
  • <!--- Do a query of queries for NOT NULL values. --->
  • <cfquery name="qTestNotNULL" dbtype="query">
  • SELECT
  • *
  • FROM
  • qNullQuery
  • WHERE
  • null_value IS NOT NULL
  • </cfquery>
  •  
  • <!--- Do a query of queries for NULL string values. --->
  • <cfquery name="qTestStringNULL" dbtype="query">
  • SELECT
  • *
  • FROM
  • qNullQuery
  • WHERE
  • empty_string IS NULL
  • </cfquery>
  •  
  • <!--- Do a query of queries for NOT NULL string values. --->
  • <cfquery name="qTestStringNULL" dbtype="query">
  • SELECT
  • *
  • FROM
  • qNullQuery
  • WHERE
  • empty_string IS NOT NULL
  • </cfquery>
  •  
  • <!--- Output the test results. --->
  •  
  • <h3>
  • qNullQuery
  • </h3>
  • <cfdump var="#qNullQuery#" />
  •  
  • <h3>
  • qTestNULL ( null_value IS NULL )
  • </h3>
  • <cfdump var="#qTestNULL#" />
  •  
  • <h3>
  • qTestNotNULL ( null_value IS NOT NULL )
  • </h3>
  • <cfdump var="#qTestNotNULL#" />
  •  
  • <h3>
  • qTestStringNULL ( empty_string IS NULL )
  • </h3>
  • <cfdump var="#qTestStringNULL#" />
  •  
  • <h3>
  • qTestStringNULL ( empty_string IS NOT NULL )
  • </h3>
  • <cfdump var="#qTestStringNULL#" />
  •  
  • <h3>
  • Testing Values in ColdFusion
  • </h3>
  • <p>
  • GetMetaData( qNullQuery.null_value ).GetName():
  • <cfoutput>
  • #GetMetaData( qNullQuery.null_value ).GetName()#
  • </cfoutput>
  • </p>

The results surprised me a bit. I has always assumed that ColdFusion query of queries did not / could not handle NULL values. But, point of fact, they do. They handle them correctly. I had also tested (above) to see if the NULL comparison would work on empty strings. It did not. Everything seems to work exactly as you would expect it to work in a regular SQL query.

Furthermore, when I get the meta data of the outputted "NULL" value, it is of type java.lang.String. This is why it comes across as an empty string. There is obviously a JavaCast() happening somewhere under the hood. I have to say, I am fairly impressed with the ColdFusion query of queries. I have been using them for like 3 years and they have dramatically improved my life... snaps for ColdFusion!



Reader Comments

Jarek just pointed this out to me:

Very interesting test you made :) But I must surprise you :D I have done test on CFMX 7.0.1 with latest hotfix, and 7.0.2. The results differ! On CFMX 7.0.1 with latest hotfix it doesnt work:

query qTestNULL ( null_value IS NULL ) returns 0 records LOL ?!?
rest of queries return 1 record.
however on 7.0.2 it is working fine (also on 7.0.1 without a hotfix) .....

Reply to this Comment

Ben,

I've run into an issue dealing with these empty strings and your POI Utility.

I'm building a query in memory of bad data from a spreadsheet. This bad data is basically just columns of empty data.

The problem is, my bad data query builds rows of entire empty strings. Then I try and write to an excel file and it bombs out due to the empty strings. I get this error:

[Table (rows 3 columns Row, instructorid, lastname, firstname): [Row: coldfusion.sql.QueryColumn@81ffa2] [instructorid: coldfusion.sql.QueryColumn@b5eb18] [lastname: coldfusion.sql.QueryColumn@15d8a90] [firstname: coldfusion.sql.QueryColumn@33faca] ] is not indexable by firstname

So I'm trying to figure out how to get rid of these total empty string rows. Any ideas?

Thanks much,
Will

Reply to this Comment

@Will,

That error means that the query does not have the column "firstname" in it. I get this when I accidentally put a space in front or after the column name:

" firstname"

... or ...

"firstname "

That way, in the debugging it looks fine, the space actually makes it a different character.

Reply to this Comment

I think another situation can cause it. I tested a spreadsheet with one row of data.

For some reason, it's reading 3 additional rows and spitting out those 3 rows of empty strings in my bad data query.

Strange!

Reply to this Comment

@Will,

When it comes to Excel, I don't know quite how you can differentiate between a NULL row and an empty row. They are different things, but Excel stores this internally somehow. Like, in Excel (the application) can you NULL a cell? Or can you merely make its value empty?

Reply to this Comment

Heck, I dunno dude. But a user tried uploading a typical spreadsheet and it bombed out on me.

After testing, I noticed my bad data query was building those rows of empty strings.

If you upload a different spreadsheet, all is fine.

Somethin weird is up with the spreadsheet I guess and I can't quite pinpoint it.

Thanks much,
Will

Reply to this Comment

If you want, you can email the XLS file to me and I can take a look. It might very well be a bug in the POI Utility.cfc (ben [ at ] bennadel [ dot ] com).

Reply to this Comment

I have recently gone through a problem with query of query in Coldfusion!

I am doing this in a query of query:
---------------------------

  • SELECT * FROM vehicles
  • WHERE (vehicleDescription <> '' OR vehicleDescription IS NOT NULL)

---------------------------
Problem is that I am also getting null values with [empty_string]

Please help out!!!

Reply to this Comment

You want to change that OR to an AND. If vehicleDescription is null, it is not an empty string and if it is an empty string, it is not null.

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.