#!/usr/bin/perl

# Perl DBI basic sample script.
# Version 1.1
# Nic Gibson
# nicg@corbas.co.uk

use strict;				# Require variable declaration.
use warnings;			# Turn on warnings.
use DBI;				# use the DataBase Interface

# Set up the data source name. In the real world, we would also need
# a user name and password.
my $dsn = 'DBI:mysql:database=test';


# Connect to the database. Returns a database connection object.
# Global DBI error string contains object creation errors.
my $dbh = DBI->connect($dsn);
die "Unable to connect: $DBI::errstr\n" unless $dbh;

# Set up our query string - just an sql statement.
my $sql = 'select name, row, col from students order by row, col';

# Prepare the query. The docs show shortcuts for this (perldoc DBI).
# On success, returns a 'statement handle'  - an object representing
# our SQL statement.
my $sth = $dbh->prepare($sql);

# Fail with the appropriate error message if we don't get a prepared
# statement (in general - there is an error in the SQL).
unless ($sth)
{
	my $error = $dbh->errstr;
	$dbh->disconnect;
	die "SQL Query Problem: $error\n";
}

# Execute queries with the execute statement. Returns a true value 
# on success and false on failure.	
my $result = $sth->execute;

# If successful, get the data.
if ($result)
{
	# fetchrow_array returns the columns from our select
	# statement (see the docs for the results of running
	# non-select statements). On failure or end of results
	# returns an empty array. Empty arrays evaluate to 
	# zero in scalar context, leading to a false while test.
	while (my @row = $sth->fetchrow_array)
	{
		# Print out the data - perldoc printf.
		printf("%32s%10d%10d\n", @row);
	}

	# Did we complete with an error or not? If we had an
	# error, exit with the error message.
	if ($sth->errstr)
	{
		my $error = $sth->$dbh->errstr;
		$dbh->disconnect;
		die "Error retrieving data: $error\n";
	}
	
	# Otherwise we are done and we can exit normally.
	$dbh->disconnect;
}

# Did an error occur?
elsif ($sth->errstr)
{
	my $error = $sth->errstr;
	$dbh->disconnect;
	die "Error executing query: $error\n";
}