#!/usr/bin/perl
#########
#
# Perl/DBI demo.
#
# The environment variables DB_DATA, DB_USER, DB_PASS must be
# set before running this script.
#
# The database tables should be set up already;
# see populate_tables.sql
#
#################
use strict;
use warnings;
use DBI;
my $dsn = "DBI:mysql:database=$ENV{DB_DATA}";
my $dbh = DBI->connect( $dsn, $ENV{DB_USER}, $ENV{DB_PASS}, {RaiseError=>1} )
  or die "Oops - couldn't connect to '$dsn'";

# This eval{ } block traps fatal errors.
# If an error occurs, execution jumps past the block, and $@ has the error.

eval {
  my $sql = "SELECT Person.name, Course.name 
   FROM Person,Course,Registration,Status
   WHERE Course.name = ? AND  Status.name   = ?
   AND  Course.id = Registration.course AND  Person.id = Registration.person 
   AND  Registration.status = Status.id ";
  my $sth = $dbh->prepare($sql);
  $sth->execute('chemistry', 'student');
  while (my $ref = $sth->fetchrow_arrayref){
    printf " '%s' '%s' \n", @$ref;
  }
  $sth->finish;
};

print "Oops - error in SQL evaluation : '$@'\n" if $@;
$dbh->disconnect;
