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

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

Monks,
I am trying to execute code when an empty record set is returned from teh database and my code is not doing the job.
Could someone please help me.
I am trying to tell when an empty record set is returned.
my $SQLString "select this, that from there"; $sth = $dbh->prepare($SQLString); $sth->execute(); @bkmark = $sth->fetchrow; if(length(@bkmark) == 0) { ........... }

-many thanks

Replies are listed 'Best First'.
Re: if (empty record set)
by Mr. Muskrat (Canon) on Jun 26, 2003 at 14:57 UTC

    if (@bkmark == 0) { or even if (scalar @bkmark == 0) {

    length is for use on scalar values. In fact, perlfunc says this about length:
    "length EXPR
    Returns the length in characters of the value of EXPR. If EXPR is omitted, returns length of $_. Note that this cannot be used on an entire array or hash to find out how many elements these have. For that, use scalar @array and scalar keys %hash respectively."

    Update: Two different approaches to the same problem. Heh.

Re: if (empty record set)
by hardburn (Abbot) on Jun 26, 2003 at 14:55 UTC

    Instead of using $sth->fetchrow(), call $sth->rows() instead, which returns the number of rows that were hit:

    my $SQLString "select this, that from there"; $sth = $dbh->prepare($SQLString); $sth->execute(); if($sth->rows() == 0) { ........... }

    ----
    I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
    -- Schemer

    Note: All code is untested, unless otherwise stated

Re: if (empty record set)
by Zaxo (Archbishop) on Jun 26, 2003 at 14:59 UTC

    The fetchrow methods are all suffixed by the return type. You clearly want fetchrow_array.

    Also, your conditional puts its argument in scalar context, so if (@bkmark) {...} will suffice. That tests if the array is empty.

    After Compline,
    Zaxo

Re: if (empty record set)
by tcf22 (Priest) on Jun 26, 2003 at 14:59 UTC
    Using $sth->rows() is the answer if you need to actually work with the data, but if you just need to see if there are rows that match, consider using: (untested)
    my $SQLString "select COUNT(1) from there"; $sth = $dbh->prepare($SQLString); $sth->execute(); @bkmark = $sth->fetchrow; if($bkmark[0] == 0) { ........... }

      If you are working with mySQL SELECT COUNT (*) FROM ... is particularly fast, the engine being optimised for such things.

      So if you just want to know if there are any records available, thats the way to do it, rather than fetching the records themselves. If you need the data also, of course it is better to go with the other suggestions.

      CountZero

      "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

      on many RDBMS the execute method will return the number of rows like so:
      my $query = "SELECT * FROM myTable"; $sth = $dbh->prepare($query); if(!$sth->execute()) { ... }
      it varies from system to system .. but is another way to accomplish what you want

      edit: you could also run this query
      SELECT Count(yourPrimaryKeyHere) FROM yourTable
      if you're on a database that doesn't support execute's return value or $sth->rows
        Thats not the way execute works in DBI. Check the docs, but to summarise for you now
        If an error occurs you get undef back
        If it doesn't know the number of rows affected get -1 back
        If no rows are affected you will get 0E0 back.
        otherwise you get the number of rows affected
Re: if (empty record set)
by Anonymous Monk on Jun 26, 2003 at 16:34 UTC
    What is gojng on today?

    A general blindness?

    What this poor fellow was asking is what to do in case of an empty record set.

    using $sth->rows is dead wrong. rows is used in non-select statements.

    Somebody suggested using $sth->fetchrow_arrayref in void context, without catching the result anywhere. Wow!

    The simple way is

    $sth->execute(); my $recordset = $sth->fetchall_arrayref(); if (@$recordset) { # there are records. Do something here } else { # empty recordset. Do something else. }

    Or, if you don't want to get all records at once

    my $found = 0; while (my $rec = $sth->fetchrow_arrayref()) { #do something $found = 1; } unless ($found) { # recordset was empty. # do something else }
Re: if (empty record set)
by digger (Friar) on Jun 26, 2003 at 16:12 UTC
    I have struggled with this same issue in the past. Just using the rows will not solve the problem, according to the DBI documentation.

    For SELECT statements, it is generally not possible to know how many rows will be returned except by fetching them all. Some drivers will return the number of rows the application has fetched so far, but others may return -1 until all rows have been fetched. So use of the rows method or $DBI::rows with SELECT statements is not recommended.

    You would have to do a fetchall_arrayref first, if I am understanding the docs correctly, then you could rely on the rows method.

    I ended up using the "SELECT COUNT(*) FROM ..." SQL statement to check the number of records returned.

    On a tangent, which method is more efficient? If I do a select count... first, then I might end up executing 2 SQL statements, if I get a count>0 from the select statement. If I do fetchall_arraref then I might end up pulling a large number of records into memory, and slowing things down. The answer is probably "it depends", but your input is appreciated.

    digger
Re: if (empty record set)
by one4k4 (Hermit) on Jun 26, 2003 at 15:07 UTC
    I have thoughts about using any array_ref as your result set from a DB call, as I like using hashref, but that's another thread. Here's what I'd do: (untested for syntax errors)
    my $i = 0; while ($sth->fetchrow_hashref){$i++; ##..do other stuff.. } do_your_no_row_stuff($vars) unless $i;
    Or
    do_no_row_stuff() unless $sth->fetchrow_hashref; ## Of course, the first row is now useless.. but..


    One4k4 - perlmonks@poorheart.com (www.poorheart.com)
Re: if (empty record set)
by Anonymous Monk on Jun 26, 2003 at 15:21 UTC
    Try one of the following:

    o Test the value contained in $sth->rows. If it is zero then no records exist. IIRC not all DBDs set this correctly.

    o if(@bkmark = $sth->fetchrow){ ... }

    Cheers,

    Logic