Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

Simple interface to DBI

by Qiang (Friar)
on May 11, 2007 at 14:47 UTC ( #614927=sourcecode: print w/replies, xml ) Need Help??
Category: Database Programming
Author/Contact Info
Description: 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];
Replies are listed 'Best First'.
Re: Simple interface to DBI
by Tux (Canon) on May 11, 2007 at 15:29 UTC

    Before you start re-inventing wheels (and have both FUN and learn from it), I'd suggest having a look at juerd's CPAN:DBIx-Simple.


    Enjoy, Have FUN! H.Merijn
      i knew about DBIx::Simple but don't know much. i will give it a go again. thanks.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: sourcecode [id://614927]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (5)
As of 2022-05-24 14:44 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you prefer to work remotely?



    Results (83 votes). Check out past polls.

    Notices?