Forgive me if this is common knowledge, but I've
searched and can't find the answer (I'm new to DBI).
I'm trying to use NULL values with DBI, and have so
far been unsuccessful. According to the help
documentation:
"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."
So, going by the above, here is my code:
my $hosthandler = $dbh->prepare(q{
SELECT id
FROM host
WHERE tid = ?
AND (domain = ? OR (? IS NULL AND domain IS NULL))
AND (subdom = ? OR (? IS NULL AND subdom IS NULL))
AND (misc = ? OR (? IS NULL AND misc IS NULL))
}) or die "Couldn't prepare statement: $DBI::errstr;
stopped";
$hosthandler->execute
($tid,$DOMAIN,$DOMAIN,$SUBDOM,$SUBDOM,$MISC,$MISC);
$DOMAIN,$SUBDOM,$MISC can all be either strings or
NULLs. I've tried turning the word "NULL" into "undef"
for each of these without luck. Running the query
manually against MySQL works just fine.
What am I doing wrong?
Thanks,
Ronnie Anderson