Adobe ColdFusion Returns NULL Dates As NULL When Using CFQuery ReturnType "Array"
Historically in ColdFusion, when you return a NULL date from the database, the CFQuery tag translates that NULL date as [empty string]. This has always made it quite easy to manage dates in the application logic because all you need to do is pass the value into the isDate() decision function before you use it. In recent years, the CFQuery tag has added a returnType attribute that allows the database recordset to be returned as either an Array-of-Structs or a Column-based Struct (Lucee CFML only). Unfortunately, when returning the query as an Array, Adobe ColdFusion no longer translates NULL dates into empty-strings.
To see this in action, I'm going to hard-code a SELECT SQL statement that returns NULL for a "date column". Then, we'll output the result and try to access said date:
<!--- NOTE: We're hard-coding a NULL date and returning an Array. --->
<cfquery name="data" returnType="array" datasource="testing">
SELECT
( 1 ) AS id,
( 'Planet Fitness' ) AS name,
( NULL ) AS lastUsedAt
;
</cfquery>
<cfoutput>
<p>
#server.coldfusion.productName# :
#server.coldfusion.productVersion#
</p>
<cfdump
var="#data.first()#"
label="ReturnType: Array"
/>
<!---
Historically, when returning a NULL-date from the database, ColdFusion will
translate the value as an empty-string, making it very easy to work with. When
returning the query as an ARRAY, however, Adobe ColdFusion seems to translate the
NULL-date as a NULL-value (not the empty string). As such, attempting to reference
it will result in a null-reference error (NRE).
--->
<cftry>
<p>
Last Used: #data.first().lastUsedAt#
</p>
<cfcatch>
<p>
Error: #cfcatch.message#
</p>
</cfcatch>
</cftry>
</cfoutput>
As you can see, we're CFDumping-out the value and then trying to reference the lastUsedAt value (which we know to be NULL). And, when we run this in both Adobe ColdFusion (2021 and 2023) and Lucee CFML, we get the following output:
As you can see, Lucee CFML returns the NULL date value as the empty string (as we would assume given ColdFusion's historical behavior). Adobe ColdFusion, on the other hand, returns it as NULL, which means that the resultant struct contained an undefined key. Which is why we get an error when go to output the NULL date value.
Personally, I prefer the Lucee CFML behavior. It is much more in alignment with how the CFQuery tag has worked historically. If one were to enable Full-Null Support in the CFML engine, I would understand the Adobe ColdFusion (ACF) response; but, without said setting enabled, the ACF behavior adds unnecessary friction to the consumption workflow.
Want to use code from this post? Check out the license.
Reader Comments
We have SO much code that is bascally:
<cfif structKeyExists(local.thing, "key") AND len(trim(local.thing.key)) GT 0>Like...SO much. I almost wish we had a base.cfc function called
hasValue(struct, key).@Will,
I feel your pain! With recent releases of ColdFusion, at the very least, we can use the Elvis operator (
?:) to set some fallback to make some things easier. I just wrote code today like:It doesn't always make sense (and Adobe ColdFusion has some strange rules around it); but, it has helped to simplify some workflows.
Looks like someone filed a bug for this in the Adobe Bug Tracker back in March: CF-4217379
Post A Comment — ❤️ I'd Love To Hear From You! ❤️
Post a Comment →