Here
is a sample Perl/DBI program.
Happy hacking.
sqlshell.pl
|
|
#!/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;
|
|
|
Of course, to use this program, you would want to replace
godfrey with your DB2 user name
and c3421a00 with whatever database you want.
A cool thing about Perl/DBI
(and CLI, in general)
is that one could make this program work effortlessly
with other database systems.
Replacing DB2 with, say, ORACLE
in the $dbname assignment statement would be all that is needed.
|