#!/cs/local/bin/perl
#-----------------------------------------------------------------------------
# SQLSHELL : A really simple SQL shell that acts a bit like DB2 interactive.
#    It reads SQL queries in, and fetches and reports the resulting rows.
#    Type <exit> to leave.
#-----------------------------------------------------------------------------

use DBI;

$dbname   = 'dbi:DB2:c3421a00';

# The second and third arguments are 'user' and 'password'.
# Leaving them undefined means the program will drop through as 'user' the
# same as the unix user and password by default.
$dbh = DBI->connect($dbname,undef,undef, { AutoCommit => 0 }) ||
       die "Error connecting $DBI::errstr\n";

while(1) {
    print "SQL> ";                      # Prompt
    $stmt = <STDIN>;
    last unless defined($stmt);
    last if ($stmt =~ /^\s*exit/i);
    next if ($stmt =~ /^\s*$/);

    chomp ($stmt);
    $stmt =~ s/;\s*$//;   # Toss the ";" if any and anything trailing.
    $stmt =~ tr/A-Z/a-z/; # Cast everything in lowercase;

    printf "Statement = <%s>\n", $stmt;

    if ($stmt =~ /^\s*select/i) {
        $sth = $dbh->prepare($stmt);
        if ($DBI::err) {
            print STDERR "$DBI::errstr\n";
            next;
        }

        $sth->execute() ;
        if ($DBI::err) {
            print STDERR "$DBI::errstr\n";
            next;
        }

        my $rl_names = $sth->{NAME};         # ref. to list of col. names
        my $rowcount = 0;
        while (@results = $sth->fetchrow) {  # retrieve results
            $rowcount++;
            if ($DBI::err) {
                print STDERR $DBI::errstr,"\n";
                last;
            }
            foreach $field_name (@$rl_names) {
                printf "%10s: %s\n", $field_name, shift @results;
            }
            print "\n";
        }
        $sth->finish;
        printf "%d tuples reported.\n", $rowcount;
    } else {
        $dbh->do($stmt);
        if ($DBI::err) {
            print STDERR "$DBI::errstr\n";
            next;
        }
    }
}

$dbh->commit;
$dbh->disconnect;