Let's try this the old fashioned way ...
my $sql = qq/
SELECT id
FROM Areas
WHERE areaname = ?
/;
my $sth = $dbh->prepare( $sql ) or print "DB err: " . DBI->errstr;
my $rv = $sth->execute( 'BUFFALO' ) or print DBI->errstr;
my @rows = $sth->fetchrow_array or print DBI->errstr;
print "Try BUFFALLO, got id is: @rows \tRV was: $rv\n";
$rv = $sth->execute( undef ) or print "exec undef " . DBI->errstr;
@rows = $sth->fetchrow_array or print "No Rows!\n " . DBI->errstr;
print "Try undef, got id is: @rows \tRV was: $rv \n";
$sth->finish;
$sql = qq/
SELECT id
FROM Areas
WHERE areaname is null
/;
$sth = $dbh->prepare( $sql ) or print "DB err: " . DBI->errstr;
$rv = $sth->execute() or print "exec undef " . DBI->errstr;
@rows = $sth->fetchrow_array or print "fetch " . DBI->errstr;
print "Try areaname is null in sql, got id is: @rows \tRV was $rv\n";
$sth->finish;
This gives....
Try BUFFALLO, got id is: 23 RV was: 0E0
No Rows!
Try undef, got id is: RV was: 0E0
Try areaname is null in sql, got id is: 1 RV was 0E0
So this shows that DBI doesn't treat undef like NULL in bindings when selecting from a dB. This is explained in the docs for DBI:
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 select NULLs you have to say "WHERE
product_code IS NULL" and to make that general you have to
say:
... WHERE (product_code = ? OR (? IS NULL AND product_code IS NULL))
and bind the same value to both placeholders.
Which is all well and good in set theory, but a PITA in my real world.
Class::DBI, if it is to be useful, is supposed to abstract such messiness away, IMHO.
How does one get around this while using C::DBI?
-------------------------------------
Nothing is too wonderful to be true
-- Michael Faraday
| 