#!/usr/bin/perl -wT<\n> # 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;