CODEWITHSUNDEEP
sqlcoldfusionnullcfmlcfquery
Konstantin Schütte
Konstantin Schütte

Reputation: 999

Query null values

I have the column deletedTime in my instances table. Now I want to check in ColdFusion if the value is null. There is only one record to be printed out with the query. IsNull(test) is returning the correct value, but IsNull(searchForDeletedInstances.deletedTime) returns false but the value is in the datatable null..

<cfquery datasource="hostmanager" name="searchForDeletedInstances">
SELECT deletedTime
FROM instances
WHERE instanceId = <cfqueryparam value="#instanceId#" 
cfsqltype="CF_SQL_NVARCHAR">
</cfquery>

<cfloop query="searchForDeletedInstances">
    <cfset test= #deletedTime#>
</cfloop>

<cfreturn IsNull(test)>
<cfreturn IsNull(searchForDeletedInstances.deletedTime)>

Upvotes: 1

Views: 697

Answers (3)

Scott Jibben
Scott Jibben

Reputation: 2287

ColdFusion 2018 does allow for NULL values but the server/site has to be configured to use it. This may break a lot of older code. ColdFusion before 2018 will use an empty string for NULL values.

I would change the query to only look for NULL deletedTime records. Then use the searchForDeletedInstances.recordCount value to determine if a NULL record was found. The code would look something like this:

<cfquery datasource="hostmanager" name="searchForDeletedInstances">
SELECT deletedTime
FROM instances
WHERE instanceId = <cfqueryparam value="#instanceId#" cfsqltype="CF_SQL_NVARCHAR">
AND deletedTime IS NULL
</cfquery>

<cfset nullRecord = searchForDeletedInstances.recordCount ? true : false>

<cfreturn nullRecord>

Upvotes: 3

James A Mohler
James A Mohler

Reputation: 11120

I would do the test in the query.

<cfquery datasource="hostmanager" name="searchForDeletedInstances">
SELECT deletedTime
FROM instances
WHERE instanceId = <cfqueryparam value="#instanceId#" cfsqltype="CF_SQL_NVARCHAR">
AND deletedTime IS NULL
</cfquery>

<cfreturn BooleanFormat(searchForDeletedInstances.recordcount)>

Upvotes: 3

Redtopia
Redtopia

Reputation: 5237

Null values from a query come back as empty strings. Test for IsDate() instead. You can always use cfdump to show the contents of your query so you can see the data and data types returned.

Upvotes: 1

Related Questions