freddo411 has asked for the wisdom of the Perl Monks concerning the following question:

I thought this question was answered when I searched and found Class::DBI specifying field is NULL.

However, I tried this: 

...
        my @areas = Areas->search( areaname => undef );
        my $area = pop @areas;

        print " Area in DB, id: " . $area->id;
        print " areaname: " . $area->areaname;
...

[download]
And got this: 
"processEmpFile.pl" 291 lines, 6885 characters 
gonzo:/dbsetup/empDataLoad.04: ./processEmpFile.pl
Can't call method "id" on an undefined value at ./processEmpFile.pl li
+ne 72, <GEN0> chunk 1.

[download]
* I verfied via SQL that there is an entry in the DB where areaname is null.

* I also searched for another known value "foobar" using C::DBI and found it successfully.

I am using Oracle 8, which I suspect is related to why the above node doesn't apply to me. Anyone know a work around?

--Fred

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

Replies are listed 'Best First'.
Re: Oracle and Class::DBI search for a NULL field value
by perrin (Chancellor) on Apr 26, 2005 at 20:48 UTC
    Turn on DBI_TRACE and look at the SQL Class::DBI is generating.
[reply]
      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

[reply]
[d/l]
        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]

Back to Seekers of Perl Wisdom