Adobe Community: NULL attribute of CFQUERYPARAM

Currently Being Moderated

1. Miguel-F,

Aug 8, 2012 9:26 AM in reply to GKiew

Report

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?

|

Mark as:

Currently Being Moderated

2. Steve Sommers,

Aug 8, 2012 9:34 AM in reply to GKiew

Report

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.

|

Mark as:

Currently Being Moderated

3. GKiew,

Aug 8, 2012 9:36 AM in reply to Miguel-F

Report

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

|

Mark as:

Currently Being Moderated

4. GKiew,

Aug 8, 2012 9:39 AM in reply to GKiew

Report

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.

|

Mark as:

Currently Being Moderated

5. Miguel-F,

Aug 8, 2012 9:43 AM in reply to GKiew

Report

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.

|

Mark as:

Currently Being Moderated

6. GKiew,

Aug 8, 2012 9:46 AM in reply to Miguel-F

Report

Thanks Miguel. I will use IS NULL then

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

|

Mark as:

Currently Being Moderated

7. Steve Sommers,

Aug 8, 2012 9:46 AM in reply to Miguel-F

Report

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.

|

Mark as:

Currently Being Moderated

8. GKiew,

Aug 8, 2012 10:09 AM in reply to Steve Sommers

Report

Thanks Steve

|

Mark as:

Currently Being Moderated

9. Adam Cameron.,

Aug 8, 2012 1:09 PM in reply to GKiew

Report

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

|

Mark as:

ColdFusion

NULL attribute of CFQUERYPARAM

Aug 8, 2012 9:12 AM

More Like This

Bookmarked By (0)

Answers + Points = Status