Perl: Chose your Database Vendor on the command line!

I have created two identical databases. Well, when I say 'identical', one is MySQL and the other is PostgreSQL, but other than that they are pretty similar! They have the same schema and pretty much the same data. It occurred to me that the Perl DBI package hides the database details away from you so successfully that it would be possible to write a program that allowed you to specify the type of database you are accessing on the command line!

Firstly, my new version of the command line routine:

sub dbinfo() {
        use Term::ReadKey;
        my $helpinstructions = "Valid parameters are\n-h {host}\n" .
                               "-d {database}\n" . 
                               "-u {user}\n" .
                               "-p [password]\n" .
                               "-dms [mysql/pgsql]\n";
        my %mydbinfo = (        db => "",
                                host => "",
                                user => "",
                                passwd => "",
                                dms => ""
        );


        die $helpinstructions unless ($#ARGV > 5);

        for (my $i=0; $i <= $#ARGV; $i++) {
                $ARGV[$i]= "-?" if ($ARGV[$i] ne '-h' and
                                    $ARGV[$i] ne '-d' and
                                    $ARGV[$i] ne '-u' and
                                    $ARGV[$i] ne '-dms' and
                                    $ARGV[$i] ne '-p' );
                die $helpinstructions if ( $ARGV[$i] eq "-?" ) ;
                $mydbinfo{db} = $ARGV[++$i] if ( $ARGV[$i] eq "-d" ) ;
                $mydbinfo{host} = $ARGV[++$i] if ( $ARGV[$i] eq "-h" ) ;
                $mydbinfo{user} = $ARGV[++$i] if ( $ARGV[$i] eq "-u" ) ;
                $mydbinfo{dms} = $ARGV[++$i] if ( $ARGV[$i] eq "-dms" ) ;
                if ( $ARGV[$i] eq "-p" ) {
                        if ( $i ne $#ARGV) {
                                $mydbinfo{passwd} = $ARGV[++$i];
                        }
                        else {
                                print "\nPlease Enter passwd: ";
                                ReadMode 'noecho';
                                chomp($mydbinfo{passwd} = ReadLine);
                                ReadMode 'normal';
                                print "\n\n";
                        }
                }
        
        }
        die $helpinstructions unless ( $mydbinfo{dms} eq 'mysql' or
                                       $mydbinfo{dms} eq 'pgsql');
        $mydbinfo{dms} = 'PgPP' if ($mydbinfo{dms} eq 'pgsql');
        return %mydbinfo;
}
return true;

And then the program that calls it:

#!/usr/bin/perl
use strict;
use DBI();

require "D:\\Documents and Settings\\timj\\perl\\choosedbcommanddbsettings.pl" or
die "Can't Open DB Settings File";


my %mydbinfo = dbinfo();
my $dbconnect = "DBI:" . $mydbinfo{dms} . ":database=" . $mydbinfo{db} . ";host=" . $mydbinfo{host} ;

my $dbh1 = DBI->connect($dbconnect,$mydbinfo{user}, $mydbinfo{passwd}, { raiseError => 1, AutoCommit => 0 })
|| die "Database connection not made: $DBI::errstr";
my $sql = "SELECT artist.name, cd.title FROM artist, cdtable AS cd WHERE artist.id = cd.artid";
my $sth = $dbh1->prepare($sql);

$sth->execute();

my( $name, $title );
$sth->bind_columns( \$name, \$title );

print "Name\t\tTitle\n";
while( $sth->fetch() ) {
        print "$name\t$title\n";
}
$dbh1->disconnect() if($dbh1);

and here's the output:

D:\Documents and Settings\timj\perl>choosedbreport.pl -dms mysql -h localhost -u timj -d cddb -p

Please Enter passwd:

Name            Title
Phatfish        Guaranteed
Phatfish        15
Bethany Dillon  Waking Up
Bethany Dillon  Bethany Dillon
Bethany Dillon  Imagination
Nick Drake      Made To Love Magic
Nick Drake      Five Leaves Left
Nick Drake      Bryter Layter

D:\Documents and Settings\timj\perl>choosedbreport.pl -dms pgsql -h localhost -u timj -d cddb -p

Please Enter passwd:

Name            Title
Nick Drake      Made To Love Magic
Nick Drake      Five Leaves Left
Nick Drake      Bryter Layter
Bethany Dillon  Waking Up
Bethany Dillon  Bethany Dillon
Bethany Dillon  Imagination
Phatfish        Guaranteed
Phatfish        15
Portland        These Broken Hands

D:\Documents and Settings\timj\perl>

Maybe not very useful, but it's a good demonstration of the power of DBI();