/ 
DBIx-FlexibleBinding-0.001002
/ DBIx::FlexibleBinding

NAME

DBIx::FlexibleBinding - flexible parameter binding and record fetching

SYNOPSIS

# Introducing the module...
# 
use DBIx::FlexibleBinding;
my $dbh = DBIx::FlexibleBinding->connect($dsn, $user, $pass, \%attributes);


# Or, alteratively...
# 
use DBI;
my $dbh = DBI->connect($dsn, $user, $pass, { %attributes, RootClass => 'DBIx::FlexibleBinding' }); 


# Using the "do" method...
# 
$dbh->do('INSERT INTO cakes (type, colour, flavour) VALUES (?, ?, ?)', undef, 
    'sponge', 'yellow', 'yummy');
$dbh->do('INSERT INTO cakes (type, colour, flavour) VALUES (?, ?, ?)', undef, 
    [ 'sponge', 'yellow', 'yummy' ]);
$dbh->do('INSERT INTO cakes (type, colour, flavour) VALUES (:1, :2, :3)', undef, 
    'sponge', 'yellow', 'yummy');
$dbh->do('INSERT INTO cakes (type, colour, flavour) VALUES (:1, :2, :3)', undef, 
    [ 'sponge', 'yellow', 'yummy' ]);
$dbh->do('INSERT INTO cakes (type, colour, flavour) VALUES (:type, :colour, :flavour)', undef, 
    type => 'sponge', colour => 'yellow', flavour => 'yummy');
$dbh->do('INSERT INTO cakes (type, colour, flavour) VALUES (:type, :colour, :flavour)', undef, 
    [ type => 'sponge', colour => 'yellow', flavour => 'yummy' ]);
$dbh->do('INSERT INTO cakes (type, colour, flavour) VALUES (:type, :colour, :flavour)', undef, 
    { type => 'sponge', colour => 'yellow', flavour => 'yummy' });


# Prepare using :NAME scheme...
# 
my $sth = $dbh->prepare(<< 'EOF');
SELECT * 
  FROM cakes 
 WHERE type    = :type    
   AND colour  = :colour  
   AND flavour = :flavour
EOF


# Execute (any of the are valid for this named scheme)...
# 
my $row_count = $sth->execute(type => 'sponge', colour => 'yellow', flavour => 'yummy');
my $row_count = $sth->execute([ type => 'sponge', colour => 'yellow', flavour => 'yummy' ]);
my $row_count = $sth->execute({ type => 'sponge', colour => 'yellow', flavour => 'yummy' });


# Prepare using @NAME scheme...
# 
my $sth = $dbh->prepare(<< 'EOF');
SELECT * 
  FROM cakes 
 WHERE type    = @type    
   AND colour  = @colour  
   AND flavour = @flavour
EOF


# Execute (any of the are valid for this named scheme)...
# 
my $row_count = $sth->execute('@type' => 'sponge', '@colour' => 'yellow', '@flavour' => 'yummy');
my $row_count = $sth->execute([ '@type' => 'sponge', '@colour' => 'yellow', '@flavour' => 'yummy' ]);
my $row_count = $sth->execute({ '@type' => 'sponge', '@colour' => 'yellow', '@flavour' => 'yummy' });


# Prepare using :N scheme...
# 
my $sth = $dbh->prepare(<< 'EOF');
SELECT * 
  FROM cakes 
 WHERE type    = :1    
   AND colour  = :2  
   AND flavour = :3
EOF


# Execute (any of the are valid for this numeric scheme)...
# 
my $row_count = $sth->execute('sponge', 'yellow', 'yummy');
my $row_count = $sth->execute([ 'sponge', 'yellow', 'yummy' ]);


# Prepare using ?N scheme...
# 
my $sth = $dbh->prepare(<< 'EOF');
SELECT * 
  FROM cakes 
 WHERE type    = ?1    
   AND colour  = ?2  
   AND flavour = ?3
EOF


# Execute (any of the are valid for this numeric scheme)...
# 
my $row_count = $sth->execute('sponge', 'yellow', 'yummy');
my $row_count = $sth->execute([ 'sponge', 'yellow', 'yummy' ]);


# Prepare using ? scheme...
# 
my $sth = $dbh->prepare(<< 'EOF');
SELECT * 
  FROM cakes 
 WHERE type    = ?    
   AND colour  = ?  
   AND flavour = ?
EOF


# Execute (any of the are valid for this positional scheme)...
# 
my $row_count = $sth->execute('sponge', 'yellow', 'yummy');
my $row_count = $sth->execute([ 'sponge', 'yellow', 'yummy' ]);


# Data binding is automatic by default.
# 
# Those with a penchant for masochism may switch automatic binding 
# off completely using the C<auto_bind> method, or by changing the
# value of DBIx::FlexibleBinding::DEFAULT_AUTO_BIND to 0.
# 
my $sth = $dbh->prepare(<< 'EOF')->auto_bind(0);
SELECT * 
  FROM cakes 
 WHERE type    = :type    
   AND colour  = :colour  
   AND flavour = :flavour
EOF

$sth->bind_param('type', 'sponge');
$sth->bind_param('colour', 'yellow');
$sth->bind_param('flavour', 'yummy');
$sth->execute();


# Manual binding with numeric or positional parameters...
#
my $sth = $dbh->prepare(<< 'EOF')->auto_bind(0);
SELECT * 
  FROM cakes 
 WHERE type    = :1    
   AND colour  = :2  
   AND flavour = :3
EOF

$sth->bind_param(1, 'sponge');
$sth->bind_param(2, 'yellow');
$sth->bind_param(3, 'yummy');
$sth->execute();


# Fetching and processing a single row (arrayref)
#
my $sth = $dbh->prepare('SELECT COUNT(*) AS count FROM cakes');
$sth->execute();

my $arrayref = $sth->processrow_arrayref()          # extra, unnecessary state
my $count = $arrayref->[0];                         # the value we actually wanted

# Or ...
#
my $count = $sth->processrow_arrayref(callback {    # single piece of state and the value we wanted
    return $_->[0];                                 # use $_  and $_[0] to reference the row in a callback
});                                                 # callback not called for emty result set


# Fetching and processing a single row (hashref)
#
my $sth = $dbh->prepare('SELECT COUNT(*) AS count FROM cakes');
$sth->execute();

my $hashref = $sth->processrow_hashref()            # extra, unnecessary state
my $count = $hashref->{count};                      # the value we actually wanted

# Or ...
#
my $count = $sth->processrow_hashref(callback {     # single piece of state and the value we wanted
    return $_[0]{count};                            # use $_  and $_[0] to reference the row in a callback
});                                                 # callback not called for emty result set


# Another way to fetch and process a single row (arrayref)
#
my $arrayref = $dbh->processrow_arrayref('SELECT COUNT(*) AS count FROM cakes');
my $count = $arrayref->[0];

# Or ...
#
my $count = $dbh->processrow_arrayref('SELECT COUNT(*) AS count FROM cakes', callback {
    return $_->[0];
});


# Another way to fetch and process a single row (hashref)
#
my $hashref = $dbh->processrow_hashref('SELECT COUNT(*) AS count FROM cakes');
my $count = $hashref->{count};

# Or ...
#
my $count = $dbh->processrow_hashref('SELECT COUNT(*) AS count FROM cakes', callback {
    return $_[0]{count};
});


# Fetching and processing multiple result sets...
#
my $array_of_array_refs = $dbh->processall_arrayref($statement, \%opt_attr, @opt_bindings, @opt_callbacks);
my @array_of_array_refs = $dbh->processall_arrayref($statement, \%opt_attr, @opt_bindings, @opt_callbacks);
my $array_of_hash_refs = $dbh->processall_hashref($statement, \%opt_attr, @opt_bindings, @opt_callbacks);
my @array_of_hash_refs = $dbh->processall_hashref($statement, \%opt_attr, @opt_bindings, @opt_callbacks);
my $array_of_array_refs = $sth->processall_arrayref(@opt_callbacks);
my @array_of_array_refs = $sth->processall_arrayref(@opt_callbacks);
my $array_of_hash_refs = $sth->processall_hashref(@opt_callbacks);
my @array_of_hash_refs = $sth->processall_hashref(@opt_callbacks);

DESCRIPTION

This module subclasses the DBI to provide the developer with greater flexibility in their choice of parameter placeholder schemes. In addition to the standard positional ? placeholders, this module supports other popular schemes:

  • :N (numeric, e.g. :1)

  • ?N (numeric, e.g. ?1)

  • :NAME (named, e.g. :foo)

  • @NAME (named, e.g. @foo)

The module places little if any addtional cognitive burden upon developers who continue to use prepare, do, execute methods as they would normally.

The module's standard behaviour is to render unnecessary the manual binding of parameters because, for any scheme other than positional ? placeholders, that binding is done automatically. And it isn't usually necessary to manually bind parameters when using postional placeholders.

When presenting parameter bindings to the do and execute methods, just remember to lay them out sensibly:

Positional or numeric schemes

Parameter bindings may be presented as a simple list of parameters, as a single reference to a list, or as a single anonymous array reference.

Name-based schemes

Parameter bindings may be presented as a simple list of key-value pairs, as a single reference to a list of key-value pairs, as a single anonymous array reference containing key-value pairs, or as a single anonymous hash reference.

EXPORTED SUBROUTINES

callback

A simple piece of syntactic sugar that announces a callback. The code reference it precedes is blessed as a Params::Callbacks::Callback object, disambiguating it from unblessed subs that are being passed as standard arguments.

Multiple callbacks may be chained together with or without comma separators:

callback { ... }, callback { ... }, callback { ... }    # Valid
callback { ... }  callback { ... }  callback { ... }    # Valid, too!

AUTHOR

Iain Campbell, <cpanic at cpan.org>

REPOSITORY

https://github.com/cpanic/DBIx-FlexibleBinding

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:

ACKNOWLEDGEMENTS

LICENSE AND COPYRIGHT

Copyright 2015 Iain Campbell.

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; version 2 dated June, 1991 or at your option any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

A copy of the GNU General Public License is available in the source tree; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA