#!/usr/bin/perl -wT<\n> # insert-new-person.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}); # 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_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; $dbh->disconnect;