How to use the cfqueryparam for a null value to get records from a table?

Question

Can i use the cfqueryparam for a null value to get records from a table?

My sample select Query is

Select * from users where lastname is null

Dan Bracuk's edit starts here

Here is a sample query against an oracle database:

<cfquery name="x" datasource="pamot">
select *
from clinic_fu
where hsc_number = <cfqueryparam cfsqltype="cf_sql_varchar" value="fred" null="yes">
</cfquery>

Here is the debugging from the web page.

select *
from clinic_fu
where hsc_number = ?

Query Parameter Value(s) -
Parameter #1(cf_sql_varchar) =

How do you interpret this? Was it this:

where hsc_number is null

or this

where hsc_number = ''

No real need to use cfqueryparam with that query unless you are passing a value that may be `NULL`. If so, look at the `null` property of `cfqueryparam`. — Scott Stroz, Jul 16 '14 at 16:28
If the null is a constant (not a variable) there is no benefit to using cfqueryparam on it. — Leigh, Jul 16 '14 at 16:48
@VersatileCrab - I am not entirely clear on what you are asking and why (which may have an impact on the answer). Dan took a stab at it and made a significant edit. Does the new question reflect what you are really asking here? — Leigh, Jul 17 '14 at 14:03

score 3 · Accepted Answer · answered Jul 16 '14 at 20:09

The way the debug SQL statement would be interpreted is where hsc_number = null which will be false for all records.

In some SQL languages (maybe just MySQL), you can create null safe expressions. The statement would work as both an is null when a null is specified or as an equals statement when a value is specified. I personally dont work with Oracle DB, but from my quick research, the only option for null safe expression would include function calls that could impact performance.

An if statement to switch to is null when you need null records should be the best way to go here.

<cfquery name="x" datasource="pamot">
  select *
  from clinic_fu
  where hsc_number
  <cfif conditionForNull>
    is null
  <cfelse>
    = <cfqueryparam cfsqltype="cf_sql_varchar" value="#hsc_number#" />
  </cfif>
</cfquery>

I find the null attribute useful for inserts and update statements more then with select statements.

<cfquery  result=>
  INSERT INTO users
  (first_name, last_name)
  VALUES
  (<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.firstName#" null="#form.firstName eq ""#" />, 
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.lastName#" null="#form.lastName eq ""#" />);
</cfquery>

In this statement, when the name values are empty strings a null value will be passed to the database instead of the form value. It's not a very realistic example, for I would personally write the empty string instead of a null, but it shows how the null attribute can be used.

Slight problem with the interpretation. where hsc_number = null is not valid sql. — Dan Bracuk, Jul 16 '14 at 22:21
@DanBracuk - Agreed on using the ansi standard `is null`, instead of equals. As an interesting side note, the behavior actually depends on the dbms. Some, like sql server, actually do support the non-standard `col = null` syntax. (Though it will be going away soon). I found that out the hard way... — Leigh, Jul 17 '14 at 15:23

How to use the cfqueryparam for a null value to get records from a table?

1 Answers1