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?
- Signup to get his blog posts by email:
- Follow his blog RSS feed
- View the rest of his blog posts
- View his blog posts on the Adobe CF portal
- 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
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.
<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.
<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
<cfqueryparam value="#form.headline#" cfsqltype="CF_SQL_VARCHAR" maxlength="255" null="#IIF(form.headline EQ "", true, false)#">
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
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.
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.
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.)
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.
<CFQUERYPARAM value="#createODBCDateTime(form.field)#"
cfsqltype="cf_sql_date" NULL="#NOT len(form.field)#>
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
"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?
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.