Skip navigation
Khovel
Currently Being Moderated

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

Jun 22, 2012 8:49 AM

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.

 
Replies
  • Currently Being Moderated
    Jun 22, 2012 9:45 AM   in reply to Khovel

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

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 22, 2012 11:59 AM   in reply to Khovel

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 29, 2012 4:40 AM   in reply to Khovel
     
    |
    Mark as:
  • Currently Being Moderated
    Jun 29, 2012 5:27 AM   in reply to Khovel

    I get the advertised results when I use the null attribute.  What did you do to arrive at the following conclusion?

     

    "

    It ends up being

    AND NAME_FIELD = ''

    "

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 30, 2012 12:17 AM   in reply to Khovel

    Hi Khovel,

    Just to Confirm: You want to insert NULL values in some database fields by your condition checking.

     

    If it is the case then use YesNoFormat() function of ColdFusion to do so. Which was described in the above blog post.

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 30, 2012 12:34 AM   in reply to Upen@Roul

    Your blog post is correct except for the need for yesNoFormat(), which is unnecessary in your example, and not really what yesNoFormat() is intended for (it's for formatting boolean values for output).  I tried to add a comment to your blog to that effect, but it didn't seem to show up (maybe you need to moderate it first?)

     

    --

    Adam

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 30, 2012 5:39 AM   in reply to Khovel

    Khovel wrote:

     

    I ended up doing a <cfif> to solve for this.

    But doesn't that defeat the purpose of using the null tag in cfqueryparam?

     

    Also, it shouldn't matter what i use as a conditional case to make this work.

    You could make the logic tighter without cfif. I was thinking of something like

     

    <cfset isArgNameBlank = trim(arguments.NAME) is ''>

    <cfquery>

    SELECT *

    FROM DB_NAME

    WHERE NAME_FIELD = <cfqueryparam value="#arguments.NAME#"  null='#isArgNameBlank#'/>

    </cfquery>

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 30, 2012 3:25 PM   in reply to Khovel

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 30, 2012 3:53 PM   in reply to itisdesign

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 30, 2012 7:12 PM   in reply to itisdesign

    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".

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 30, 2012 7:52 PM   in reply to Dan Bracuk

    Dan Bracuk wrote:

     

    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".

     

    Hi Dan,

     

    Exactly.  And '= NULL' is what his code boils down to.  I was just explaining to him that it returns 0 rows, instead of an exception, in a non-QoQ query.

     

    Thanks,

    -Aaron

     

    Message was edited by: itisdesign

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points