DBIx-FlexibleBinding
view release on metacpan or search on metacpan
lib/DBIx/FlexibleBinding.pm view on Meta::CPAN
else {
CRIT_PROXY_UNDEF( $fqpi )
unless $proxies{$fqpi};
my $proxy = $proxies{$fqpi};
$proxy->execute( @args )
if $proxy->isa( "$package\::st" );
return $proxy->getrows( @args );
}
return $proxies{$fqpi};
}
sub import
{
my ( $package, @args ) = @_;
my $caller = caller;
@_ = ( $package );
while ( @args ) {
my $arg = shift( @args );
if ( substr( $arg, 0, 1 ) eq '-' ) {
if ( $arg eq '-alias' || $arg eq '-as' ) {
my $ns_alias = shift @args;
$package->_create_namespace_alias( $ns_alias );
}
elsif ( $arg eq '-subs' ) {
my $list_of_sub_names = shift @args;
$package->_create_dbi_handle_proxies( $caller, $list_of_sub_names );
}
else {
CRIT_UNEXP_ARG( $arg );
}
}
else {
push @_, $arg;
}
}
goto &Exporter::import;
}
sub connect
{
my ( $invocant, $dsn, $user, $pass, $attr ) = @_;
$attr = {}
unless defined $attr;
$attr->{RootClass} = ref( $invocant ) || $invocant
unless defined $attr->{RootClass};
return $invocant->next::method( $dsn, $user, $pass, $attr );
}
package # Hide from PAUSE
DBIx::FlexibleBinding::db;
our $VERSION = '2.0.4'; # VERSION
BEGIN {
*_is_hashref = \&DBIx::FlexibleBinding::_is_hashref;
*_as_list_or_ref = \&DBIx::FlexibleBinding::_as_list_or_ref;
}
use Params::Callbacks 'callbacks';
use namespace::clean;
our @ISA = 'DBI::db';
sub do
{
my ( $callbacks, $dbh, $sth, @bind_values ) = &callbacks;
my $result;
unless ( ref $sth ) {
my $attr;
$attr = shift @bind_values
if _is_hashref( $bind_values[0] );
$sth = $dbh->prepare( $sth, $attr );
return undef
if $sth->err;
}
$result = $sth->execute( @bind_values );
return undef
if $sth->err;
local $_;
$result = $callbacks->smart_transform( $_ = $result )
if @$callbacks;
return $result;
}
sub prepare
{
my ( $dbh, $stmt, @args ) = @_;
my @params;
if ( $stmt =~ m{:\w+\b} ) {
@params = $stmt =~ m{:(\w+)\b}g;
$stmt =~ s{:\w+\b}{?}g;
}
elsif ( $stmt =~ m{\@\w+\b} ) {
@params = $stmt =~ m{(\@\w+)\b}g;
$stmt =~ s{\@\w+\b}{?}g;
}
elsif ( $stmt =~ m{\?\d+\b} ) {
@params = $stmt =~ m{\?(\d+)\b}g;
$stmt =~ s{\?\d+\b}{?}g;
}
else {
# No recognisable placeholders to extract/convert
}
my $sth = $dbh->next::method( $stmt, @args );
return $sth
unless defined $sth;
$sth->_init_private_attributes( \@params );
return $sth;
}
sub getrows_arrayref
{
my ( $callbacks, $dbh, $sth, @bind_values ) = &callbacks;
unless ( ref $sth ) {
my $attr;
$attr = shift( @bind_values )
if _is_hashref( $bind_values[0] );
$sth = $dbh->prepare( $sth, $attr );
return _as_list_or_ref( undef )
if $sth->err;
}
$sth->execute( @bind_values );
return _as_list_or_ref( undef )
if $sth->err;
return $sth->getrows_arrayref( $callbacks );
}
sub getrows_hashref
{
my ( $callbacks, $dbh, $sth, @bind_values ) = &callbacks;
unless ( ref $sth ) {
my $attr;
$attr = shift( @bind_values )
if _is_hashref( $bind_values[0] );
$sth = $dbh->prepare( $sth, $attr );
return _as_list_or_ref( undef )
if $sth->err;
}
$sth->execute( @bind_values );
return _as_list_or_ref( undef )
if $sth->err;
return $sth->getrows_hashref( $callbacks );
}
sub getrow_arrayref
{
my ( $callbacks, $dbh, $sth, @bind_values ) = &callbacks;
unless ( ref $sth ) {
my $attr;
$attr = shift( @bind_values )
if _is_hashref( $bind_values[0] );
$sth = $dbh->prepare( $sth, $attr );
return undef
if $sth->err;
}
$sth->execute( @bind_values );
return undef
if $sth->err;
return $sth->getrow_arrayref( $callbacks );
}
sub getrow_hashref
{
my ( $callbacks, $dbh, $sth, @bind_values ) = &callbacks;
unless ( ref $sth ) {
my $attr;
$attr = shift( @bind_values )
if _is_hashref( $bind_values[0] );
$sth = $dbh->prepare( $sth, $attr );
return undef
if $sth->err;
}
$sth->execute( @bind_values );
return undef
if $sth->err;
return $sth->getrow_hashref( $callbacks );
}
BEGIN {
*getrows = \&getrows_hashref;
*getrow = \&getrow_hashref;
}
package # Hide from PAUSE
DBIx::FlexibleBinding::st;
our $VERSION = '2.0.4'; # VERSION
BEGIN {
*_is_arrayref = \&DBIx::FlexibleBinding::_is_arrayref;
*_is_hashref = \&DBIx::FlexibleBinding::_is_hashref;
*_as_list_or_ref = \&DBIx::FlexibleBinding::_as_list_or_ref;
}
use List::MoreUtils ( 'any' );
use Params::Callbacks ( 'callbacks' );
use namespace::clean;
use message << 'EOF';
ERR_EXP_AHR Expected a reference to a HASH or ARRAY
ERR_MISSING_BIND_ID Binding identifier is missing
ERR_BAD_BIND_ID Bad binding identifier (%s)
EOF
our @ISA = 'DBI::st';
sub _priv_attr
{
my ( $sth, $name, $value, @more ) = @_;
return $sth->{private_dbix_flexbind}
unless @_ > 1;
$sth->{private_dbix_flexbind}{$name} = $value
if @_ > 2;
return $sth->{private_dbix_flexbind}{$name}
unless @more;
while ( @more ) {
$name = shift @more;
$sth->{private_dbix_flexbind}{$name} = shift @more;
}
return $sth;
}
sub _auto_bind
{
my ( $sth, $value ) = @_;
return $sth->_priv_attr( 'auto_bind' )
unless @_ > 1;
$sth->_priv_attr( auto_bind => !!$value );
return $sth;
}
sub _param_count
{
my ( $sth, $value ) = @_;
if ( @_ > 1 ) {
$sth->_priv_attr( 'param_count' => $value );
return $sth;
}
$sth->_priv_attr( 'param_count' => {} )
unless defined $sth->_priv_attr( 'param_count' );
return %{ $sth->_priv_attr( 'param_count' ) }
if wantarray;
return $sth->_priv_attr( 'param_count' );
}
sub _param_order
{
my ( $sth, $value ) = @_;
if ( @_ > 1 ) {
$sth->_priv_attr( param_order => $value );
return $sth;
}
$sth->_priv_attr( param_order => [] )
unless defined $sth->_priv_attr( 'param_order' );
return @{ $sth->_priv_attr( 'param_order' ) }
if wantarray;
return $sth->_priv_attr( 'param_order' );
lib/DBIx/FlexibleBinding.pm view on Meta::CPAN
unless _is_arrayref( $args[0] ) || _is_hashref( $args[0] );
return $sth->_bind_hashref( $args[0] )
if _is_hashref( $args[0] );
return $sth->_bind_arrayref( $args[0] )
if $sth->_using_numbered;
return $sth->_bind_hashref( { @{ $args[0] } } );
}
if ( @args ) {
return $sth->_bind_arrayref( \@args )
if $sth->_using_numbered;
return $sth->_bind_hashref( {@args} );
}
return $sth;
}
sub bind_param
{
my ( $sth, $param, $value, $attr ) = @_;
return $sth->set_err( $DBI::stderr, ERR_MISSING_BIND_ID )
unless $param;
return $sth->set_err( $DBI::stderr, ERR_BAD_BIND_ID( $param ) )
if $param =~ m{[^\@\w]};
my $result;
if ( $sth->_using_positional ) {
$result = $sth->next::method( $param, $value, $attr );
}
else {
my ( $pos, $count, %param_count ) = ( 0, 0, $sth->_param_count );
for my $identifier ( $sth->_param_order ) {
$pos++;
if ( $identifier eq $param ) {
last if ++$count > $param_count{$param};
$result = $sth->next::method( $pos, $value, $attr );
}
}
}
return $result;
}
sub execute
{
my ( $sth, @bind_values ) = @_;
my $rows;
if ( $sth->_auto_bind ) {
$sth->_bind( @bind_values );
$rows = $sth->next::method();
}
else {
if ( @bind_values == 1 && _is_arrayref( $bind_values[0] ) ) {
$rows = $sth->next::method( @{ $bind_values[0] } );
}
else {
$rows = $sth->next::method( @bind_values );
}
}
return $rows;
}
sub iterate
{
my ( $callbacks, $sth, @bind_values ) = &callbacks;
my $rows = $sth->execute( @bind_values );
return $rows unless defined $rows;
my $iter_fn = sub { $sth->getrow( $callbacks ) };
return bless( $iter_fn, 'DBIx::FlexibleBinding::Iterator' );
}
sub getrows_arrayref
{
my ( $callbacks, $sth, @args ) = &callbacks;
unless ( $sth->{Active} ) {
$sth->execute( @args );
return _as_list_or_ref( undef )
if $sth->err;
}
my $result = $sth->fetchall_arrayref;
return _as_list_or_ref( $result )
if $sth->err or not defined $result;
local $_;
$result = [ map { $callbacks->transform( $_ ) } @$result ]
if @$callbacks;
return _as_list_or_ref( $result );
}
sub getrows_hashref
{
my ( $callbacks, $sth, @args ) = &callbacks;
unless ( $sth->{Active} ) {
$sth->execute( @args );
return _as_list_or_ref( undef )
if $sth->err;
}
my $result = $sth->fetchall_arrayref( {} );
return _as_list_or_ref( $result )
if $sth->err or not defined $result;
local $_;
$result = [ map { $callbacks->transform( $_ ) } @$result ]
if @$callbacks;
return _as_list_or_ref( $result );
}
sub getrow_arrayref
{
my ( $callbacks, $sth, @args ) = &callbacks;
unless ( $sth->{Active} ) {
$sth->execute( @args );
return undef
if $sth->err;
}
my $result = $sth->fetchrow_arrayref;
return $result
if $sth->err or not defined $result;
local $_;
$result = [@$result];
$result = $callbacks->smart_transform( $_ = $result )
if @$callbacks;
return $result;
}
sub getrow_hashref
{
my ( $callbacks, $sth, @args ) = &callbacks;
unless ( $sth->{Active} ) {
$sth->execute( @args );
return undef
if $sth->err;
}
my $result = $sth->fetchrow_hashref;
return $result
if $sth->err or not defined $result;
local $_;
$result = $callbacks->smart_transform( $_ = $result )
if @$callbacks;
return $result;
}
BEGIN {
*getrows = \&getrows_hashref;
*getrow = \&getrow_hashref;
}
package # Hide from PAUSE
DBIx::FlexibleBinding::Iterator;
our $VERSION = '2.0.4'; # VERSION
use Params::Callbacks ( 'callbacks' );
use namespace::clean;
sub for_each
{
my ( $callbacks, $iter ) = &callbacks;
my @results;
local $_;
while ( my @items = $iter->() ) {
last if @items == 1 and not defined $items[0];
push @results, map { $callbacks->transform( $_ ) } @items;
}
return wantarray ? @results : \@results;
}
1;
=pod
=encoding utf8
=head1 NAME
DBIx::FlexibleBinding - Greater statement placeholder and data-binding flexibility.
=head1 VERSION
version 2.0.4
=head1 SYNOPSIS
This module extends the DBI allowing you choose from a variety of supported
parameter placeholder and binding patterns as well as offering simplified
ways to interact with datasources, while improving general readability.
#########################################################
# SCENARIO 1 #
# A connect followed by a prepare-execute-process cycle #
#########################################################
use DBIx::FlexibleBinding;
use constant DSN => 'dbi:mysql:test;host=127.0.0.1';
use constant SQL => << '//';
SELECT solarSystemName AS name
FROM mapsolarsystems
WHERE regional = :is_regional
AND security >= :minimum_security
//
# Pretty standard connect, just with the new DBI subclass ...
#
my $dbh = DBIx::FlexibleBinding->connect(DSN, '', '', { RaiseError => 1 });
# Prepare statement using named placeholders (not bad for MySQL, eh) ...
#
my $sth = $dbh->prepare(SQL);
# Execute the statement (parameter binding is automatic) ...
#
my $rv = $sth->execute(is_regional => 1,
minimum_security => 1.0);
# Fetch and transform rows with a blocking callback to get only the data you
# want without cluttering the place up with intermediate state ...
#
my @system_names = $sth->getrows_hashref(callback { $_->{name} });
############################################################################
# SCENARIO 2 #
# Let's simplify the previous scenario using the database handle's version #
# of that getrows_hashref method. #
lib/DBIx/FlexibleBinding.pm view on Meta::CPAN
# Or, initialise by passing in a DBI database handle.
# The handle is also the return value.
#
MyDB $dbh;
# Once initialised, use the subroutine as you would a DBI database handle.
#
my $statement = << '//';
SELECT solarSystemName AS name
FROM mapsolarsystems
WHERE security >= :minimum_security
//
my $sth = MyDB->prepare($statement);
# Or use it as an expressive time-saver!
#
my $array_of_hashrefs = MyDB($statement, security => 1.0);
my @system_names = MyDB($statement, minimum_security => 1.0, callback {
return $_->{name};
});
MyDB $statement, minimum_security => 1.0, callback {
my ($row) = @_;
print "$row->{name}\n";
};
=head1 CLASS METHODS
=head2 connect
$dbh = DBIx::FlexibleBinding->connect($data_source, $user, $pass)
or die $DBI::errstr;
$dbh = DBIx::FlexibleBinding->connect($data_source, $user, $pass, \%attr)
or die $DBI::errstr;
Establishes a database connection, or session, to the requested data_source and
returns a database handle object if the connection succeeds or undef if it does
not.
Refer to L<http://search.cpan.org/dist/DBI/DBI.pm#connect> for a more detailed
description of this method.
=head1 DATABASE HANDLE METHODS
=head2 do
$rows = $dbh->do($statement_string) or die $dbh->errstr;
$rows = $dbh->do($statement_string, @bind_values) or die $dbh->errstr;
$rows = $dbh->do($statement_string, \%attr) or die $dbh->errstr;
$rows = $dbh->do($statement_string, \%attr, @bind_values) or die $dbh->errstr;
$rows = $dbh->do($statement_handle) or die $dbh->errstr;
$rows = $dbh->do($statement_handle, @bind_values) or die $dbh->errstr;
Prepares (if necessary) and executes a single statement. Returns the number of
rows affected or undef on error. A return value of -1 means the number of rows
is not known, not applicable, or not available. When no rows have been affected
this method continues the C<DBI> tradition of returning C<0E0> on successful
execution and C<undef> on failure.
The C<do> method accepts optional callbacks for further processing of the result.
The C<do> implementation provided by this module allows for some minor
deviations in usage over the standard C<DBI> implementation. In spite
of this, the new method may be used just like the original.
Refer to L<http://search.cpan.org/dist/DBI/DBI.pm#do> for a more detailed
description of this method.
B<Examples>
=over
=item 1. Statement attributes are now optional:
$sql = << '//';
UPDATE employees
SET salary = :salary
WHERE employee_id = :employee_id
//
$dbh->do($sql, employee_id => 52, salary => 35_000)
or die $dbh->errstr;
A reference to the statement attributes hash is no longer required, even if it's
empty. If, however, a hash reference is supplied as the first parameter then it
would be used for that purpose.
=item 2. Prepared statements now may be re-used:
$sth = $dbh->prepare(<< '//');
UPDATE employees
SET salary = ?
WHERE employee_id = ?
//
$dbh->do($sth, 35_000, 52) or die $dbh->errstr;
A prepared statement may also be used in lieu of a statement string. In such
cases, referencing a statement attributes hash is neither required nor expected.
=back
=head2 prepare
$sth = $dbh->prepare($statement_string);
$sth = $dbh->prepare($statement_string, \%attr);
Prepares a statement for later execution by the database engine and returns a
reference to a statement handle object.
Refer to L<http://search.cpan.org/dist/DBI/DBI.pm#prepare> for a more detailed
description of this method.
B<Examples>
=over
=item 1. Prepare a statement using positional placeholders:
$sql = << '//';
UPDATE employees
SET salary = ?
WHERE employee_id = ?
//
$sth = $dbh->prepare($sql);
=item 2. Prepare a statement using named placeholders:
I<(Yes, even for those MySQL connections!)>
$sql = << '//';
UPDATE employees
SET salary = :salary
WHERE employee_id = :employee_id
//
$sth = $dbh->prepare($sql);
=back
=head2 getrows_arrayref I<(database handles)>
$results = $dbh->getrows_arrayref($statement_string, @bind_values);
@results = $dbh->getrows_arrayref($statement_string, @bind_values);
$results = $dbh->getrows_arrayref($statement_string, \%attr, @bind_values);
@results = $dbh->getrows_arrayref($statement_string, \%attr, @bind_values);
$results = $dbh->getrows_arrayref($statement_handle, @bind_values);
@results = $dbh->getrows_arrayref($statement_handle, @bind_values);
Prepares (if necessary) and executes a single statement with the specified data
bindings and fetches the result set as an array of array references.
The C<getrows_arrayref> method accepts optional callbacks for further processing
of the results by the caller.
B<Examples>
=over
=item 1. Prepare, execute it then get the results as a reference:
$sql = << '//';
SELECT solarSystemName AS name
, security
FROM mapsolarsystems
WHERE regional = 1
AND security >= :minimum_security
//
$systems = $dbh->getrows_arrayref($sql, minimum_security => 1.0);
# Returns a structure something like this:
#
# [ [ 'Kisogo', '1' ],
# [ 'New Caldari', '1' ],
# [ 'Amarr', '1' ],
# [ 'Bourynes', '1' ],
# [ 'Ryddinjorn', '1' ],
# [ 'Luminaire', '1' ],
# [ 'Duripant', '1' ],
# [ 'Yulai', '1' ] ]
=item 2. Re-use a prepared statement, execute it then return the results as a list:
We'll use the query from Example 1 but have the results returned as a list for
further processing by the caller.
$sth = $dbh->prepare($sql);
@systems = $dbh->getrows_arrayref($sql, minimum_security => 1.0);
for my $system (@systems) {
printf "%-11s %.1f\n", @$system;
}
# Output:
#
# Kisogo 1.0
# New Caldari 1.0
# Amarr 1.0
# Bourynes 1.0
# Ryddinjorn 1.0
# Luminaire 1.0
# Duripant 1.0
# Yulai 1.0
=item 3. Re-use a prepared statement, execute it then return modified results as a
reference:
We'll use the query from Example 1 but have the results returned as a list
for further processing by a caller who will be using callbacks to modify those
results.
$sth = $dbh->prepare($sql);
$systems = $dbh->getrows_arrayref($sql, minimum_security => 1.0, callback {
my ($row) = @_;
return sprintf("%-11s %.1f\n", @$row);
});
# Returns a structure something like this:
#
# [ 'Kisogo 1.0',
# 'New Caldari 1.0',
# 'Amarr 1.0',
# 'Bourynes 1.0',
# 'Ryddinjorn 1.0',
# 'Luminaire 1.0',
# 'Duripant 1.0',
# 'Yulai 1.0' ]
=back
=head2 getrows_hashref I<(database handles)>
$results = $dbh->getrows_hashref($statement_string, @bind_values);
@results = $dbh->getrows_hashref($statement_string, @bind_values);
$results = $dbh->getrows_hashref($statement_string, \%attr, @bind_values);
@results = $dbh->getrows_hashref($statement_string, \%attr, @bind_values);
$results = $dbh->getrows_hashref($statement_handle, @bind_values);
@results = $dbh->getrows_hashref($statement_handle, @bind_values);
Prepares (if necessary) and executes a single statement with the specified data
bindings and fetches the result set as an array of hash references.
The C<getrows_hashref> method accepts optional callbacks for further processing
of the results by the caller.
B<Examples>
=over
=item 1. Prepare, execute it then get the results as a reference:
$sql = << '//';
SELECT solarSystemName AS name
, security
FROM mapsolarsystems
WHERE regional = 1
AND security >= :minimum_security
//
$systems = $dbh->getrows_hashref($sql, minimum_security => 1.0);
# Returns a structure something like this:
#
# [ { name => 'Kisogo', security => '1' },
# { name => 'New Caldari', security => '1' },
# { name => 'Amarr', security => '1' },
# { name => 'Bourynes', security => '1' },
# { name => 'Ryddinjorn', security => '1' },
# { name => 'Luminaire', security => '1' },
# { name => 'Duripant', security => '1' },
# { name => 'Yulai', security => '1' } ]
=item 2. Re-use a prepared statement, execute it then return the results as a list:
We'll use the query from Example 1 but have the results returned as a list for
further processing by the caller.
$sth = $dbh->prepare($sql);
@systems = $dbh->getrows_hashref($sql, minimum_security => 1.0);
for my $system (@systems) {
printf "%-11s %.1f\n", @{$system}{'name', 'security'}; # Hash slice
}
# Output:
#
# Kisogo 1.0
# New Caldari 1.0
# Amarr 1.0
# Bourynes 1.0
# Ryddinjorn 1.0
# Luminaire 1.0
# Duripant 1.0
# Yulai 1.0
=item 3. Re-use a prepared statement, execute it then return modified results as a
reference:
We'll use the query from Example 1 but have the results returned as a list
for further processing by a caller who will be using callbacks to modify those
results.
$sth = $dbh->prepare($sql);
$systems = $dbh->getrows_hashref($sql, minimum_security => 1.0, callback {
sprintf("%-11s %.1f\n", @{$_}{'name', 'security'}); # Hash slice
});
# Returns a structure something like this:
#
# [ 'Kisogo 1.0',
# 'New Caldari 1.0',
# 'Amarr 1.0',
# 'Bourynes 1.0',
# 'Ryddinjorn 1.0',
# 'Luminaire 1.0',
# 'Duripant 1.0',
# 'Yulai 1.0' ]
=back
=head2 getrows I<(database handles)>
$results = $dbh->getrows($statement_string, @bind_values);
@results = $dbh->getrows($statement_string, @bind_values);
$results = $dbh->getrows($statement_string, \%attr, @bind_values);
@results = $dbh->getrows($statement_string, \%attr, @bind_values);
$results = $dbh->getrows($statement_handle, @bind_values);
@results = $dbh->getrows$statement_handle, @bind_values);
Alias for C<getrows_hashref>.
If array references are preferred, have the symbol table glob point alias the
C<getrows_arrayref> method.
The C<getrows> method accepts optional callbacks for further processing
of the results by the caller.
=head2 getrow_arrayref I<(database handles)>
$result = $dbh->getrow_arrayref($statement_string, @bind_values);
$result = $dbh->getrow_arrayref($statement_string, \%attr, @bind_values);
$result = $dbh->getrow_arrayref($statement_handle, @bind_values);
Prepares (if necessary) and executes a single statement with the specified data
bindings and fetches the first row as an array reference.
The C<getrow_arrayref> method accepts optional callbacks for further processing
of the result by the caller.
=head2 getrow_hashref I<(database handles)>
$result = $dbh->getrow_hashref($statement_string, @bind_values);
$result = $dbh->getrow_hashref($statement_string, \%attr, @bind_values);
$result = $dbh->getrow_hashref($statement_handle, @bind_values);
Prepares (if necessary) and executes a single statement with the specified data
bindings and fetches the first row as a hash reference.
The C<getrow_hashref> method accepts optional callbacks for further processing
of the result by the caller.
=head2 getrow I<(database handles)>
$result = $dbh->getrow($statement_string, @bind_values);
$result = $dbh->getrow($statement_string, \%attr, @bind_values);
$result = $dbh->getrow($statement_handle, @bind_values);
Alias for C<getrow_hashref>.
If array references are preferred, have the symbol table glob point alias the
C<getrows_arrayref> method.
The C<getrow> method accepts optional callbacks for further processing
of the result by the caller.
=head1 STATEMENT HANDLE METHODS
=head2 bind_param
$sth->bind_param($param_num, $bind_value)
$sth->bind_param($param_num, $bind_value, \%attr)
$sth->bind_param($param_num, $bind_value, $bind_type)
$sth->bind_param($param_name, $bind_value)
$sth->bind_param($param_name, $bind_value, \%attr)
$sth->bind_param($param_name, $bind_value, $bind_type)
The C<bind_param> method associates (binds) a value to a placeholder embedded in the
prepared statement. The implementation provided by this module allows the use of
parameter names, if appropriate, in addition to parameter positions.
I<Refer to L<http://search.cpan.org/dist/DBI/DBI.pm#bind_param> for a more detailed
explanation of how to use this method>.
=head2 execute
$rv = $sth->execute() or die $DBI::errstr;
$rv = $sth->execute(@bind_values) or die $DBI::errstr;
Perform whatever processing is necessary to execute the prepared statement. An
C<undef> is returned if an error occurs. A successful call returns true regardless
of the number of rows affected, even if it's zero.
Refer to L<http://search.cpan.org/dist/DBI/DBI.pm#execute> for a more detailed
description of this method.
B<Examples>
=over
=item Use prepare, execute and getrow_hashref with a callback to modify my data:
use strict;
use warnings;
use DBIx::FlexibleBinding -subs => [ 'TestDB' ];
use Data::Dumper;
use Test::More;
$Data::Dumper::Terse = 1;
$Data::Dumper::Indent = 1;
TestDB 'dbi:mysql:test', '', '', { RaiseError => 1 };
my $sth = TestDB->prepare(<< '//');
SELECT solarSystemID AS id
, solarSystemName AS name
, security
FROM mapsolarsystems
WHERE solarSystemName RLIKE "^U[^0-9\-]+$"
ORDER BY id, name, security DESC
LIMIT 5
//
lib/DBIx/FlexibleBinding.pm view on Meta::CPAN
return $row;
}
);
while ( my $row = $sth->getrow_hashref(@callback_list) ) {
push @rows, $row;
}
my $expected_result = [
{
'name' => 'Uplingur',
'filled_with' => 'Yarrbears',
'id' => '30000037',
'security' => '0.4'
},
{
'security' => '0.4',
'id' => '30000040',
'name' => 'Uzistoon',
'filled_with' => 'Yarrbears'
},
{
'name' => 'Usroh',
'filled_with' => 'Carebears',
'id' => '30000068',
'security' => '0.6'
},
{
'filled_with' => 'Yarrbears',
'name' => 'Uhtafal',
'id' => '30000101',
'security' => '0.5'
},
{
'security' => '0.3',
'id' => '30000114',
'name' => 'Ubtes',
'filled_with' => 'Yarrbears'
}
];
is_deeply( \@rows, $expected_result, 'iterate' )
and diag( Dumper(\@rows) );
done_testing();
=back
=head2 iterate
$iterator = $sth->iterate() or die $DBI::errstr;
$iterator = $sth->iterate(@bind_values) or die $DBI::errstr;
Perform whatever processing is necessary to execute the prepared statement. An
C<undef> is returned if an error occurs. A successful call returns an iterator
which can be used to traverse the result set.
B<Examples>
=over
=item 1. Using an iterator and callbacks to process the result set:
use strict;
use warnings;
use DBIx::FlexibleBinding -subs => [ 'TestDB' ];
use Data::Dumper;
use Test::More;
$Data::Dumper::Terse = 1;
$Data::Dumper::Indent = 1;
my @drivers = grep { /^SQLite$/ } DBI->available_drivers();
SKIP: {
skip("iterate tests (No DBD::SQLite installed)", 1) unless @drivers;
TestDB "dbi:SQLite:test.db", '', '', { RaiseError => 1 };
my $sth = TestDB->prepare(<< '//');
SELECT solarSystemID AS id
, solarSystemName AS name
, security
FROM mapsolarsystems
WHERE solarSystemName REGEXP "^U[^0-9\-]+$"
ORDER BY id, name, security DESC
LIMIT 5
//
# Iterate over the result set
# ---------------------------
# We also queue up a sneaky callback to modify each row of data as it
# is fetched from the result set.
my $it = $sth->iterate( callback {
my ($row) = @_;
$row->{filled_with} = ( $row->{security} >= 0.5 )
? 'Carebears' : 'Yarrbears';
$row->{security} = sprintf('%.1f', $row->{security});
return $row;
} );
my @rows;
while ( my $row = $it->() ) {
push @rows, $row;
}
# Done, now check the results ...
my $expected_result = [
{
'name' => 'Uplingur',
'filled_with' => 'Yarrbears',
'id' => '30000037',
'security' => '0.4'
},
{
'security' => '0.4',
'id' => '30000040',
'name' => 'Uzistoon',
'filled_with' => 'Yarrbears'
},
{
'name' => 'Usroh',
'filled_with' => 'Carebears',
'id' => '30000068',
'security' => '0.6'
},
{
'filled_with' => 'Yarrbears',
'name' => 'Uhtafal',
'id' => '30000101',
'security' => '0.5'
},
{
'security' => '0.3',
'id' => '30000114',
'name' => 'Ubtes',
'filled_with' => 'Yarrbears'
}
];
is_deeply( \@rows, $expected_result, 'iterate' )
and diag( Dumper(\@rows) );
}
done_testing();
In this example, we're traversing the result set using an iterator. As we iterate
through the result set, a callback is applied to each row and we're left with
an array of transformed rows.
=item 2. Using an iterator's C<for_each> method and callbacks to process the
result set:
use strict;
use warnings;
use DBIx::FlexibleBinding -subs => [ 'TestDB' ];
use Data::Dumper;
use Test::More;
$Data::Dumper::Terse = 1;
$Data::Dumper::Indent = 1;
my @drivers = grep { /^SQLite$/ } DBI->available_drivers();
SKIP: {
skip("iterate tests (No DBD::SQLite installed)", 1) unless @drivers;
TestDB "dbi:SQLite:test.db", '', '', { RaiseError => 1 };
my $sth = TestDB->prepare(<< '//');
SELECT solarSystemID AS id
, solarSystemName AS name
, security
FROM mapsolarsystems
WHERE solarSystemName REGEXP "^U[^0-9\-]+$"
ORDER BY id, name, security DESC
LIMIT 5
//
# Iterate over the result set
# ---------------------------
# This time around we call the iterator's "for_each" method to process
# the data. Bonus: we haven't had to store the iterator anywhere or
# pre-declare an empty array to accommodate our rows.
my @rows = $sth->iterate->for_each( callback {
my ($row) = @_;
$row->{filled_with} = ( $row->{security} >= 0.5 )
? 'Carebears' : 'Yarrbears';
$row->{security} = sprintf('%.1f', $row->{security});
return $row;
} );
# Done, now check the results ...
my $expected_result = [
{
'name' => 'Uplingur',
'filled_with' => 'Yarrbears',
'id' => '30000037',
'security' => '0.4'
},
{
'security' => '0.4',
'id' => '30000040',
'name' => 'Uzistoon',
'filled_with' => 'Yarrbears'
},
{
'name' => 'Usroh',
'filled_with' => 'Carebears',
'id' => '30000068',
'security' => '0.6'
},
{
'filled_with' => 'Yarrbears',
'name' => 'Uhtafal',
'id' => '30000101',
'security' => '0.5'
},
{
'security' => '0.3',
'id' => '30000114',
'name' => 'Ubtes',
'filled_with' => 'Yarrbears'
}
];
is_deeply( \@rows, $expected_result, 'iterate' )
and diag( Dumper(\@rows) );
}
done_testing();
Like the previous example, we're traversing the result set using an iterator but
this time around we have done away with C<$it> in favour of calling the iterator's
own C<for_each> method. The callback we were using to process each row of the
result set has now been passed into the C<for_each> method also eliminating a
C<while> loop and an empty declaration for C<@rows>.
=back
=head2 getrows_arrayref I<(database handles)>
$results = $sth->getrows_arrayref();
@results = $sth->getrows_arrayref();
Fetches the entire result set as an array of array references.
The C<getrows_arrayref> method accepts optional callbacks for further processing
of the results by the caller.
=head2 getrows_hashref I<(database handles)>
$results = $sth->getrows_hashref();
@results = $sth->getrows_hashref();
Fetches the entire result set as an array of hash references.
The C<getrows_hashref> method accepts optional callbacks for further processing
of the results by the caller.
=head2 getrows I<(database handles)>
$results = $sth->getrows();
@results = $sth->getrows();
Alias for C<getrows_hashref>.
If array references are preferred, have the symbol table glob point alias the
C<getrows_arrayref> method.
The C<getrows> method accepts optional callbacks for further processing
of the results by the caller.
=head2 getrow_arrayref I<(database handles)>
$result = $sth->getrow_arrayref();
Fetches the next row as an array reference. Returns C<undef> if there are no more
rows available.
The C<getrow_arrayref> method accepts optional callbacks for further processing
of the result by the caller.
=head2 getrow_hashref I<(database handles)>
$result = $sth->getrow_hashref();
Fetches the next row as a hash reference. Returns C<undef> if there are no more
rows available.
The C<getrow_hashref> method accepts optional callbacks for further processing
of the result by the caller.
=head2 getrow I<(database handles)>
$result = $sth->getrow();
Alias for C<getrow_hashref>.
If array references are preferred, have the symbol table glob point alias the
C<getrows_arrayref> method.
The C<getrow> method accepts optional callbacks for further processing
of the result by the caller.
=head1 EXPORTS
The following symbols are exported by default:
=head2 callback
To enable the namespace using this module to take advantage of the callbacks,
which are one of its main features, without the unnecessary burden of also
including the module that provides the feature I<(see L<Params::Callbacks> for
more detailed information)>.
=head1 SEE ALSO
=over 2
=item * L<DBI>
=item * L<Params::Callbacks>
=back
=head1 REPOSITORY
=over 2
=item * L<https://github.com/cpanic/DBIx-FlexibleBinding>
=item * L<http://search.cpan.org/dist/DBIx-FlexibleBinding/lib/DBIx/FlexibleBinding.pm>
=back
=head1 BUGS
Please report any bugs or feature requests to C<bug-dbix-anybinding at rt.cpan.org>, or through
the web interface at L<http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBIx-FlexibleBinding>. I will be notified, and then you'll
automatically be notified of progress on your bug as I make changes.
=head1 SUPPORT
You can find documentation for this module with the perldoc command.
perldoc DBIx::FlexibleBinding
You can also look for information at:
=over 4
=item * RT: CPAN's request tracker (report bugs here)
L<http://rt.cpan.org/NoAuth/Bugs.html?Dist=DBIx-FlexibleBinding>
=item * AnnoCPAN: Annotated CPAN documentation
L<http://annocpan.org/dist/DBIx-FlexibleBinding>
=item * CPAN Ratings
L<http://cpanratings.perl.org/d/DBIx-FlexibleBinding>
=item * Search CPAN
L<http://search.cpan.org/dist/DBIx-FlexibleBinding/>
=back
=head1 ACKNOWLEDGEMENTS
( run in 1.206 second using v1.01-cache-2.11-cpan-5c0b1e786e0 )