possiblilities for null= in <cfqueryparam

[nikos101]

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?

[Owainnorth]

You can do that. What's the problem?

[Adam Cameron.]

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

[Steve Sommers]

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]

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.

[BKBK · Accepted solution]

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]

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