6 Replies Latest reply on Jan 27, 2012 1:33 AM by nikos101

    possiblilities for null=  in <cfqueryparam

    nikos101 Level 2

      why can't I do this:

       

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

       

      whats the best workaround for this?

        • 1. Re: possiblilities for null=  in <cfqueryparam
          Owain North Level 4

          You can do that. What's the problem?

          • 2. Re: possiblilities for null=  in <cfqueryparam
            Adam Cameron. Level 5

            why can't I do this:

             

            <cfqueryparam value="#structFromParsedXML[d].ClientID#" cfsqltype="cf_sql_varchar" null="not structKeyExists(structFromParsedXML[d], '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

            1 person found this helpful
            • 3. Re: possiblilities for null=  in <cfqueryparam
              Steve Sommers Level 4

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

               

              <cfparam name="structFromParsedXML[d].ClientID" default="" />

              <cfquery...>

                ...

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

              </cfquery>

              1 person found this helpful
              • 4. Re: possiblilities for null=  in <cfqueryparam
                ilssac Level 5

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

                 

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

                 

                Should work just fine.

                1 person found this helpful
                • 5. Re: possiblilities for null=  in <cfqueryparam
                  BKBK Adobe Community Professional & MVP

                  nikos101 wrote:

                   

                  why can't I do this:

                   

                  <cfqueryparam value="#structFromParsedXML[d].ClientID#" cfsqltype="cf_sql_varchar" null="not structKeyExists(structFromParsedXML[d], '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[d], 'ClientID')>

                      <cfset isNullValue = false>

                      <cfset id     = structFromParsedXML[d].ClientID>

                  </cfif>

                   

                   

                  Within cfquery tag:

                   

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

                  • 6. Re: possiblilities for null=  in <cfqueryparam
                    nikos101 Level 2

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