http://qs321.pair.com?node_id=374616

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

What I am trying to do seems it should be quite simple. I am using Class::DBI and am trying to do a search to find entries that have a particular field as NULL.
my @listings = Table::Listings->search(somefield=>'NULL');
This doesn't work, and neither does everything else I have tried to think of. I tried retrieve_from_sql but for some reason that is giving me problems, and I figure there is a way to get this to work...if I just knew how!! I looked around here, google, Class::DBI pod...but nothing. -Michael

Replies are listed 'Best First'.
Re: Class::DBI specifying field is NULL
by trantor (Chaplain) on Jul 15, 2004 at 13:34 UTC
    This should do the trick:

    my @listings = Table::Listings->search(somefield => undef);

      BINGO! Thanks!

      -Michael
Re: Class::DBI specifying field is NULL
by Fletch (Bishop) on Jul 15, 2004 at 13:26 UTC

    Since it sits on top of Ima::DBI you could always define a custom search routine.

    MyClass->set_sql( null_somefield => <<'EOT' ); SELECT __ESSENTIAL__ FROM __TABLE__ WHERE somefield IS NULL EOT my $iter = MyClass->search_null_somefield( );

    Update: Ooh, looks like passing undef as is suguested below works fine (it gets translated into field IS NULL correctly). Strange they don't explicitly mention that in the docs or on the Class::DBI wiki.