Welcome Dialog

Welcome to the Community!

We have a brand new look! Take a tour with us and explore the latest updates on Adobe Support Community.


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

New Here ,
Jun 22, 2012 Jun 22, 2012

Copy link to clipboard

Copied

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.

Views

3.8K

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jun 22, 2012 Jun 22, 2012

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jun 22, 2012 Jun 22, 2012

Copy link to clipboard

Copied

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'/>

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jun 22, 2012 Jun 22, 2012

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jun 22, 2012 Jun 22, 2012

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jun 29, 2012 Jun 29, 2012

Copy link to clipboard

Copied

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jun 29, 2012 Jun 29, 2012

Copy link to clipboard

Copied

did you even read the original post i have? The null property of <cfqueryparam>, REGUARDLESS of its value (yes, no, 1, 0, true, false) DOES NOT SUBSTITUTE

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

for its NULL equivalent in the case of null being true, 1, or yes.

It ends up being

AND NAME_FIELD = ''

which is not the same thing as inserting NULL

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jun 29, 2012 Jun 29, 2012

Copy link to clipboard

Copied

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 = ''

"

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jun 30, 2012 Jun 30, 2012

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jun 30, 2012 Jun 30, 2012

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Jun 30, 2012 Jun 30, 2012

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Jun 30, 2012 Jun 30, 2012

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Jun 30, 2012 Jun 30, 2012

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jun 30, 2012 Jun 30, 2012

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Jun 30, 2012 Jun 30, 2012

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Feb 16, 2015 Feb 16, 2015

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Feb 16, 2015 Feb 16, 2015

Copy link to clipboard

Copied

@Lseymore

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Feb 16, 2015 Feb 16, 2015

Copy link to clipboard

Copied

@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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Feb 18, 2015 Feb 18, 2015

Copy link to clipboard

Copied

lseymore wrote:

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.

More work? No, this solution has fewer lines of code and fewer function calls, and so involves less work than the one you suggested before!

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Feb 18, 2015 Feb 18, 2015

Copy link to clipboard

Copied

This is why I don't post on forums

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Feb 18, 2015 Feb 18, 2015

Copy link to clipboard

Copied

LATEST

Please expand.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines