Re: null values

[Christopher G Tantalo]

Bretz, Ellen wrote:





I have a prepare statement for a select using DBI In ORACLE.



One of the attributes can be null or can contain data.   How do I set up
my scalar for the
execute?



I tried $scalar = 'NULL';

if it was null but didn't work.



Thanks

I just ran into this problem last week, and got a great solution.  I am 
re-posting the code that a fellow newsgrouper gave to me for my solution

my $sql = "select dns_id
             from dmg.do_not_solicit
             where dns_prospect_id = ?\n";
my @columns = qw(
   dns_area_code
   dns_phone
   dns_zip_code
   dns_pr_branch
   dns_pr_client_nbr
   dns_pr_client_status
   );
my @fields = (
   $dnc_areacode,        
   $dnc_phone,          
   $dnc_zipcode,        
   $pro_pr_branch,      
   $pro_pr_client_nbr,  
   $pro_pr_client_status
   );
for (my $i = 0; $i <= $#columns; $i++)
{
   $sql .= "              AND " . $columns[$i] .
         (defined($fields[$i])? " = ?" : " IS NULL") . "\n";
}
my $sth = $dbh->prepare($sql);
$sth->bind_param(1,$pro_prospect_id);
my $j = 2;
for (my $i = 0; $i <= $#fields; $i++)
{
   $sth->bind_param($j++,$fields[$i]) if (defined(@fields[$i])
}

-------------

If the 3rd and 4th values are undef in @fields, this produces the SQL statement:

select dns_id
             from dmg.do_not_solicit
             where dns_prospect_id = ?
             AND dns_area_code = ?
             AND dns_phone = ?
             AND dns_zip_code = ?
             AND dns_pr_branch IS NULL
             AND dns_pr_client_nbr IS NULL
             AND dns_pr_client_status = ?


--
-------------------------------
Just Your Friendly Neighborhood
_SPIDEY_


-----------------------------------------
The information contained in this message may be privileged, confidential, and protected from disclosure. If the reader of this message is not the intended recipient, or any employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. 

Thank you. Paychex, Inc.