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

Yesterday I wrote an SQL statement:

   select sum(whatever)
   from
      -- 10 tables, omitted --
   where
      -- whole pile of stuff omitted --
   group by
      -- another pile of things

[download]

The idea being that I would plug in various placeholder values and get back the value I was interested in. This worked very well, and as I was pressed for time I simply derefenced the result of fetchrow_arrayref to get my value. The code looked something like:

  $sth->execute($foo, $bar, $rat) or die $sth->errstr, $/;
  my $sum = $sth->fetchrow_arrayref->[0];
  $sth->finish;

[download]

This worked perfectly well for quite some time until I hit a particular set of inputs which produced an empty result set. At that point, fetchrow_arrayref returned undef, and perl rightly got upset when I asked it to dereference it to obtain the first element.

So I sighed, cursed my haste and realised I had to copy the result into an intermediary to check whether it was defined:

  $sth->execute($foo, $bar, $rat) or die $sth->errstr, $/;
  my $r = $sth->fetchrow_arrayref;
  my $sum = $r ? $r->[0] : 0;
  $sth->finish;

[download]

but before I did so, I had an insight that allowed me to zen the code:

  $sth->execute($foo, $bar, $rat) or die $sth->errstr, $/;
  my $sum = ($sth->fetchrow_arrayref || [0])->[0];
  $sth->finish;

[download]

That is, call the fetchrow_arrayref. If that evaluates to false, the expression lazily collapses to a single element array ref containing zero. And then dereference whatever happened, and take the first element.

And then I finished the program. I did leave a comment above the statement explaining what was going on. I like the idiom, but I worry that it's too clever for its own good.

Comments? Opinions?

• another intruder with the mooring in the heart of the Perl

Replies are listed 'Best First'.
Re: Is this too clever? (or, stupid DBI tricks)
by kyle (Abbot) on Jan 05, 2008 at 15:14 UTC 
    my ($sum) = $sth->fetchrow_array;

    [download]

    Or, if you have your SQL in a variable... 

    my ($sum) = $dbh->selectrow_array( $sql, undef, $foo, $bar, $rat );

    [download]

    DBI has all kinds of good stuff.

    Update: When there is no result, $sum will be undef. You can do $sum ||= 0 (or $sum //= 0 in Perl 5.10) to avoid generating warnings about this later.

[reply]
[d/l]
[select]
Re: Is this too clever? (or, stupid DBI tricks)
by bradcathey (Prior) on Jan 05, 2008 at 15:40 UTC

    Don't forget the incredibly helpful DBI recipes by gmax. I've incorporated most of that into my DBI work. In fact, I can't remember the last time I used "fetch...".


    —Brad
    "The important work of moving the world forward does not wait to be done by perfect men." George Eliot
[reply]
Re: Is this too clever? (or, stupid DBI tricks)
by perrin (Chancellor) on Jan 05, 2008 at 16:25 UTC
    When I want a single value, I usually just do this: 
    my ($sum) = $sth->fetchrow_array;

    [download]
    When efficiency counts, you should use bind_cols(), but it doesn't seem like it's important here.
[reply]
[d/l]
Re: Is this too clever? (or, stupid DBI tricks)
by Jenda (Abbot) on Jan 05, 2008 at 16:56 UTC

    I think it would be easier to ask DBI to throw the exceptions with RaiseError => 1 than to dutifuly test the return values and throw $sth->errstr.

    Jenda
    Support Denmark!
    Defend the free world!

[reply]
[d/l]
      A select which returns 0 rows is not an error, so why would it raise an exception or set $sth->errstr?
[reply]
[d/l]

        That's not the point, that was sufficiently solved by the previous responses. I was not replying to the main point of the root node, but rather commenting on something else in the code. Grinder seems to use  or die $sth->errstr, $/; next to any execute() statements which to me looks unnecessary, something that can be had for "free". Sorry if I did not make myself clear.

        Jenda
        Support Denmark!
        Defend the free world!

[reply]
[d/l]
Re: Is this too clever? (or, stupid DBI tricks)
by jplindstrom (Monsignor) on Jan 05, 2008 at 18:05 UTC
    I consider that idiom fairly standard, so I wouldn't hesitate using it in general. But others have shown better ways of solving this particular problem.

    On a general note, consider DBIx::Simple if you want a more elegant interface on top of DBI.

    /J

[reply]
Re: Is this too clever? (or, stupid DBI tricks)
by Tux (Canon) on Jan 06, 2008 at 09:15 UTC

    Why use dereferencing when you have bind_columns ()

    my ($sth, $sum) = ($dbh->prepare ("select sum (whatever) from ..."));
$sth->bind_columns (\$sum);

while ( long running loop ) {
    if ($sth->execute and $sth->fetch) {
        # My $sum is now valid
        }
    else {
        print STDERR "Something went wrong: ", $sth->errstr, "\n";
        }
    }

    [download]

    Enjoy, Have FUN! H.Merijn
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom