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

Hi Monks, I don't know if I'm missing the obvious here, but I'm trying to use eval to catch errors in writing to a database. Here is a piece of code I'm using to test the idea. 

  
  $dbh->{AutoCommit} = 0; 
  $dbh->{RaiseError} = 1;
  eval {
      $sql = "UPDATE stockhistory SET invoiced = 1 WHERE id = 737";
      $sth = $dbh->prepare($sql) or die "Cannot prepare: " . $dbh->err
+str();
      $sth->execute();
  };

  $dbh->commit ();

  if ($@) {
      print "Transaction aborted because $@";
      $dbh->rollback; 
  }

[download]

If I change the name of the database $@ shows an error (as you would expect) as does changing the field name 'invoiced' or 'id'.

However, if I change the id number to a number that does not exist in the db (say 1737), no error is shown even though it cannot have written that id number. Nothing changes in the db but the script carries on as if it has written successfully.

Can anyone tell me where I'm going wrong?

Replies are listed 'Best First'.
Re: Eval not working
by Utilitarian (Vicar) on Sep 03, 2010 at 11:03 UTC
    Two things,
    1. eval is not failing
    2. An update command that does nothing is not an error so if the condition(id = 1737) is not met nothing happens but no error is thrown.

    Update: However the documentation on execute in DBI notes that

    For a non-SELECT statement, execute returns the number of rows affected, if known. If no rows were affected, then execute returns "0E0", which Perl will treat as 0 but will regard as true. Note that it is not an error for no rows to be affected by a statement.
    Perhaps you could assign the result of $sth->execute() and check to ensure that something was done

    print "Good ",qw(night morning afternoon evening)[(localtime)[2]/6]," fellow monks."
[reply]
[d/l]
[select]
      Thanks Utilitarian for explaining that.

      Am I right in saying the only way to catch a problem like that would be to use the 'affected rows' function?
[reply]
          $dbh->{AutoCommit} = 0; 
  $dbh->{RaiseError} = 1;

  my $id = 737;

  my $rows;
  if (!eval {
      my $sql = "UPDATE stockhistory SET invoiced = 1 WHERE id = ?";
      my $sth = $dbh->prepare($sql);
      $rows = $sth->execute($id);
      $dbh->commit();
      1
  }) {
      print "Transaction aborted because $@";
      $dbh->rollback();
  }

  die("Record $id not found") if $rows < 1;

        [download]

        Note: Can't check if $rows is true since "0E0" (equal to zero but true) is returned when no rows are affected.

[reply]
[d/l]

        Exactly.

        Your query might successfully update every record in the table, or only one, or none at all, but it ran successfully. The number of rows affected is provided by: 

        print 'The query updated '. $sth->rows . " rows.\n";

        [download]

        As Occam said: Entia non sunt multiplicanda praeter necessitatem.

[reply]
[d/l]
Re: Eval not working
by Anonymous Monk on Sep 09, 2010 at 21:52 UTC

    I could be totally off base here (haven't programmed in Perl in eons), but perhaps your $dbh->commit(); needs to be inside the eval curly braces ({}).

    I suspect what is happening is that after the error occurs within the braces the error is being reset by the commit() call outside of the braces.

    Justin Hanekom<justin.hanekom@gmail.com>
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom