ColdFusion Query of Queries: Uses NULL Values, Returns Empty Strings
Posted June 30, 2006 at 1:36 PM by Ben Nadel
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) .....
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
@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.
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!
@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?
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
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).
Already sent it. Thanks!
And I doubt it's a bug. It usually turns out being me. :)
Ha ha, I will check it out.
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!!!
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.
