Tuesday, November 1, 2011

Null Values within SQL Select Statement with CFQUERYPARAM

I was working on code where I wanted to store nulls into the database and query based on those nulls.

<cfquery name="qRetrieve" datasource="#getDSN().getName()#">
 select semesterID,Major_ID,Campus_ID,AdmTypeReqID,startDate,startOrEndFlag
 from isFull
 where 
  semesterID = <cfqueryparam value="#arguments.DTO.getsemesterID()#" cfsqltype="CF_SQL_INTEGER">
  AND Campus_ID = <cfqueryparam value="#arguments.DTO.getCampus_ID()#" cfsqltype="CF_SQL_INTEGER">
  AND startOrEndFlag = <cfqueryparam value="#arguments.DTO.getstartOrEndFlag()#" cfsqltype="CF_SQL_INTEGER">
  AND AdmTypeReqID= <cfqueryparam value="#arguments.DTO.getAdmTypeReqID()#" cfsqltype="CF_SQL_INTEGER" null="#iif(arguments.DTO.getAdmTypeReqID(),true,false)#">
  
</cfquery>

Notice the last line is using the function IIF instide the null attribute of cfqueryparam.  In the SQL Insert and Update statements, this just works.  I am noticing in interacting with MSSQL 2005 this is not working.  I am getting zero rows back when it should be in the 5-10 rows. For now I have replaced my last line of code with the following 5 rows of code

AND 
   <cfif arguments.dto.getAdmTypeReqID() EQ "">
   AdmTypeReqID is null
   <cfelse>
   AdmTypeReqID = <cfqueryparam value="#arguments.DTO.getAdmTypeReqID()#" cfsqltype="CF_SQL_INTEGER" >
   </cfif>

Has anyone else ran into this?
Posted by Craig Benner at 2:41 PM

7 comments:

  1. Is it possible that "arguments.DTO.getAdmTypeReqID()" is returning a numeric value 0 instead of a blank value? That IIF() also looks backward to me. If arguments.DTO.getAdmTypeReqID() evaluates to true, then null="true" - is that correct? I would expect it to say: #IIF(arguments.DTO.getAdmTypeReqID(),false,true)# meaning "if arguments.DTO.getAdmTypeReqID() is true, then 'not null' ". Maybe I'm just not understanding the intent.

    ReplyDelete

  2. That's expected behavior under The SQL-92 standard.

    NULL is a special value reprenting the unknown, it's not equal to anything as you don't know what it is... we all are tempted to say but it's NULL, but the power that be don't agree ;)

    You can say {column} is unknown this can evaluate to a logical condition

    but you can't say {column} = unknown this will always be false as you compare known with unknown.

    You can override this behavior with the SET ANSI_NULLS {ON | OFF} instruction but I wouldn't recommend it.

    http://msdn.microsoft.com/en-us/library/aa259229%28v=sql.80%29.aspx

    ReplyDelete

  4. There's no need to use iif()--which has some performance issues. Just do:

    <cfqueryparam value="#arguments.DTO.getAdmTypeReqID()#" cfsqltype="CF_SQL_INTEGER" null="#(arguments.DTO.getAdmTypeReqID())#">

    ReplyDelete

  5. This comment has been removed by the author.

    ReplyDelete

  6. Why dont you do something like this:


    <cfqueryparam value="#arguments.DTO.getAdmTypeReqID()#" cfsqltype="CF_SQL_INTEGER" null="#NOT isNumeric(arguments.DTO.getAdmTypeReqID())#">

    ReplyDelete

  7. Thanks for all the great comments, I would like to clear one thing up, I actually mis-typed the example. I was actually comparing it to blank "" in the null attribute, so the last example is probably the best solution which i probably switch my code too. Other than that, I believe JF is right, it is just frustrating that we can use the same concept in SELECT statements as we do in UPDATE,INSERT,DELETE.

    Thanks everyone for great feedback, sometimes I wonder if anyone watches my blog :-D (PS. thanks coldfusionblogger.org for aggregating)

    ReplyDelete

Subscribe to: Post Comments (Atom)