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];
[download]

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.