DBIx-FlexibleBinding
view release on metacpan or search on metacpan
# 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";
};
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);
Alias for "getrows_hashref".
If array references are preferred, have the symbol table glob point
alias the "getrows_arrayref" method.
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);
Alias for "getrow_hashref".
If array references are preferred, have the symbol table glob point
alias the "getrows_arrayref" method.
The "getrow" method accepts optional callbacks for further processing of
the result by the caller.
STATEMENT HANDLE METHODS
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;
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();
Alias for "getrows_hashref".
If array references are preferred, have the symbol table glob point
alias the "getrows_arrayref" method.
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();
Alias for "getrow_hashref".
If array references are preferred, have the symbol table glob point
alias the "getrows_arrayref" method.
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)*.
SEE ALSO
* DBI
* Params::Callbacks
REPOSITORY
* <https://github.com/cpanic/DBIx-FlexibleBinding>
* <http://search.cpan.org/dist/DBIx-FlexibleBinding/lib/DBIx/FlexibleBin
ding.pm>
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
Many, many thanks to the CPANTesters network.
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>
( run in 1.461 second using v1.01-cache-2.11-cpan-140bd7fdf52 )