I just figured it out.
If $DOMAIN is NULL, I'm setting it to "undef" and then
passing that in to the execute statement. It seems to
be taking the _string_ "undef" instead of the _value_
of undef. So my query was doing "WHERE domain
= 'undef'" instead of evaluating undef to NULL.
I'll just wrap the $DOMAIN with an eval() and that
should do the trick.
On a related note, is there any way to see what the
actual query MySQL is running?
Thanks for the response,
Ronnie
>
> ----- Original Message -----
> From: "Ronnie Anderson" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Friday, January 11, 2002 8:36 PM
> Subject: dbi and nulls
>
>
> <snip>
>> my $hosthandler = $dbh->prepare(q{
>> SELECT id
>> FROM host
>> WHERE tid = ?
>> AND (domain = ? OR (? IS NULL AND domain IS
>> NULL))
> What are you binding to the second ? here?
> There should be a column name there i think.
> What are you tring to test.
> Let's assume $DOMAIN contains 'foo'
> Using binding below the statement will be:
> domain = 'foo' OR ( 'foo' IS NULL AND domain IS
> NULL)
> I dont think you have a column foo.
>
>> 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.
>
> Maarten