Listing 8: Insert-New-Person-2.pl, Using eval to Trap Errors

#!/usr/bin/perl -wT
# insert-new-person-2.pl

use strict;
use DBI;

# Get parameters
my ($new_name, $new_email, $monthly_gross) = @ARGV;
die qw { "You need to specify (a) name
   (b) e-mail address, (c) monthly gross"
       } unless (@ARGV == 3);

# Connect to the PostgreSQL server with DBD::Pg
my $host = 'ahad-haam';
my $user = 'reuven';
my $password = '';
my $dsn = "DBI:Pg:dbname=test;host=$host;";
my $dbh =
    DBI->connect($dsn, $user, $password,
       {RaiseError => 1, AutoCommit => 0});

eval {

    # Insert the new employee into the People table
    my $sql = "INSERT INTO People ";
    $sql .= " (name, email) ";
    $sql .= "VALUES ";
    $sql .= " (?, ?) ";

    $dbh->do($sql, undef, $new_name, $new_email);

    # Get the person_id that we just inserted
    $sql = "SELECT currval('people_person ";
    $sql = "_id_seq')";

    my $person_id = $dbh->selectrow_array($sql);

    # Now insert a row into the Salaries table, using $person_id
    $sql = "INSERT INTO Salaries ";
    $sql .= " (person_id, monthly_gross, as_of, ";
    $sql .= " approved_by, notes) ";
    $sql .= "VALUES ";
    $sql .= " (?, ?, NOW(), ?, ?) ";

    # We will assume here that the boss has a
    # person_id of 1 -- but
    # hard-wiring this sort of information is a
    # bad idea in practice.
    $dbh->do($sql, undef, $person_id, $monthly_gross,
                        1, "Testing insert");

    # If we do not commit this transaction,
    # PostgreSQL will behave as
    # if we rolled it back and it never happened!
    $dbh->commit();
};

# If the eval died in the middle, then $@ was set.
# We can now perform
# a rollback, as well as give the user a message.

if ($@)
{
    print "Sorry, but the transaction failed.
                Better luck next time!\n";
    $dbh->rollback();
}

$dbh->disconnect;