Perl/DBI

For the Perl hacks out there, the DBI module for Perl is a great way to write programs that interact with the database. To see documentation on DBI, ask

% perldoc DBI

If you want to go this route to write your database application programs, I shall assume you know Perl and have access to documentation for Perl!

As stated above, Perl/DBI is akin to CLI. It is not really embedded SQL. Instead, DBI offers function calls within Perl to interact with the database.

An example Perl/DBI program

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:c3421a';
$user     = 'godfrey'; 
$password = '';

$dbh = DBI->connect($dbname,$user,$password, { 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 go3421 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.