in reply to Re: Oracle and Class::DBI search for a NULL field value
in thread Oracle and Class::DBI search for a NULL field value

OK. done. I got the following. The sql is not what I'd expect if I were doing it by hand. 
2T  <- prepare= Ima::DBI::st=HASH(0x407094) at Oracle.pm line 308
    -> FETCH for DBD::Oracle::st (Ima::DBI::st=HASH(0x407094)~0x4068ac
+ 'NUM_OF_FIELDS')
 T  <- FETCH= 1 at Oracle.pm line 310
    -> DESTROY for DBD::Oracle::st (Ima::DBI::st=HASH(0x4068ac)~INNER)
 T  <- DESTROY= undef at Oracle.pm line 312
 T  <- ping= 1 at DBI.pm line 347
    -> prepare_cached for DBD::Oracle::db (Ima::DBI::db=HASH(0x350280)
+~0x370678 'SELECT id
FROM   Areas
WHERE  areaname = ?
')
2   -> FETCH for DBD::Oracle::db (Ima::DBI::db=HASH(0x370678)~INNER 'C
+achedKids')
2T  <- FETCH= HASH(0x3eadd0)2keys at DBI.pm line 1236
2   -> prepare for DBD::Oracle::db (Ima::DBI::db=HASH(0x370678)~INNER 
+'SELECT id
FROM   Areas
WHERE  areaname = ?
' undef)
    dbd_preparse scanned 1 distinct placeholders
    fbh 1: 'ID' NO null , otype   2->  5, dbsize 22/134, p0.s0
2T  <- prepare= Ima::DBI::st=HASH(0x403414) at DBI.pm line 1249
 T  <- prepare_cached= ( Ima::DBI::st=HASH(0x403414) ) [1 items] at DB
+I.pm line 424

[download]

-------------------------------------
Nothing is too wonderful to be true
-- Michael Faraday

Replies are listed 'Best First'.
Re^3: Oracle and Class::DBI search for a NULL field value
by perrin (Chancellor) on Apr 26, 2005 at 21:38 UTC
    Without knowing the details of your tables, it looks fine to me. I suspect there is some reason why your code can't see that record. Maybe it hasn't been committed, or you are running with an unusual isolation level?

    UPDATE: Actually, that looks wrong. It should be generating an "IS NULL" there. Do you have the latest Class::DBI?

[reply]
      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;

      [download]
      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

      [download]
      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

[reply]
[d/l]
[select]
      Do you have the latest Class::DBI?
      No, the latest appears to be .96

      I am at $VERSION = '0.94';

      UPDATE: Confirmed. Version .96 fixes this problem.
      Thanks perrin.

      -------------------------------------
      Nothing is too wonderful to be true
      -- Michael Faraday

[reply]
[d/l]
        Hence the problem. This bug was fixed in 0.96, released more than a year ago.
[reply]

In Section Seekers of Perl Wisdom