23

I ran into a problem when running code similar to the following example:

my $rows = $dbh->do('UPDATE table SET deleted=NOW() WHERE id=?', undef, $id) 
  or die $dbh->errstr;
if (!$rows) {
  # do something else
}

Since the docs state that do returns the number of rows affected, I thought that would work.

Prepare and execute a single statement. Returns the number of rows affected or undef on error. A return value of -1 means the number of rows is not known, not applicable, or not available.

As it turns out, I was mistaken. When I debugged it, I saw that $rows in fact holds the string 0E0, which of course is a true-ish value. I dug in the docs further and saw this piece of code:

The default do method is logically similar to:

  sub do {
      my($dbh, $statement, $attr, @bind_values) = @_;
      my $sth = $dbh->prepare($statement, $attr) or return undef;
      $sth->execute(@bind_values) or return undef;
      my $rows = $sth->rows;
      ($rows == 0) ? "0E0" : $rows; # always return true if no error
  }

There it is. It returns 0E0. I just don't get why it would do that. Does anyone know?

simbabque
  • 53,749
  • 8
  • 73
  • 136
  • 1
    LOL, your skills have improved since. :-) I like this question. It's a very good example of a well written question of general interest. – PerlDuck Oct 20 '17 at 12:35
  • @PerlDuck yes, this was some time ago. But I would call this particular one _knowledge_, not _skill_. But my skills have certainly improved, too. Will I see you in London, btw? – simbabque Oct 20 '17 at 14:09
  • I'm afraid not, though I'd love to. But that weekend doesn't work for me :-( – PerlDuck Oct 20 '17 at 14:34
  • @PerlDuck that's a shame. However, the [German Perl Workshop 2018](http://act.yapc.eu/gpw2018/) (which is the 20th aniversary!) is going to be in Bergisch-Gladbach next spring. Iirc that's around the corner for you, so there are no excuses not to go! ;) – simbabque Oct 20 '17 at 14:48

1 Answers1

29

It's a true value, so you can distinguish it from the false value it returns on error, yet it's numerically equal to zero (without warning), so it's still equal to the number of records affected.

$ perl -e'
   for (undef, "0E0", 4) {
      if ($_) {
         printf "Success: %d rows affected\n", $_;
      } else {
         print "Error!\n";
      }
   }
'
Error!
Success: 0 rows affected
Success: 4 rows affected

If 0 was returned on success when no records are affected, you'd be forced to check errors using defined, which is far less convenient than testing for truth (e.g. foo() or die;).

Other true zeroes. (Ignore "0x0"; it warns.)

mlibby
  • 162
  • 1
  • 8
ikegami
  • 367,544
  • 15
  • 269
  • 518
  • So I should say either `if ($rows == 0)` or `if ($rows eq '0E0')`, right? – simbabque Nov 13 '12 at 10:12
  • 1
    There is also another special value `"0 but true"` which serves the same purpose – mvp Nov 13 '12 at 10:18
  • 2
    @mvp, Technically, `"0 but true"` is the only specially coded case, but there's a [dozen ways](http://www.perlmonks.org/?node_id=464548) of achieving this. (The `0x0` on that page is wrong; it warns.) – ikegami Nov 13 '12 at 10:20
  • I like the explanation on that perlmonks link. You should add that to your answer. – simbabque Nov 13 '12 at 11:46
  • @simbabque, To which reply are you referring? – ikegami Nov 13 '12 at 11:48
  • I meant the *Exponential ("0E0")* from your link above. It's explained in [perlnumber](http://perldoc.perl.org/perlnumber.html). – simbabque Nov 13 '12 at 11:52
  • Note on modern Perl you can test not only for truth (`foo() or die;`); you can test for defined too (`foo() // die;`) – Galimov Albert Nov 13 '12 at 13:47
  • @PSIAlt, Even on modern Perls, there's no low-precedence "defined-or" like there is for "or" (`or`). Feels really wrong to use `//` that way. – ikegami Nov 13 '12 at 14:04
  • I receive `1` even when same (as already recorded) values were "updated" to the row. Shouldn't I receive `0` in such case? – Ωmega Oct 20 '19 at 18:08