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

Currently Being Moderated

1. Dan Bracuk,

Jun 22, 2012 9:45 AM in reply to Khovel

Report

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

|

Mark as:

Currently Being Moderated

2. Khovel,

Jun 22, 2012 9:57 AM in reply to Dan Bracuk

Report

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

|

Mark as:

Currently Being Moderated

3. Peter Freitag,

Jun 22, 2012 11:59 AM in reply to Khovel

Report

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

4. Khovel,

Jun 22, 2012 12:14 PM in reply to Peter Freitag

Report

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.

|

Mark as:

Currently Being Moderated

5. Upen@Roul,

Jun 29, 2012 4:40 AM in reply to Khovel

Report

This post may help you...

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

|

Mark as:

Currently Being Moderated

6. Khovel,

Jun 29, 2012 4:49 AM in reply to Upen@Roul

Report

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

|

Mark as:

Currently Being Moderated

7. Dan Bracuk,

Jun 29, 2012 5:27 AM in reply to Khovel

Report

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

8. Upen@Roul,

Jun 30, 2012 12:17 AM in reply to Khovel

Report

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

9. Adam Cameron.,

Jun 30, 2012 12:34 AM in reply to Upen@Roul

Report

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

10. BKBK,

Jun 30, 2012 5:39 AM in reply to Khovel

Report

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>

|

Mark as:

Currently Being Moderated

11. itisdesign,

Jun 30, 2012 3:25 PM in reply to Khovel

Report

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

12. itisdesign,

Jun 30, 2012 3:53 PM in reply to itisdesign

Report

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

|

Mark as:

Currently Being Moderated

13. Dan Bracuk,

Jun 30, 2012 7:12 PM in reply to itisdesign

Report

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

14. itisdesign,

Jun 30, 2012 7:52 PM in reply to Dan Bracuk

Report

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:

ColdFusion

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

Jun 22, 2012 8:49 AM

More Like This

Bookmarked By (0)

Answers + Points = Status