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.
Copy link to clipboard
Copied
What was the value of argument.name? What does the listfindnocase function return for that value?
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'/>
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.
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.
Copy link to clipboard
Copied
This post may help you...
http://coldfusion-tip.blogspot.com/2011/09/insert-null-values-into-data-base-using.html
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
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 = ''
"
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.
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
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>
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
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
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".
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
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
Copy link to clipboard
Copied
@Lseymore
Why go to all the trouble of calling a function when cfqueryparam has an attribute to represent NULL?
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
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!
Copy link to clipboard
Copied
This is why I don't post on forums
Copy link to clipboard
Copied
Please expand.