#! /usr/bin/perl

# box_out - run a query and print output in boxed format

use DBI;
use strict;


my ($dsn, $db);
my (%attr) = ( RaiseError => 1 );
my ($dbh);

$db = "samp_db";
$dsn = "dbi:mysql:$db;mysql_read_default_file=$ENV{HOME}/.my.cnf";
$dbh = DBI->connect ($dsn, undef, undef, \%attr);

# MAIN-BODY
my ($sth) = $dbh->prepare (qq{
	SELECT last_name, first_name, city, state
	FROM president ORDER BY last_name, first_name
});
$sth->execute (); # attributes should be available after this call

# actual maximum widths of column values in result set
my (@wid) = @{$sth->{mysql_max_length}};
# number of columns
my ($ncols) = scalar (@wid);

# adjust column widths if column headings are wider than data values
for (my $i = 0; $i < $ncols; $i++)
{
my ($name_wid) = length ($sth->{NAME}->[$i]);

	$wid[$i] = $name_wid if $wid[$i] < $name_wid;
}

# print output
print_dashes (\@wid, $ncols);            	# row of dashes
print_row ($sth->{NAME}, \@wid, $ncols); 	# column headings
print_dashes (\@wid, $ncols);            	# row of dashes
while (my $ary_ref = $sth->fetch)
{
	print_row ($ary_ref, \@wid, $ncols); 	# row data values
}
print_dashes (\@wid, $ncols);            	# row of dashes
$sth->finish ();
# MAIN-BODY

$dbh->disconnect ();
exit (0);

# PRINT-FUNCTIONS
sub print_dashes
{
my ($wid_ary_ref) = shift;	# column widths
my ($cols) = shift;			# number of columns

	print "+";
	for (my $i = 0; $i < $cols; $i++)
	{
		print "-" x ($wid_ary_ref->[$i]+2) . "+";
	}
	print "\n";
}

# print row of data.  (doesn't right-align numeric columns)

sub print_row
{
my ($val_ary_ref) = shift;	# column values
my ($wid_ary_ref) = shift;	# column widths
my ($cols) = shift;			# number of columns

	print "|";
	for (my $i = 0; $i < $cols; $i++)
	{
		printf " %-*s |", $wid_ary_ref->[$i],
				defined ($val_ary_ref->[$i]) ? $val_ary_ref->[$i] : "NULL";
	}
	print "\n";
}
# PRINT-FUNCTIONS