15 September 2006 04:23 PM
CFQUERY Results: Is it NULL or is it just an Empty String?
One of the annoying things in CF is that when you query a database, all NULL values in the db actually come back as empty strings. This means that if you actually store an empty string in the database, you have no way of knowing if it was an empty string, or a NULL value.I hit this the other day when working on a database column where both an empty string and NULL values where valid data values, and had very different meanings.
That's when the light turned on, and I realized 'duh! CF is Java, so if the DB value is NULL, the Java call will return NULL, which we can check for'.
So, knowing that (a) a cfquery resultset is actually a java.sql.ResultSet, and (b) that a cfoutput / cfloop call only iterates through the result set, I can can run .getString(columnName) on it, and if the value it returns is NULL, it will return a Java null value.
So what I ended up with was the following isNull() function, that works a treat, and can tell when a real NULL value is returned, not just an empty string:
<cfquery datasource="db" name="query">
select * from table
</cfquery>
<cfoutput query="query">
#columnName# : #isNull(query, "columnName")#
</br>
</cfoutput>
<cffunction name="isNull" hint="is this null" access="public" returntype="boolean" output="false">
<cfargument name="query" hint="the current query" type="query" required="Yes">
<cfargument name="column" hint="Column Name" type="string" required="Yes">
<cfscript>
var value = arguments.query.getString(arguments.column);
if(isDefined("value"))
{
return false;
}
return true;
</cfscript>
</cffunction>
Comments
Posted by Jordan Clark on 15 September 2006 05:21 PM
Good idea, I wish this was built into CF.
Also instead of using isDefined( "value" ) it might work to replace that with just <cfreturn arguments.query.wasNull()>
Posted by Falcon79 on 15 September 2006 05:29 PM
mmm.. but in cfmx7 it doesn't work?
Posted by Ben Nadel on 15 September 2006 09:37 PM
In my experience, values outside the query are empty strings. However, when are still inside the query, they are still NULL values. Here are some posts I have made:
http://www.bennadel.com/index.cfm?dax=blog:179.view
http://www.bennadel.com/index.cfm?dax=blog:112.view
However, it seems like with thew latest hotfix:
http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=aae43964
This is no longer true. This is what it says about NULL values in query of queries:
62273
Query of Queries NULL behavior has changed. Specifiying IS NULL in a query of queries returns zero rows, even if there are NULL values in the original query.
I am not sure why they are changing it.
Posted by Mark on 18 September 2006 09:34 AM
Jordan -
Yup, that would also work too...
Posted by Marc on 19 September 2006 01:07 AM
You could use COALESCE(columnName, value to set to if NULL) in your query if you know you want both empty string and NULLs to be the same.
SELECT COALESCE(columnName, '') AS string
FROM table
That way the SQL Server does the work and not CF.
Posted by Ben Lee on 02 June 2009 01:53 AM
Slow come to bumping into this problem. This really is deeper than just a couple of empty string. The Null value can be used in int/date and other data types, and does have a meaning in DB representation. An empty string is not going to solve any real problem.