I wrote this to avoid writing the same kind of DBI calls in my program. I haven't used this module that much yet. but i would like to hear what your thought to it.
package YourOrg::DB;
use strict;
use warnings;
use DBI;
# stub method. should be implemented from subclass.
#
# Return: a database handler.
sub dbh {}
# ( sql => $sql, pholder => [..,..] )
sub row_arrayref {
my $self = shift;
my %parm = @_;
my $sth = $self->dbh->prepare_cached( $parm{sql} );
$sth->execute( @{ $parm{pholder} } );
return $sth->fetchrow_arrayref;
}
sub row_hashref {
my $self = shift;
my %parm = @_;
my $sth = $self->dbh->prepare_cached( $parm{sql} );
$sth->execute( @{ $parm{pholder} } );
return $sth->fetchrow_hashref( $parm{name} );
}
sub all_hashref {
my $self = shift;
my %parm = @_;
my $sth = $self->dbh->prepare_cached( $parm{sql} );
$sth->execute( @{ $parm{pholder} } );
return $sth->fetchall_hashref( $parm{name} );
}
sub all_arrayref {
my $self = shift;
my %parm = @_;
my $sth = $self->dbh->prepare_cached( $parm{sql} );
$sth->execute( @{ $parm{pholder} } );
return $sth->fetchall_arrayref;
}
# ( sql => $sql, pholder => [ ..,..] )
sub all_AoHref {
my $self = shift;
my %parm = @_;
my $sth = $self->dbh->prepare_cached( $parm{sql} );
$sth->execute( @{ $parm{pholder} } );
my ( %row , @rows );
$sth->bind_columns( \( @row{ @{$sth->{NAME_lc}} } ) );
while ( $sth->fetch ) {
push @rows, { %row };
}
return \@rows;
}
1;
### subclass YourOrg::Db
package MyApp::DB;
use base 'YourOrg::Db';
use strict;
use warnings;
sub dbh {
return DBI->connect_cached( "DBName",
"DBUser",
"DBPass",
{ RaiseError => 1, PrintError => 0 }
);
}
1;
### in your code..
use MyApp::DB;
my $row = MyApp::DB->row_arrayref( sql => 'select * from mytable where
+ username = ?', pholder => [ 'Qiang' ] )
print $row->[0];