OxPM: Handling errors from DBI

[Andrew Chadwick]

(I'm re-sending this unsigned and using my subscription address. If
it's a duplicate of something you already received, please ignore it)

On Fri, Oct 17, 2003 at 01:54:29PM +0100, James Davis wrote:
> I'm not exactly an expert in Perl and have found difficulty in getting a
> concise answer to my questions from Google. I've written a perl script
> that uses DBI to insert data into a MySQL table. However, the script will
> evoke all sorts of nastyness should MySQL be unavailble, or become
> unavailable during the execution of the script. I don't want to loose my
> data! :-)
>
> What's the best way to catch these errors so that I can recover sanely and
> spool the data for insertion into the table at a later time when MySQL is
> available again?

It's all there in the manpage, at least on my system :)
DBD::mysql can be instructed to throw any errors it encounters through
the normal Perl mechanism. These can be caught and reported via an
eval/if($@) incantation, which is a bit like try/catch in other
languages.

If you're prepared to queue the data you want to insert in the current
Perl process, then something like the following should do the trick.
You may want to split the connect/tidyup parts into other fragments of
code if it's to be a long-running process: there's no special magic to
doing everything in just a single eval {}.


#!/usr/bin/perl -w
use strict;
use DBI;

my $ok = 0;
for my $try (1 .. 10)
{
        my $dbh;
        eval {  
                $dbh = DBI->connect
                        ("DBD:mysql:host=foobox.domain;database=bardb",
                         "thuduser", "bangpass",
                         {RaiseError => 1})
                    or die "Oops: couldn't connect to MySQL";
                my $sth = $dbh->prepare("
                        INSERT INTO MyTable
                                SET id = ?,
                                    field_a = ?
                                    field_b = ?
                ") or die "Whoops: failed to make query";
                $sth->execute(@ARGV) or die "Failed silently!";
                $sth->finish;
        };
        my $err = $@;
        $dbh->disconnect if defined $dbh;
        undef $dbh;
        if ($err)
        {
                warn $err;
                $ok = 0;
        }
        else
        {
                $ok = 1;
                last;
        }
        my $t = $try * 5;
        warn "$0: insert failed: sleeping for $t seconds\n";
        sleep $t;
}
if (! $ok)
{
        # Gracefully recover from a no-win situation: perhaps
        # write @ARGV to a file.
}
                                                       

-- 
Andrew Chadwick