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

Report · Jun 22, 2012

This is my query i'm doing here:

SELECT *

FROM DB_NAME

WHERE 0=0

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.

Report · Jun 22, 2012

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

Report · Jun 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'/>

Report · Jun 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.

Report · Jun 22, 2012

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.

Report · Jun 29, 2012

This post may help you...

http://coldfusion-tip.blogspot.com/2011/09/insert-null-values-into-data-base-using.html

Report · Jun 29, 2012

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

Report · Jun 29, 2012

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

"

Report · Jun 30, 2012

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.

Report · Jun 30, 2012

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

Report · Jun 30, 2012

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

SELECT *

FROM DB_NAME

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

</cfquery>

Report · Jun 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

Report · Jun 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:

SELECT * FROM q WHERE col2 = NULL

</cfquery>

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

Report · Jun 30, 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".

Report · Jun 30, 2012

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

Report · Feb 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:

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

Report · Feb 16, 2015

@Lseymore

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

Report · Feb 16, 2015

@BKBK

Well, the alternative is to write something like this

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:

Regards,

Leonard

Report · Feb 18, 2015

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!

Report · Feb 18, 2015

This is why I don't post on forums

Report · Feb 18, 2015

Please expand.