NULL attribute of CFQUERYPARAM

[Guest]

I'm not sure if I'm using the NULL attribute of cfqueryparam correctly.

I already know that I just need to get records where the status is NULL. In the DB this column is set to allow NULL

So I can do this:

<cfquery name="aa" datasource="#application.dsn#>

SELECT column1, column2

FROM mytable

Where status IS NULL

</cfquery>

When using cfqueryparam, can I do it this way:

<cfquery name="aa" datasource="#application.dsn#>

SELECT column1, column2

FROM mytable

Where status = <cfqueryparam cfsqltype="cf_sql_varchar" value=" " NULL="yes">

</cfquery>

Please advice

[Miguel-F]

Yes, I believe that should work.  According to the documentation if you have the null attribute set to "yes" it ignores the value attribute.

Is it not working?  What error do you get?

[Guest]

Hi Miguel,

I don't get any error but the result is different.

When I tested with "Where status IS NULL" I got 750 record count

When I use cfqueryparam version, I got 0 recordcount

[Guest]

I'm using Oracle 11g. I tested in Oracle SQL developer, Where Status IS NULL gave me 750 counts

when I ran cfqueryparamg through CF (back end is the same Oracle), I got 0 recordcount

Or I can just use where status IS NULL and don't bother with cfqueryparam???? but I thought it's better to use cfqueryparam for security purpose.

[Steve Sommers]

This may depend on what SQL server and version you are talking to. In my experience, this does not work in the where clause because of the equal sign -- null comparison needs "is", even though both will execute without any error.

"where status=null" does not work as one would think whereas "where status is null" does. I use MS-SQL. I'm using 2008 now and I have not tested this specifically but I know MS-SQL 2000 had this issue. Using query analyzer you can compare results yourself.

[Miguel-F]

Based on what Steve said maybe you can try using is insted of =, like:

Where status is <cfqueryparam cfsqltype="cf_sql_varchar" value=" " NULL="yes">

I never use cfqueryparam for NULL anyway.  There is no threat of a sql injection if you just use "where status is NULL".  Where you run into issues and need to use cfqueryparam is when you are using variable input (especially user input) for the query.

My .02 is that you are fine to just use "where status is NULL" in this case.

[Guest]

Thanks Miguel. I will use IS NULL then

If I use IS with cfqueryparam, I got CF error.

[Steve Sommers]

I alway bypass the cfqueryparam within the where clause for null and instead hard code the comparison:

where status is null

I don't do null comparisons too often in queries so it's no big deal for me.

[Guest]

Thanks Steve

[Adam Cameron.]

People have got you on the right track with the fact you need the IS operator not the = operator.  You said when you used an IS operator with the <cfqueryparam> tag you got an error, but you didn't say what the error was (ALWAYS post errors you get).

Also: what version of Oracle are you using?

You're OK hardcoding NULL in this case rather than using a param, but strictly-speaking (IMO) one should separate out the SQL statement from the values the SQL statement is using.  Although I guess NULL is almost an edge case here.

I explain when/how to use <cfqueryparam>, and why in this article.  It might be worth reading.

--

Adam