dws is quite right. I agree, one gets bored typing in all that stuff. I have a few subroutines in a local module that I use with pretty much anything I write:

package GS::DBIUtilities;
require Exporter;
@ISA = ("Exporter");

use DBI;

my $module_dbh;

sub
GetDBH
{

#--------------------------------------------------------------
# Connect to the amt db and return a db
#--------------------------------------------------------------

    my $db = shift;
    $module_dbh = DBI->connect("DBI:mysql:database=$db", "xxxxx", "xxx
+xx");
    return $module_dbh;
}


sub
DropDBH
{

#---------------------------------------------------------------
# Disconnect from DB
#---------------------------------------------------------------

    $module_dbh->disconnect if $module_dbh;
}


sub
InsertMultipleValues
{

#---------------------------------------------------------------
# Inserts contents of a hashref into the db table specified
#---------------------------------------------------------------

    my $dbh = shift;
    my $table = shift;
    my $Inserts = shift;

    my @cols = keys %$Inserts;
    my @vals = @$Inserts{@cols};
    my $cols = join ',', @cols;
    my $places = '?,' x @vals;
    chop $places;

    my $sth = $dbh->prepare("INSERT INTO $table ($cols) VALUES ($place
+s)") or die $dbh->errstr;
    $sth->execute(@vals) or die "$dbh->errstr : $table";
}


sub
ReplaceMultipleValues
{

#---------------------------------------------------------------
# Replaces contents of a hashref into the db table specified
#---------------------------------------------------------------

    my $dbh = shift;
    my $table = shift;
    my $Replaces = shift;

    my @cols = keys %$Replaces;
    my @vals = @$Replaces{@cols};
    my $cols = join ',', @cols;
    my $places = '?,' x @vals;
    chop $places;

    my $sth = $dbh->prepare("REPLACE INTO $table ($cols) VALUES ($plac
+es)") or die $dbh->errstr;
    $sth->execute(@vals) or die $dbh->errstr;
}


sub
FetchSingleItem
{
#---------------------------------------------------------------
# Fetch a single item from a database
#---------------------------------------------------------------

    my $dbh = shift;
    my $FetchCol = shift;
    my $table = shift;
    my $SearchCol = shift;
    my $SearchVal = shift;
   
    my $sth = $dbh->prepare("SELECT $FetchCol FROM $table WHERE $Searc
+hCol = ? LIMIT 1") or die $dbh->errstr;
    $sth->execute($SearchVal);
    my @ref = $sth->fetchrow_array;

    return $ref[0];
}


sub
InsertAndGetID
{

#---------------------------------------------------------------
# inserts an entry into a db and gets the auto_increment ID
#---------------------------------------------------------------

    my $dbh = shift;
    my $table = shift;
    my $Inserts = shift;
    my $IDCol = shift;

    $Inserts->{$IDCol} = 'NULL';
    
    $dbh->do("LOCK TABLES $table WRITE") or die $dbh->errstr;

    InsertMultipleValues($dbh,$table,$Inserts);

    $sth = $dbh->prepare("SELECT LAST_INSERT_ID() FROM $table") or die
+ $dbh->errstr;
    $sth->execute or die $dbh->errstr;
    my @ary = $sth->fetchrow_array or die $dbh->errstr;

    $dbh->do("UNLOCK TABLES") or die $dbh->errstr;
    $sth->finish;

    return $ary[0];
}


sub
FetchStar
{

#---------------------------------------------------------------
# Retrieves the whole of each row that matches the submitted
# criteria.   Returns a hashref if there is only one row,
# otherwise a ref to an array of hashes.
#---------------------------------------------------------------

    my $dbh = shift;
    my $table = shift;
    my $SearchCol = shift;
    my $SearchVal = shift;
    
    my $sth = $dbh->prepare("SELECT * FROM $table WHERE $SearchCol = ?
+") or die $dbh->errstr;
    $sth->execute($SearchVal);

    my @returns;
    while (my $ref = $sth->fetchrow_hashref) {
        push @returns, $ref;
    }

    if (@returns <= 1) {
        return $returns[0];
    }
    else {
        return \@returns;
    }
}



@EXPORT = qw/
    GetDBH
    DropDBH
    InsertMultipleValues
    ReplaceMultipleValues
    FetchStar
    FetchSingleItem
    InsertAndGetID
/;

1;#
[download]

§ George Sherston