Skip to main content
nikos101
nikos101
Inspiring
January 25, 2012
Answered

possiblilities for null= in <cfqueryparam

  • January 25, 2012
  • 4 replies
  • 2206 views

why can't I do this:

<cfqueryparam value="#structFromParsedXML.ClientID#" cfsqltype="cf_sql_varchar" null="not structKeyExists(structFromParsedXML, 'ClientID')"

whats the best workaround for this?

This topic has been closed for replies.
Correct answer BKBK

nikos101 wrote:

why can't I do this:

<cfqueryparam value="#structFromParsedXML.ClientID#" cfsqltype="cf_sql_varchar" null="not structKeyExists(structFromParsedXML, 'ClientID')"

whats the best workaround for this?

Actually, the logic you wish to apply is a bit subtle. If the key ClientID does not exist in the struct, then you want the value to be NULL. ColdFusion would then ignore the value attribute, whatever it is.

However, I do believe you could improve the code logic by doing something like

Outside cfquery tag:

<cfset isNullValue = true>

<cfset id          = "">

<cfif structKeyExists(structFromParsedXML, 'ClientID')>

    <cfset isNullValue = false>

    <cfset id     = structFromParsedXML.ClientID>

</cfif>

Within cfquery tag:

<cfqueryparam value="#id#" cfsqltype="cf_sql_varchar" null="#isNullValue#">

4 replies

BKBK
Community Expert
BKBKCommunity ExpertCorrect answer
Community Expert
January 26, 2012

nikos101 wrote:

why can't I do this:

<cfqueryparam value="#structFromParsedXML.ClientID#" cfsqltype="cf_sql_varchar" null="not structKeyExists(structFromParsedXML, 'ClientID')"

whats the best workaround for this?

Actually, the logic you wish to apply is a bit subtle. If the key ClientID does not exist in the struct, then you want the value to be NULL. ColdFusion would then ignore the value attribute, whatever it is.

However, I do believe you could improve the code logic by doing something like

Outside cfquery tag:

<cfset isNullValue = true>

<cfset id          = "">

<cfif structKeyExists(structFromParsedXML, 'ClientID')>

    <cfset isNullValue = false>

    <cfset id     = structFromParsedXML.ClientID>

</cfif>

Within cfquery tag:

<cfqueryparam value="#id#" cfsqltype="cf_sql_varchar" null="#isNullValue#">

nikos101
nikos101Author
Inspiring
January 27, 2012

thanks guys, love the answers, I'll implement bkbk's

S
Steve Sommers
Legend
January 25, 2012

While the parameter must exists, you can do logic like this (I use it all the time):

<cfparam name="structFromParsedXML.ClientID" default="" />

<cfquery...>

  ...

  clientID = <cfqueryparam value="#structFromParsedXML.ClientID#" cfsqltype="cf_sql_varchar" null="#evaluate('NOT len(structFromParsedXML.ClientID)')#" />

</cfquery>

ilssac
ilssac
Inspiring
January 25, 2012

Just a quick point that you should not need to use the evalute function in this case.

<cfqueryparam value="#structFromParsedXML.ClientID#" cfsqltype="cf_sql_varchar" null="#NOT len(structFromParsedXML.ClientID)#" />

Should work just fine.

A
Adam Cameron.
Inspiring
January 25, 2012

why can't I do this:

<cfqueryparam value="#structFromParsedXML.ClientID#" cfsqltype="cf_sql_varchar" null="not structKeyExists(structFromParsedXML, 'ClientID')"

Two reasons:

1) the expression you have in your NULL attribute is a string.  It needs to be boolean.

2) all the attribute values for a tag need to be defined.  So if your null condition was true, your VALUE value would be invalid.

You need to wrap your <cfqueryparam> tag in an if/else block which checks the key exists, and either use it or send just a null.

--

Adam

Owainnorth
Owainnorth
Inspiring
January 25, 2012

You can do that. What's the problem?

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices