[Looking for Charlie's main web site?]

Did you know about the NULL attribute of CFQUERYPARAM? I didn't, until today.

Note: This blog post is from 2007. Some content may be outdated--though not necessarily. Same with links and subsequent comments from myself or others. Corrections are welcome, in the comments. And I may revise the content as necessary.
Will the riches of CFML ever cease to amaze me? :-) Did you know about the NULL attribute of CFQUERYPARAM? I didn't, until today. Consider that you use CFQUERYPARAM and point its VALUE to a variable. What would happen if the value was empty, but your database required a null? How would you solve this? If you didn't know better, you might use an IF test to say "if it's null, use a null, else use the variable", but there a much more elegant solution.

Did you know that CFQUERYPARAM has a NULL attribute that is just for this purpose? It takes a boolean to indicate whether and when to use a NULL rather than the VALUE.

It's not new, having been around since 4.5 according to the CFML language history file. I've just never noticed it before. I learned about it today from a couple of folks on the great CFAUSSIE list.

Now, to be honest, the docs (CFML Reference for the tag) just don't make it as clear as it could be, I don't think (otherwise I'd like to think I'd have noticed it before). I don't see any mention of it in the Developer's Guide, for instance.

Anyway, you can read more about it in a blog entry from Michael Sharman, who I see also just happened to blog about it last month, with a lot more detail:

http://www.chapter31.com/2007/02/04/cfqueryparam-and-conditional-handling-of-nulls/

You might also want to read the comments there as well as at the CFMX 7 docs for the tag, both of which have people sharing their experiences using the tag, over time.

For more content like this from Charlie Arehart: Need more help with problems?
  • If you may prefer direct help, rather than digging around here/elsewhere or via comments, he can help via his online consulting services
  • See that page for more on how he can help a) over the web, safely and securely, b) usually very quickly, c) teaching you along the way, and d) with satisfaction guaranteed
Comments
i knew about it thanks to an old article written by the inimitable Dave Watts.

http://www.adobe.com...

towards the bottom he illustrates how to use it instead of using <cfif> statements in the SQL to test for empty strings.

great article all around, and one that to this day i still point people towards when they have <cfqueryparam> questions.
# Posted By charlie griefer | 3/5/07 4:58 PM
Yeah, I recently found out about the NULL attribute also. Rocked my world something sweet! The LIST attribute is another winner.
# Posted By Ben Nadel | 3/5/07 5:13 PM
I've always been aware of this, however, there's still one "gotcha" that's always irritated me. According to the docs, if you specify a null value of true, then the "value" attribute is ignored. The gotcha is that the "value" attribute's value isn't completey ignored. Take this example:

<cfqueryparam value="#someValue#" null="#not isDefined(someValue)#">

the thought being that if #someValue# is defined, use it, otherwise use null. Yet, you'll still get an error saying someValue is not defined even though null returns true.
# Posted By Devin | 3/5/07 6:58 PM
Been using it for years and in my opinion this is the easiest way:

<cfqueryparam value="#form.headline#" cfsqltype="CF_SQL_VARCHAR" maxlength="255" null="#IIF(variables.myvar EQ "", true, false)#">

no need to use DE() around the values. Don't know why though :P
# Posted By tony petruzzi | 3/5/07 7:48 PM
Screwed up the code in my last comment:

<cfqueryparam value="#form.headline#" cfsqltype="CF_SQL_VARCHAR" maxlength="255" null="#IIF(form.headline EQ "", true, false)#">
# Posted By Tony Petruzzi | 3/5/07 7:49 PM
null="#not len(myvar)#" is a big more concise and solves the same issue if you want to avoid the iif() performance issues.
# Posted By Devin | 3/5/07 8:03 PM
I have been wanting to see a NULL="AUTO" option for years
# Posted By zac spitzer | 3/5/07 8:23 PM
Ben, I'd noticed Dave's article too, and thought about mentioning it, especially if it was much older than the blog entry I'd pointed to, but since I couldn't find a date for it. I tried for several minutes to confirm that (looking in the wayback machine, etc.), but since the names of files at Adobe/MM/Allaire have changed, it just wasn't proving fruitful. And since it didn't offer more than that entry did, I decided not to mention it. Glad you have. Sounds like you're confirming that it's been around for a while. Dave deserves credit for so much that he's done over the years.

As for those pointing out where it's failing, again, I pointed to the comments in the docs and the blog entry, where others have debated this. I've not looked into it completely, but it seemed some were indicating that things had changed over time. Perhaps updaters or hotfixes have addressed this. I'll leave it to others to explore and report.

/charlie
# Posted By Charlie Arehart | 3/5/07 8:48 PM
hey charlie...

unfortunately, i don't know the exact date of Dave's article either, but i can tell you that i made reference to it in a post on easycfm.com that's dated aug 25, 2004 (http://www.easycfm.c... so it's certainly older than a blog entry made last month :)

i'd wager that it had been around for at least a couple of years before that.
# Posted By charlie griefer | 3/5/07 9:05 PM
link got messed up :\

http://www.easycfm.c...

also, to address those pointing out where it's failing... i'm not sure anyone is saying it's failing, but rather taking issue with the behavior. the docs do state that a TRUE value for the null attribute will cause CF to ignore the value attribute, but that's not entirely true. it will ignore it in that it will use the NULL value (as opposed to the "value" value)... but if the "value" value does not exist, it will still try to evaluate it and throw an error.

it might have been more accurate for the docs to state that using the null attribute will OVERRIDE the value attribute, rather than stating it will ignore it.
# Posted By charlie griefer | 3/5/07 9:12 PM
Thanks for the clarifications, Charlie G, and all due props to Dave for his article.

Now, as for the "troublesome behavior", that's all I meant by "failing". Now, have you read all the comments in both places I pointed to, because there seemed to be some change in sentiment over time. I didn't read it closely. I'm just pointing it out for folks to consider.

And if anyone reports they do still see the "problem", can you report (for those interested) if you're running on CFMX 7.02 with the "cumulative hotfix 1"? Just seems fair to indicate if the latest and greatest version still has the issue. (Again, no, I've not read the release notes to see if they even bother to address it. Just trying to help those interested.)
# Posted By Charlie Arehart | 3/5/07 9:47 PM
The "troublesome" behavior really does exactly what it should do. It doesn't say it won't evaluate the value - it justs says it will ignore the value.

It would be weird, to me, if it conditionally didn't evaluate different parameters based on some other parameter because the evaulation takes place in one level of the processing and then the execution logic takes place in another part.
# Posted By Bill | 3/16/07 12:28 PM
HOW do you insert a NULL when the form.field is blank/has no value....? because createodbc function will throw an error with a blank input param.

<CFQUERYPARAM value="#createODBCDateTime(form.field)#"
cfsqltype="cf_sql_date" NULL="#NOT len(form.field)#>

# Posted By eric | 3/24/07 9:12 PM
Eric, look at the CFPARAM tag or the IIF function. What you're asking about is not at all unique to the CFQUERYPARAM tag's NULL attribute. I'll leave it to others to elaborate further if interested.
# Posted By Charlie Arehart | 3/25/07 7:57 AM
Hey Charlie,
Great blog post! I too wasn't pleased with the documentation provided for this attribute of <cfqueryparam>. I looked around for a better description, and instantly this page came up. Thanks for taking the time to explain this neat little feature.
I probably wouldn't have even known about it if it wasn't for CFEclipse's code hinting. When I saw it pop up I asked myself, "Does this attribute do what I think it would do?" So props to Mark Drew, et. al. as well for allowing us to learn more about CF's hidden gems.
Jose
# Posted By Jose Galdamez | 12/9/08 2:12 PM
Thanks for the kind regards, Jose, and happy to have helped. :-)
# Posted By Charlie Arehart | 12/9/08 5:16 PM
Quote

"I have been wanting to see a NULL="AUTO" option for years"

that would be amazing:

SO , in the end what the best way to do this for all types of datatypes?
# Posted By Nikos | 12/10/09 5:42 AM
# Posted By zac spitzer | 12/10/09 6:49 AM
There is another issue with cfqueryparam, consider the following...
set somenumber = <cfqueryparam cfsqltype="CF_SQL_SMALLINT" value="#session.number#" null="#!isNull('session.number')#" />

If session.number is blank, they it sets "null = yes" and puts in a blank value...
set somenumber = (nothing)
which will cause your query to fail.

If you change the cfqueryparam to varchar...
cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#session.number#" null="#!isNull('session.number')#" />

... then it puts empty quotes into you query
set somenumber = ''


This works great, but now I have my cfqueryparam set to varchar and it will now accept non-numeric values. Seems like a security issue to me.

I could put a conditional statement around the cfsqltype too, but damn this is getting to be a lot of extra coding to make this tag work.
# Posted By Josh | 5/7/10 3:05 PM
Thanks for sharing, Josh. Fair point. I've not dealt with it. Have you filed a bug report about that? More at http://www.carehart....
# Posted By charlie arehart | 5/8/10 6:44 PM
Copyright ©2020 Charlie Arehart
Carehart Logo
BlogCFC was created by Raymond Camden. This blog is running version 5.005.
(Want to validate the html in this page?)

Managed Hosting Services provided by
Managed Dedicated Hosting