Traditionally in ColdFusion, once you return a NULL date from the database, the CFQuery tag interprets that NULL date as [empty string]. This has at all times made it fairly straightforward to handle dates within the utility logic as a result of all it’s worthwhile to do is move the worth into the isDate() choice operate earlier than you utilize it. In recent times, the CFQuery tag has added a returnType attribute that permits the database recordset to be returned as both an Array-of-Structs or a Column-based Struct (Lucee CFML solely). Sadly, when returning the question as an Array, Adobe ColdFusion now not interprets NULL dates into empty-strings.
To see this in motion, I will hard-code a SELECT SQL assertion that returns NULL for a “date column”. Then, we’ll output the consequence and attempt to entry mentioned date:
<!--- NOTE: We're hard-coding a NULL date and returning an Array. --->
<cfquery title="information" returnType="array" datasource="testing">
SELECT
( 1 ) AS id,
( 'Planet Health' ) AS title,
( NULL ) AS lastUsedAt
;
</cfquery>
<cfoutput>
<p>
#server.coldfusion.productName# :
#server.coldfusion.productVersion#
</p>
<cfdump
var="#information.first()#"
label="ReturnType: Array"
/>
<!---
Traditionally, when returning a NULL-date from the database, ColdFusion will
translate the worth as an empty-string, making it very straightforward to work with. When
returning the question as an ARRAY, nonetheless, Adobe ColdFusion appears to translate the
NULL-date as a NULL-value (not the empty string). As such, trying to reference
it'll lead to a null-reference error (NRE).
--->
<cftry>
<p>
Final Used: #information.first().lastUsedAt#
</p>
<cfcatch>
<p>
Error: #cfcatch.message#
</p>
</cfcatch>
</cftry>
</cfoutput>
As you’ll be able to see, we’re CFDumping-out the worth after which making an attempt to reference the lastUsedAt worth (which we all know to be NULL). And, after we run this in each Adobe ColdFusion (2021 and 2023) and Lucee CFML, we get the next output:
As you’ll be able to see, Lucee CFML returns the NULL date worth because the empty string (as we might assume given ColdFusion’s historic conduct). Adobe ColdFusion, however, returns it as NULL, which implies that the resultant struct contained an undefined key. Which is why we get an error when go to output the NULL date worth.
Personally, I desire the Lucee CFML conduct. It’s rather more in alignment with how the CFQuery tag has labored traditionally. If one have been to allow Full-Null Assist within the CFML engine, I might perceive the Adobe ColdFusion (ACF) response; however, with out mentioned setting enabled, the ACF conduct provides pointless friction to the consumption workflow.
Need to use code from this put up?
Try the license.
