perl DBI - DButils

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", "xxxxx");
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
($places)") 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
($places)") 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
$SearchCol = ? 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;#