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>

Leave a Comment

Comments

  • Jordan Clark | September 15, 2006

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

  • Falcon79 | September 15, 2006

    mmm.. but in cfmx7 it doesn’t work?

  • Ben Nadel | September 15, 2006

    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.

  • Mark | September 18, 2006

    Jordan –

    Yup, that would also work too…

  • Marc | September 19, 2006

    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.

  • Ben Lee | June 2, 2009

    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.

  • Florian | August 8, 2014

    This may be applicable to some databases, but in Oracle there is no distinction between an empty string and null. They are one and the same. As such CF’s lack of distinction is accurate.

    select * from dual where ” is null;

    DUMMY
    —–
    X