Skip to main content
K
Khovel
Participant
June 22, 2012
Question

Problem with <cfqueryparam null="#ListFindNoCase('NULL',argument.name)#">

  • June 22, 2012
  • 3 replies
  • 4824 views

This is my query i'm doing here:

         SELECT *

         FROM DB_NAME

         WHERE 0=0

            <cfif arguments.NAME is not ''>

               AND NAME_FIELD = <cfqueryparam value="#arguments.NAME#"  null='#ListFindNoCase("NULL",arguments.NAME)#'/>

            </cfif>

      </cfquery>

The resulting query ends up being

SELECT *

FROM DB_NAME

WHERE 0=0

AND NAME_FIELD = ''

instead of being

SELECT *

FROM DB_NAME

WHERE 0=0

AND NAME_FIELD is NULL

I am currently working in Coldfusion 9.

    This topic has been closed for replies.

    3 replies

    L
    lseymore
    Participant
    February 16, 2015

    Hello,

    I've ran into a similar problem trying to use NULL properties, which I which ColdFusion supported. NULL is a useful thing, javacast('null','') is a pain!

    Anyway, see if this solves the actual problem you are having, which I understand is to support NULL values.

    The idea is to have a helper function "getNullableProperty" like follows:

    <cffunction name="getNullableProperty" returntype="any">

      <cfargument name="object" type="struct"/>

      <cfargument name="prop" type="string"/>

      <cfif structKeyExists(object, prop)>

      <cfreturn object[prop]/>

      <cfelse>

      <cfreturn javacast("null", "")/>

      </cfif>

    </cffunction>

    And everywhere you have a property that could possibly be NULL, call it like so:

    CONFIG = <cfqueryparam cfsqltype="cf_sql_varchar" value="#getNullableProperty(candidateSelection, 'CONFIG')#"/>

    Hope this helps

    Regards,

    Leonard

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    February 16, 2015

    @Lseymore

    Why go to all the trouble of calling a function when cfqueryparam has an attribute to represent NULL?

    L
    lseymore
    Participant
    February 16, 2015

    @BKBK

    Well, the alternative is to write something like this

    <cfqueryparam cfsqltype="cf_sql_varchar" null="#structKeyExists(candidateSelection, 'CONFIG')#" value="#candidateSelection.CONFIG#"/>

    Which in my mind at least is a more work and looks like duplicated code. First it has to check if the property exists then use the property.

    Ideally CF should support NULL properties so that you could simply write:

    <cfqueryparam cfsqltype="cf_sql_varchar" value="#candidateSelection.CONFIG#"/>

    Regards,

    Leonard

    itisdesign
    itisdesign
    Inspiring
    June 30, 2012

    Khovel wrote:

    [...]

    The resulting query ends up being

    SELECT *

    FROM DB_NAME

    WHERE 0=0

    AND NAME_FIELD = ''

    instead of being

    SELECT *

    FROM DB_NAME

    WHERE 0=0

    AND NAME_FIELD is NULL

    I am currently working in Coldfusion 9.

    Hi Khovel,

    If you enable profiling in your database server, I believe you will find it actually becomes:

    SELECT *

    FROM DB_NAME

    WHERE 0=0

    AND NAME_FIELD = NULL

    And 0 rows will be selected. (tested w/ CF10 and MSSQL Server 2008 R2)  B/c, in a SELECT, cfqueryparam's null="1|yes|true" replaces the cfqueryparam tag w/ NULL and the '=' is not changed to 'IS'.

    If you are cfdumping cfquery's result (ex: <cfquery result="r") struct, CF displays NULL sqlParameters as [empty string].  Best to enable profiling in the db server, to see what is actually sent.

    Thanks,

    -Aaron

    itisdesign
    itisdesign
    Inspiring
    June 30, 2012

    itisdesign wrote:

    AND NAME_FIELD = NULL

    And 0 rows will be selected.

    Except w/in a QoQ, which disallows this.  Examples (CF10 syntax):

    Example 1:

    <cfset q = queryNew("col1,col2", "integer,varchar", [[1,''],[2,javaCast("null", "")]]) />

    <cfquery name="q" dbtype="query">

    SELECT * FROM q WHERE col2 = NULL

    </cfquery>

    <cfdump var="#q#" />

    Example 2: replace '= NULL' w/ '<cfqueryparam null="1" value="ignored" />'

    Example 3: replace '= NULL' w/ '<cfqueryparam null="0" value="#javaCast('null', '')#" />'

    All throw:

    Query Of Queries syntax error.
      Encountered "col2 = NULL.  Incorrect conditional expression,  Expected one of [like|null|between|in|comparison] condition,

    Thanks,

    -Aaron

    D
    Dan_Bracuk
    Inspiring
    July 1, 2012

    Regarding

    All throw:

    Query Of Queries syntax error.
      Encountered "col2 = NULL.  Incorrect conditional expression,  Expected one of [like|null|between|in|comparison] condition,

    Of course it throws an error.  It's "is null" not "= null".

    D
    Dan_Bracuk
    Inspiring
    June 22, 2012

    What was the value of argument.name?  What does the listfindnocase function return for that value?

    K
    KhovelAuthor
    Participant
    June 22, 2012

    ListFindNoCase("NULL",arguments.NAME) returns 0 if "NULL" is not in the string of arguments.NAME.

    Otherwise, i believe it returns the position in which the string was found, but not 100% on that part.

    So for sake of argument. I am passing arguments.NAME = "NULL"

    So in short, here is what it should be.

    ListFindNoCase("NULL",arguments.NAME) should return 1 since arguments.NAME = "NULL"

    Thus

    AND NAME_FIELD = <cfqueryparam value="#arguments.NAME#"  null='#ListFindNoCase("NULL",arguments.NAME)#'/>

    becomes

    AND NAME_FIELD = <cfqueryparam value="#arguments.NAME#"  null='1'/>

    pete_freitag
    pete_freitag
    Participating Frequently
    June 22, 2012

    Why not just do <cfif arguments.name IS "NULL">IS NULL<cfelse> = <cfqueryparam value="#arguments.name#"></ciff>

    Also ListFindNoCase is probably not the correct function to use here, that is for dealing with lists, FindNoCase would be more appropriate... but that too may have an issue if the persons name is Nully it would still match.

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