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

Got a problem here that it seems to me that there should be an elegent solution to, but darned if I can find it..

I have an order entry system written in Perl/DBI/MySQL, and within this system I often need to create new queries based on the results of previous ones. The problem is, I don't have an elegant way of determining if a SELECT statement came back with 0 results. The best I've been able to come up with is to read all of the rows of results into an array of arrays, determine if that array is empty or not, and then continue processing if it isn't. The problem here, of course, is that it takes up a heck of a lot of memory (not something you want when 20 reps are going to be using it simultaneously), and is ugly to work with (and probably horrific for someone else to maintain)

Does anyone have an idea of how I can do this better? I know that when I submit queries directly in MySQL's client, I get extra data like:
10 rows in set (0.00 sec)
or
Empty set (0.26 sec)
All I'm looking for is a way to access this data from Perl.

Spacewarp

DISCLAIMER:
Use of this advanced computing technology does not imply an endorsement
of Western industrial civilization.

Replies are listed 'Best First'.
Re: Determining if a DBI SELECT comes back empty
by grinder (Bishop) on Dec 05, 2003 at 21:29 UTC

    There are cheaper ways of doing it... When I have to do things like this, the code tends to look like the following:

    $sth->execute( @arg );
my $row = 0;
while( defined( my $r = $sth->fetchrow_arrayref )) {
    ...
    # do stuff with $r
    ....
    ++$row;
}
print "fetched $row row(s)\n";

    [download]

    Just keeping a scalar around isn't a particularly expensive proposition. That's all your mySQL client is doing.

    Note that for some database architectures, such as Sybase, the only way to determine the number of rows that a query would return is to go and fetch them; there is no way to know beforehand (that is, after the statement is executed but before the first row is fetched) how many rows will be returned.

    I believe that the DBD driver for mySQL supports the rows method, which does provide you with this information beforehand, if you really need it, but do note that using it is not portable.

[reply]
[d/l]
Re: Determining if a DBI SELECT comes back empty
by iburrell (Chaplain) on Dec 05, 2003 at 21:46 UTC
    If you just need to know if there are any results but don't need to fetch them, use SELECT COUNT(*). If you need to both read any results and do something if nothing was read, then you can use a flag to signal when you read anything: 
    $sth->execute();
my $count = 0;
while (my $row = $sth->fetchrow_arrayref()) {
   process_row($row);
   $count++;
}
unless ($count) {
   do_something_else();
}

    [download]
[reply]
[d/l]
[select]
      Beautiful! Now that's the level of elegance I like to use, and I'm embarassed that I couldn't think of something this simple myself. 8)

      Thanks to everyone else as well.. you've added to my toolbox 8)

      Spacewarp

      DISCLAIMER:
      Use of this advanced computing technology does not imply an endorsement
      of Western industrial civilization.
[reply]
Re: Determining if a DBI SELECT comes back empty
by Zed_Lopez (Chaplain) on Dec 05, 2003 at 21:03 UTC

    I think I don't understand. If you need the results for further processing when there are results, where's the memory penalty? When there are no results, you have an empty array, which involves trivial memory, and you move on.

    You could do SELECT COUNT(*)'s in advance, but that does incur a performance penalty over what you're already doing.

[reply]
Re: Determining if a DBI SELECT comes back empty
by hmerrill (Friar) on Dec 05, 2003 at 21:16 UTC
    There is no DBI method that will reliably give you a count of the number of rows fetched. Two things I can think of to do: 
      1. if you intend to process all the rows of your
     original fetch anyway, then just count the rows as
     you process them - that is *the* most reliable way
     to get a count of rows fetched.

  2. use the 'count(*)' which will retrieve one column(that
     being the number of rows fetched), and in this example
     I assigned the count to alias 'count':
     
     $dbh->{RaiseError} = 1; # save having to check each method call

     my $sql = qq{
         SELECT count(*) as count
         FROM your_table
         WHERE one_of_the_columns = something
     };
     my $sth = $dbh->prepare($sql);
     $sth->execute;
     my ($count) = $sth->fetchrow_array;
     if ($count == 0) {
        print "NO rows fetched!\n";
     }

    [download]
    HTH.
[reply]
[d/l]
Re: Determining if a DBI SELECT comes back empty
by jZed (Prior) on Dec 05, 2003 at 22:14 UTC
    Not all DBDs set the statement handle before a fetch, but MySQL does. That means that you don't have to fetch *any* rows. For a purposefully verbose example: 
    if (my $rows = $sth->execute) {
    if ($rows==0) {
        print "Zero rows returned";
    }
    else {
        print "$rows rows returned";
    }
}
else {
   print "Failed execute: ". $sth->errstr;
}
$sth->finish;

    [download]
    Note that the finish() is required here because you do no fetching. If one of the if's had a fetch, you'd omit the finish().

    --

    The heck with patents, here's how to protect your thoughts: <tinfoil>.o0(Martians can't read this)</tinfoil>
[reply]
[d/l]
[select]

      Not all DBDs set the statement handle before a fetch, but MySQL does

      But not always. If you create the statement with mysql_use_result rows() will no longer behave that way.

      Note that the finish() is required here because you do no fetching. If one of the if's had a fetch, you'd omit the finish().

      If one had a fetch() and fetch()ed to the end of the result set. If you fetch 10 rows out of an 11 row result set you will still need to call finish on the sth

[reply]

Back to Seekers of Perl Wisdom