Skip to main content
Ben Nadel at CFUNITED 2009 (Lansdowne, VA) with: Phill Nacelli and Amy Nacelli
Ben Nadel at CFUNITED 2009 (Lansdowne, VA) with: Phill Nacelli Amy Nacelli

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

By
Published in , Comments (11)

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!

Want to use code from this post? Check out the license.

Reader Comments

15,822 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) .....

25 Comments

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

15,822 Comments

@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.

25 Comments

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!

15,822 Comments

@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?

25 Comments

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

15,822 Comments

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).

1 Comments

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

9 Comments

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.

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel