Re: DBI Prepared Update and NULLs

Sounds like you're out of luck:

   Null Values

   Undefined values, or "undef", can be used to indicate null values. 
+ However, care must
   be taken in the particular case of trying to use null values to qua
+lify a "SELECT"
   statement. Consider:

     SELECT description FROM products WHERE product_code = ?

   Binding an "undef" (NULL) to the placeholder will not select rows
   which have a NULL "product_code"! Refer to the SQL manual for your 
+database
   engine or any SQL book for the reasons for this.  To explicitly sel
+ect
   NULLs you have to say ""WHERE product_code IS NULL"" and to make th
+at
   general you have to say:

     ... WHERE (product_code = ? OR (? IS NULL AND product_code IS NUL
+L))

   and bind the same value to both placeholders. Sadly, that more gene
+ral syntax
   doesn't work for Sybase and MS SQL Server. However on those two ser
+vers
   the original ""product_code = ?"" syntax works for binding nulls.
[download]

-- DBI

Comment on Re: DBI Prepared Update and NULLs Download Code

Replies are listed 'Best First'.
Re^2: DBI Prepared Update and NULLs by iburrell (Chaplain) on Jul 28, 2004 at 20:55 UTC
This applies to the WHERE clause of a SELECT, UPDATE, or DELETE. Not the data in an UPDATE or INSERT. For UPDATEs, this should work: `my $sql = "UPDATE foo SET bar = ?"; $dbh->do($sql, {}, undef);` [download] Once the placeholders are substituted, this should be the same as: `UPDATE foo SET bar = NULL;` [download]	[reply] [d/l] [select]