#!/usr/bin/perl -w
#######################################
# Example of connection to SQL database via a perl script.
# This one isn't a .cgi; it just runs from the command line.
########################################
use DBI;
my ($user,    $dbname,  $table,   $passwd) = 
   ('msie02', 'msie02', 'sample', 'olymp.02' );
my $attributes	= { RaiseError => 1,  # Errors throw exceptions, i.e. die.
		    AutoCommit => 0,  # Use transactions, i.e. commit/rollback
		  };
my $dbh = DBI->connect("dbi:Pg:dbname=$dbname", $user, $passwd, $attributes)
  or die(" couldn't connect: $DBI::err ");

# Because of RaiseError=>1, we shouldn't use "or die" again on $dbh.
# Instead, we should trap errors in an "eval" block.

# Because of AutoCommit=>0, we must explicitly commit or rollback
# any changes to the database.  This works well with trapping errors.

# "Try" - start transaction
eval {				# Do one "chunk" of stuff.
  my %ages = ( John => 12, Paul => 14, Ringo => 18 );
  my $sth = $dbh->prepare("INSERT INTO $table VALUES (?,?);");
  foreach my $person (keys %ages) {
    print "Trying to add ( $person , $ages{$person} ) \n";
    $sth->execute( $person, $ages{$person} );
  }
  my $allNames = $dbh->selectcol_arrayref("SELECT name FROM $table;");
  print "The names from the table are : " . join(",", @$allNames) . "\n";
  print "Committing changes \n";
  $dbh->commit;			# Commit changes if we get this far
};

# "Catch" - handle error (if any) that aborted the eval{}
if ($@) {
  warn "Transaction aborted : $@";
  $dbh->rollback;		# Undo changes.
}

print "Done. \n";
$dbh->disconnect();