NAME

DBIx::FlexibleBinding - Greater flexibility on statement placeholder choice and data binding

VERSION

version 1.8.3

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.                                       #
############################################################################

use DBIx::FlexibleBinding -alias => 'DFB';
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, this time with the DBI subclass package alias ...
#
my $dbh = DFB->connect(DSN, '', '', { RaiseError => 1 });

# Cut out the middle men ...
#
my @system_names = $dbh->getrows_hashref(SQL,
                                            is_regional => 1,
                                            minimum_security => 1.0,
                                            callback { $_->{name} });

#############################################################################
# SCENARIO 3                                                                #
# The subclass import method provides a versatile mechanism for simplifying #
# matters further.                                                          #
#############################################################################

use DBIx::FlexibleBinding -subs => [ 'MyDB' ];
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
//

# MyDB will represent our datasource; initialise it ...
#
MyDB DSN, '', '', { RaiseError => 1 };

# Cut out the middle men and some of the line-noise, too ...
#
my @system_names = MyDB(SQL,
                        is_regional => 1,
                        minimum_security => 1.0,
                        callback { $_->{name} });

DESCRIPTION

This module subclasses the DBI to provide improvements and greater flexibility in the following areas:

Parameter placeholders and data binding
Data retrieval and processing
Accessing and interacting with datasources

Parameter placeholders and data binding

This module provides support for a wider range of parameter placeholder and data-binding schemes. As well as continued support for the simple positional placeholders (?), additional support is provided for numeric placeholders (:N and ?N), and named placeholders (:NAME and @NAME).

As for the process of binding data values to parameters: that is, by default, now completely automated, removing a significant part of the workload from the prepare-bind-execute cycle. It is, however, possible to swtch off automatic data-binding globally and on a statement-by-statement basis.

The following familiar operations have been modified to accommodate all of these changes, though developers continue to use them as they always have done:

$DATABASE_HANDLE->prepare($STATEMENT, \%ATTR);
$DATABASE_HANDLE->do($STATEMENT, \%ATTR, @DATA);
$STATEMENT_HANDLE->bind_param($NAME_OR_POSITION, $VALUE, \%ATTR);
$STATEMENT_HANDLE->execute(@DATA);

Data retrieval and processing

Four new methods, each available for database and statement handles, have been implemented:

getrow_arrayref
getrow_hashref
getrows_arrayref
getrows_hashref

These methods complement DBI's existing fetch methods, providing new ways to retrieve and process data.

Accessing and interacting with datasources

The module's -subs import option may be used to create subroutines, during the compile phase, and export them to the caller's namespace for use later as representations of database and statement handles.

Use for connecting to datasources

use DBIx::FlexibleBinding -subs => [ 'MyDB' ];

# Pass in any set of well-formed DBI->connect(...) arguments to associate
# your name with a live database connection ...
#
MyDB( 'dbi:mysql:test;host=127.0.0.1', '', '', { RaiseError => 1 } );

# Or, simply pass an existing database handle as the only argument ...
#
MyDB($dbh);

Use them to represent database handles

use DBIx::FlexibleBinding -subs => [ 'MyDB' ];
use constant SQL => << '//';
SELECT *
  FROM mapsolarsystems
 WHERE regional  = :is_regional
   AND security >= :minimum_security
//

MyDB( 'dbi:mysql:test;host=127.0.0.1', '', '', { RaiseError => 1 } );

# If your name is already associated with a database handle then just call
# it with no parameters to use it as such ...
#
my $sth = MyDB->prepare(SQL);

Use them to represent statement handles

use DBIx::FlexibleBinding -subs => [ 'MyDB', 'solar_systems' ];
use constant SQL => << '//';
SELECT *
  FROM mapsolarsystems
 WHERE regional  = :is_regional
   AND security >= :minimum_security
//

MyDB( 'dbi:mysql:test;host=127.0.0.1', '', '', { RaiseError => 1 } );

my $sth = MyDB->prepare(SQL);

# Simply call the statement handle proxy, passing a statement handle in as
# the only argument ...
#
solar_systems($sth);

Use to interact with the represented database and statement handles

use DBIx::FlexibleBinding -subs => [ 'MyDB', 'solar_systems' ];
use constant SQL => << '//';
SELECT *
  FROM mapsolarsystems
 WHERE regional  = :is_regional
   AND security >= :minimum_security
//

MyDB( 'dbi:mysql:test;host=127.0.0.1', '', '', { RaiseError => 1 } );

# Use the database handle proxy to prepare, bind and execute statements, then
# retrieve the results ...
#
# Use the database handle proxy to prepare, bind and execute statements, then
# retrieve the results ...
#
my $array_of_hashrefs = MyDB(SQL,
                             is_regional => 1,
                             minimum_security => 1.0);

# In list context, results come back as lists ...
#
my @array_of_hashrefs = MyDB(SQL,
                             is_regional => 1,
                             minimum_security => 1.0);

# Using -subs also relaxes strict 'subs' in the caller's scope, so pretty-up
# void context calls by losing the parentheses, if you wish to use callbacks
# to process the results ...
#
MyDB SQL, is_regional => 1, minimum_security => 1.0, callback {
    printf "%-16s %.1f\n", $_->{solarSystemName}, $_->{security};
};

# You can use proxies to represent statements, too. Simply pass in a statement
# handle as the only argument ...
#
my $sth = MyDB->prepare(SQL);
solar_systems($sth);    # Using "solar_systems" as statement proxy.

# Now, when called with other types of arguments, those argument values are
# bound and the statement is executed ...
#
my $array_of_hashrefs = solar_systems(is_regional => 1,
                                      minimum_security => 1.0);

# In list context, results come back as lists ...
#
my @array_of_hashrefs = solar_systems(is_regional => 1,
                                      minimum_security => 1.0);

# Statements requiring no parameters cannot be used in this manner because
# making a call to a statement proxy with an arity of zero results in the
# statement handle being returned. In this situation, use something like
# undef as an argument (it will be ignored in this particular instance) ...
#
my $rv = statement_proxy(undef);
#
# Meh, you can't win 'em all!

PACKAGE GLOBALS

$DBIx::FlexibleBinding::AUTO_BINDING_ENABLED

A boolean setting used to determine whether or not automatic binding is enabled or disabled globally.

The default setting is "1" (enabled).

$DBIx::FlexibleBinding::GETROWS_USING

The subroutines created with the -subs import option may be used to retrieve result sets. By default, any such subroutines delegate that particular task to a method called "getrows_hashref", which is provided by this module for both database and statement handles alike.

It is also used to influence the type of structure the getrows methods fetch by default (hashrefs or arrayrefs).

For reasons of efficiency the developer may prefer array references over hash references, in which case they only need assign the value "getrows_arrayref" to this global.

$DBIx::FlexibleBinding::GETROW_USING

Used to control influence the type of structure the getrow methods fetch by default (hashrefs or arrayrefs).

For reasons of efficiency the developer may prefer array references over hash references, in which case they only need assign the value "getrows_arrayref" to this global.

IMPORT TAGS AND OPTIONS

-alias

This option may be used by the caller to select an alias to use for this package's unwieldly namespace.

use DBIx::FlexibleBinding -alias => 'DBIF';

my $dbh = DBIF->connect('dbi:SQLite:test.db', '', '');

-subs

This option may be used to create subroutines, during the compile phase, in the caller's namespace to be used as representations of database and statement handles.

use DBIx::FlexibleBinding -subs => [ 'MyDB' ];

# Initialise by passing in a valid set of DBI->connect(...) arguments.
# The database handle will be the return value.
#
MyDB 'dbi:mysql:test;host=127.0.0.1', '', '', { RaiseError => 1 };

# 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";
};

Use of this option automatically relaxes strict 'subs' for the remainder of scope containing the use directive. That is unless use strict 'subs' or use strict appears afterwards.

CLASS METHODS

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 http://search.cpan.org/dist/DBI/DBI.pm#connect for a more detailed description of this method.

DATABASE HANDLE METHODS

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 DBI tradition of returning 0E0 on successful execution and undef on failure.

The do method accepts optional callbacks for further processing of the result.

The do implementation provided by this module allows for some minor deviations in usage over the standard DBI implementation. In spite of this, the new method may be used just like the original.

Refer to http://search.cpan.org/dist/DBI/DBI.pm#do for a more detailed description of this method.

Examples

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.

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.

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 http://search.cpan.org/dist/DBI/DBI.pm#prepare for a more detailed description of this method.

Examples

1. Prepare a statement using positional placeholders:

$sql = << '//';
UPDATE employees
   SET salary = ?
 WHERE employee_id = ?
//

$sth = $dbh->prepare($sql);

2. Prepare a statement using named placeholders:

(Yes, even for those MySQL connections!)

$sql = << '//';
UPDATE employees
   SET salary = :salary
 WHERE employee_id = :employee_id
//

$sth = $dbh->prepare($sql);

getrows_arrayref (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 getrows_arrayref method accepts optional callbacks for further processing of the results by the caller.

Examples

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' ] ]

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

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' ]

getrows_hashref (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 getrows_hashref method accepts optional callbacks for further processing of the results by the caller.

Examples

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' } ]

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

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' ]

getrows (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);

Prepares (if necessary) and executes a single statement with the specified data bindings and fetches the result set.

Default behaviour is for all rows to be fetched as hash references. If array references are preferred, change the $DBIx::FlexibleBinding::GETROWS_USING package global from "getrows_hashref" to "getrows_arrayref".

The getrows method accepts optional callbacks for further processing of the results by the caller.

getrow_arrayref (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 getrow_arrayref method accepts optional callbacks for further processing of the result by the caller.

getrow_hashref (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 getrow_hashref method accepts optional callbacks for further processing of the result by the caller.

getrow (database handles)

$result = $dbh->getrow($statement_string, @bind_values);
$result = $dbh->getrow($statement_string, \%attr, @bind_values);
$result = $dbh->getrow($statement_handle, @bind_values);

Prepares (if necessary) and executes a single statement with the specified data bindings and fetches the first row.

Default behaviour is for the row to be fetched as a hash reference. If array references are preferred, change the $DBIx::FlexibleBinding::GETROW_USING package global from "getrow_hashref" to "getrow_arrayref".

The getrow method accepts optional callbacks for further processing of the result by the caller.

STATEMENT HANDLE METHODS

auto_bind

$sth->auto_bind($boolean);
$state = $sth->auto_bind();

Use this method to enable, disable or inspect the current state of automatic binding for a particular statement handle.

bind

$sth->bind(@bind_values);       # For positional and numeric placeholders
$sth->bind(\@bind_values);      # For positional and numeric placeholders
$sth->bind(%bind_values);       # For numeric and named placeholders
$sth->bind(\%bind_values);      # For numeric and named placeholders
$sth->bind([%bind_values]);     # For numeric and named placeholders

The bind method associates (binds) the values supplied in the parameter list with the placeholders embedded in the prepared statement.

With automatic binding enabled (and it is by default), any operation that results in a subsequent call to the execute method will almost certainly complete any parameter binding automatically using this method.

With automatic binding disabled, this method will not be called at all and the execute method will almost dutifully convey bind values as they are presented up the DBI inheritance chain to the handler's execute method. Almost dutifully because a lone array reference will be de-referenced and passed up as a list. The general assumption here is that you won't present any bind values because the hard work of doing the bind_param calls has already been done, or a manual call to the bind method has already taken place.

In any case, it's pretty hard to screw-up when relying on this method to bind your data values. Provided that what you present can be interpreted as a list of values for positional placeholders, a list of key-value pairs for named placeholders, or either for numeric placeholders, then you'll be absolutely fine.

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 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.

Refer to http://search.cpan.org/dist/DBI/DBI.pm#bind_param for a more detailed explanation of how to use this method.

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 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 http://search.cpan.org/dist/DBI/DBI.pm#execute for a more detailed description of this method.

Examples

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
//

$sth->execute() or die $DBI::errstr;

my @rows;
my @callback_list = (
    callback {
        my ($row) = @_;
        $row->{filled_with} = ( $row->{security} >= 0.5 )
            ? 'Carebears' : 'Yarrbears';
        $row->{security} = sprintf('%.1f', $row->{security});
        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();

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 undef is returned if an error occurs. A successful call returns an iterator which can be used to traverse the result set.

Examples

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.

2. Using an iterator's 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 $it in favour of calling the iterator's own for_each method. The callback we were using to process each row of the result set has now been passed into the for_each method also eliminating a while loop and an empty declaration for @rows.

getrows_arrayref (database handles)

$results = $sth->getrows_arrayref();
@results = $sth->getrows_arrayref();

Fetches the entire result set as an array of array references.

The getrows_arrayref method accepts optional callbacks for further processing of the results by the caller.

getrows_hashref (database handles)

$results = $sth->getrows_hashref();
@results = $sth->getrows_hashref();

Fetches the entire result set as an array of hash references.

The getrows_hashref method accepts optional callbacks for further processing of the results by the caller.

getrows (database handles)

$results = $sth->getrows();
@results = $sth->getrows();

Fetches the entire result set.

The getrows method accepts optional callbacks for further processing of the results by the caller.

getrow_arrayref (database handles)

$result = $sth->getrow_arrayref();

Fetches the next row as an array reference. Returns undef if there are no more rows available.

The getrow_arrayref method accepts optional callbacks for further processing of the result by the caller.

getrow_hashref (database handles)

$result = $sth->getrow_hashref();

Fetches the next row as a hash reference. Returns undef if there are no more rows available.

The getrow_hashref method accepts optional callbacks for further processing of the result by the caller.

getrow (database handles)

$result = $sth->getrow();

Fetches the next row. Returns undef if there are no more rows available.

The getrow method accepts optional callbacks for further processing of the result by the caller.

EXPORTS

The following symbols are exported by default:

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 (see Params::Callbacks for more detailed information).

REPOSITORY

BUGS

Please report any bugs or feature requests to bug-dbix-anybinding at rt.cpan.org, or through the web interface at 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.

SUPPORT

You can find documentation for this module with the perldoc command.

perldoc DBIx::FlexibleBinding

You can also look for information at:

RT: CPAN's request tracker (report bugs here)

http://rt.cpan.org/NoAuth/Bugs.html?Dist=DBIx-FlexibleBinding
AnnoCPAN: Annotated CPAN documentation

http://annocpan.org/dist/DBIx-FlexibleBinding
CPAN Ratings

http://cpanratings.perl.org/d/DBIx-FlexibleBinding
Search CPAN

http://search.cpan.org/dist/DBIx-FlexibleBinding/

ACKNOWLEDGEMENTS

Test data set extracted from Fuzzwork's MySQL conversion of CCP's EVE Online Static Data Export:

Fuzzwork https://www.fuzzwork.co.uk/
EVE Online http://www.eveonline.com/

Eternal gratitude to GitHub contributors:

Syohei Yoshida http://search.cpan.org/~syohex/

AUTHOR

Iain Campbell <cpanic@cpan.org>

COPYRIGHT AND LICENSE

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.

To install DBIx::FlexibleBinding, copy and paste the appropriate command in to your terminal.

cpanm

cpanm DBIx::FlexibleBinding

CPAN shell

perl -MCPAN -e shell
install DBIx::FlexibleBinding

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

VERSION

SYNOPSIS

DESCRIPTION

Parameter placeholders and data binding

Data retrieval and processing

Accessing and interacting with datasources

PACKAGE GLOBALS

$DBIx::FlexibleBinding::AUTO_BINDING_ENABLED

$DBIx::FlexibleBinding::GETROWS_USING

$DBIx::FlexibleBinding::GETROW_USING

IMPORT TAGS AND OPTIONS

-alias

-subs

CLASS METHODS

connect

DATABASE HANDLE METHODS

do

prepare

getrows_arrayref (database handles)

getrows_hashref (database handles)

getrows (database handles)

getrow_arrayref (database handles)

getrow_hashref (database handles)

getrow (database handles)

STATEMENT HANDLE METHODS

auto_bind

bind

bind_param

execute

iterate

getrows_arrayref (database handles)

getrows_hashref (database handles)

getrows (database handles)

getrow_arrayref (database handles)

getrow_hashref (database handles)

getrow (database handles)

EXPORTS

callback

SEE ALSO

REPOSITORY

BUGS

SUPPORT

ACKNOWLEDGEMENTS

AUTHOR

COPYRIGHT AND LICENSE

Module Install Instructions