#!/usr/bin/perl
use strict;
use warnings;
use DBI;

#
# Connection parameters.
# Adjust them to suit your needs.
#
my $HOST            = '127.0.0.1';
my $user            = undef;
my $password        = undef;
my $defaults_file   = "$ENV{HOME}/.my.cnf";

my $dbh = DBI->connect('DBI:mysql:test;'
        . "host=$HOST"
	. ";mysql_read_default_file=$defaults_file",  
	$user,
        $password, 
        {RaiseError => 1, PrintError => 1}) 
	or die "can't connect ($DBI::errstr)\n";

#
# A sample table.
#
my $create_table = qq{
    CREATE TABLE t1 (
        id INT NOT NULL, 
        some_text CHAR(20),
        PRIMARY KEY (id)
    )
};

#
# The sample procedure.
# Notice that it contains 4 SELECT statements
#
my $create_procedure = qq{
    CREATE PROCEDURE p1(x INT, y INT)
    DETERMINISTIC
    BEGIN
        SELECT 
            x ;
        SELECT 
            x AS first_param, 
            y AS second_param;
        SELECT 
            x, 
            y, 
            x + y AS sum_xy, 
            x * y AS prod_xy; 
        SELECT *  FROM t1;
    END
};

#
# creating the test table and procedure
#
$dbh->do(qq{DROP TABLE IF EXISTS t1});
$dbh->do(qq{DROP PROCEDURE IF EXISTS p1});
$dbh->do($create_table);
$dbh->do($create_procedure);

#
# inserting a few rows in the table
#
$dbh->do(qq{INSERT INTO t1 VALUES 
        (1, 'aaa'), 
        (2, 'bbb'), 
        (3, 'ccc')});

#
# Calling the procedure
#
my $sth = $dbh->prepare(qq{CALL p1(2,5)});

$sth->execute();

my $more_results;
my $count = 0;
do {
    # 
    # The loop must run at least once
    #
    $count++;
    print "\ndataset $count\n";

    # 
    # The dataset is isolated. Each one has its own column names
    #
    my $names = $sth->{NAME};
    print_line($names); 
    print_line([map {'-' x length $_} @$names]); 

    #
    # Now we can get the data as if we were dealing
    # with a normal dataset,
    #
    # either all records at once
    #
    if ($count % 2 == 0 ) {
        my $rows = $sth->fetchall_arrayref();
        print_line ($_) for @$rows; 
    }
    #
    # or one by one.
    #
    else {
        while (my $row = $sth->fetchrow_arrayref()) {
            print_line ($row);
        }
    }
    #
    # Finally, check if more datasets are available
    #
} while ( $more_results = $sth->more_results);

sub print_line {
    my ($line) = @_;
    print q{  }, join(q{ }, map {sprintf '%-12s', $_} @$line),"\n";
}