#!/usr/bin/perl -wT
#
#  Written to illustrate various mechanisms of data retrieval from
#  a mysql database 'new'.
#
#  060402 liverpole -- created.
#

##############
### Strict ###
##############
use strict;
use warnings;


#################
### Libraries ###
#################
use Data::Dumper;
use DBI;


####################
### Main program ###
####################
my $dbh = connect_to_dbase('DBI:mysql:database=new', 'root', 'rootpass');
my $sql = "SELECT * FROM employee";

my $p1 = sql_hash($dbh, $sql);
printf "Results as hash  => '%s'\n", Dumper($p1);

my $p2 = sql_array($dbh, $sql);
printf "Results as array => '%s'\n", Dumper($p2);



###################
### Subroutines ###
###################
sub connect_to_dbase {
    my ($dsn, $user, $pass) = @_;
    my $dbh = DBI->connect($dsn, $user, $pass, { 'AutoCommit' => 1 });
    defined $dbh or die "Cannot connect to database '$dsn'\n";
    return $dbh;
}


sub sql_hash {
    my ($dbh,$sql) = @_;
    my $sth = $dbh->prepare($sql);
    $sth->execute() or die "Failed to execute SQL '$sql'\n";
    my @data;
    while (my $p = $sth->fetchrow_hashref) {
    	push @data, $p;
    }
    return \@data;
}


sub sql_array {
    my ($dbh,$sql) = @_;
    my $sth = $dbh->prepare($sql);
    $sth->execute() or die "Failed to execute SQL '$sql'\n";
    my @data;
    while (my $p = $sth->fetchrow_arrayref) {
        push @data, [ @$p ];
    }
    return \@data;
}