Thread: Catching errors with Perl DBI

How can I catch the errors generated whne I call an INSERT that violates a
constraint? I have coded like this:

             my $sth = $dbh->prepare($stmt);
             my $rv = $sth->execute() or die $DBI::errstr;
             if ( $rv < 0 ) {
                 print $DBI::errstr;
             }

But, if the INSERT violates a constraint, it never gets the the evaluation
of the $rv

Is this a setting for the DBI?

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

On Thu, 2 Jul 2020 11:03:37 -0400
stan <stanb@panix.com> wrote:

> my $sth = $dbh->prepare($stmt);
> my $rv = $sth->execute() or die $DBI::errstr;

that ``or die`` means: if the result of the ``execute`` is false
(which only happens on error), throw an exception (which, as you
noticed, terminates the process unless caught with an ``eval {}`` or
similar construct)

> if ( $rv < 0 ) { print $DBI::errstr; }

Notice that ``$rv`` would never be less than 0: for an ``INSERT``,
it's the number of rows inserted (or a special "0 but true" value in
case no rows were inserted).

So, you can do two things:

* keeping the same style::

    my $rv = $sth->execute(@bind_values);
    if (!$rv) {
        print $sth->errstr;
        # and probably do something useful here ☺
    }

* switching to exceptions everywhere

  Tell DBI you want exceptions::

    my $dbh = DBI->connect(
        $dsn,$user,$password,
        {
            PrintError => 0,
            RaiseErorr => 1,
            PrintWarn  => 0,
            RaiseWarn  => 1,
        }
    );

  then run statements like this::

    eval { $dbh->prepare($stmt)->execute(@bind_values) }
        or do {
            print $@; # the exception is store in this variable
        };

--
    Dakkar - <Mobilis in mobile>
    GPG public key fingerprint = A071 E618 DD2C 5901 9574
                                 6FE2 40EA 9883 7519 3F88
                        key id = 0x75193F88

Stan:

On Thu, Jul 2, 2020 at 5:03 PM stan <stanb@panix.com> wrote:

> How can I catch the errors generated whne I call an INSERT that violates a
> constraint? I have coded like this:
>
>              my $sth = $dbh->prepare($stmt);
>                          my $rv = $sth->execute() or die $DBI::errstr;
>                          if ( $rv < 0 ) {
>                                  print $DBI::errstr;
>                          }
>
> But, if the INSERT violates a constraint, it never gets the the evaluation
> of the $rv

I assume you mean the if($rv<0) is what it is not executed.

In perl this happens because something died. I assume it is not the
one you coded. This means some of your handles have the RaiseError
attribute, lookit up in the perldoc.

> Is this a setting for the DBI?

I do not remember if it has a global setting, but it sure has a
database handle setting ( which percolates down ). I use it routinely
for easier error handling.

I'm not sure if you know how to from your message, but if something is
dying you can use the block eval construct:

eval {
    # potentially dying code...
       my $sth = $dbh->prepare($stmt);
       my $rv = $sth->execute() or die $DBI::errstr;
       if ( $rv < 0 ) {
              print $DBI::errstr;
        }
     1; # This forces the eval to return true if execution gets here.
} or do {
    # Whatever you want, $@ has the codes.
}

to trap it in perl.

About RaiseError, it is common to set it to true in the handle, put
all your code in a sub() and catch it, in programs where you donot
have easy recovery of errors, and use local eval if needed to catch
this kind of prim. key violation things.

Francisco Olarte.