NAME

DBI::Fetch - Prepare SQL statements, execute them and process the results easily.

SYNOPSIS

use DBI::Fetch qw/process/;

my $dbh = DBI->connect("dbi:mysql:database=test;host=localhost", "testuser", "testpass", { 
    RaiseError => 1, 
    PrintError => 0,
} );

# Execute statements, or just continue using $dbh->do(...)
#
process $dbh, << "__EOF__";
    CREATE TABLE tracks (
        id INT,
        name VARCHAR(20)
    )
__EOF__

process $dbh, << "__EOF__";
    INSERT INTO tracks 
        (id, name) 
    VALUES 
        (1, 'The Wings of Icarus'),
        (2, 'Pulsar'),
        (3, 'The Sentinel'),
        (4, 'Adrift on Celestial Seas')
__EOF__

# Get a single row
#
my $result = process $dbh, "SELECT * FROM tracks WHERE id = 2";

# Get multiple rows
#
my @result = process $dbh, "SELECT * FROM tracks";

# Get all result sets using lower-overhead return as reference
#
DBI::Fetch->config(return_result_sets_as_ref => 1);

my $result = process $dbh, "SELECT * FROM tracks";

# Get a result set using lower-overhead return as reference
# for the next query only
#
DBI::Fetch->push_config(return_result_sets_as_ref => 1);

my $result = process $dbh, "SELECT * FROM tracks";

DBI::Fetch->pop_config;

# Get result set using lower-overhead return as reference
# for the next query only
#
DBI::Fetch->push_config(
    return_result_sets_as_ref => 1, 
    auto_pop_config           => 1
);

my $result = process $dbh, "SELECT * FROM tracks";

# Process rows immediately using fetch-time callbacks
#
my @names = process $dbh, "SELECT name FROM tracks", sub {
    my $row = shift;
    return $row->{name};
};

# Provide parameterized input using ANY of the placeholder styles
# popularised by some of the drivers.
#
my $id = 3;

# Using standard "?" placeholder notation
#
my $result = process( 
    $dbh, "SELECT * FROM tracks WHERE id = ?", $id
);

# Or the Pg ":1" placeholder notation
#
my $result = process(
    $dbh, "SELECT * FROM tracks WHERE id = :1", $id
);

# Or the Oracle ":name" placeholder notation
#
my $result = process(
    $dbh, "SELECT * FROM tracks WHERE id = :id", id => $id
);

# Same again using ":" prefix on parameter keys for clarity
#
my $result = process(
    $dbh, "SELECT * FROM tracks WHERE id = :id", ":id" => $id
);

print "Track #$id $result\n";

# Mixing parameterized input and fetch-time callbacks is easy
#
process $dbh, "SELECT * FROM tracks WHERE id = :id", id => $id, sub {
    my $row = shift;
    print "Track #$row->{id} $row->{name}\n";    
};

DESCRIPTION

I had three goals when creating the DBI::Fetch module:

Simplify lower-level database interactions and help the developer write less code that does more and to keep that code readable.
Remove the irritation of having to constantly adjust to the parameter placeholder style offered by this or that DBI driver. I love using MySQL but, honestly, I personally prefer using the clearer and more readable ":name" placeholder style offered by the Oracle driver.

I wanted to make it possible for any developer who has to use DBI to continue using his or her favoured style, so DBI::Fetch offers them all but normalizes down to standard "?" style.
Provide a light-weight and accessible way to process result-sets row by row and at fetch time, yet still offer the ability to recall, store and iterate over result sets the traditional way.

In short, I wanted to make it easier to work with DBI without requiring access to or knowledge of larger, more complex database abstraction layers.

THE PROCESS FUNCTION

RESULT = DBI::Fetch->process( [DB-HANDLE,] STATEMENT [, PARAMETER-LIST] [, CALLBACK-LIST] );

RESULT = DBI::Fetch::process( [DB-HANDLE,] STATEMENT [, PARAMETER-LIST] [, CALLBACK-LIST] );

RESULT = process( [DB-HANDLE,] STATEMENT [, PARAMETER-LIST] [, CALLBACK-LIST] );

The process function will prepare your statement, bind it to any parameters you have provided, execute that statement and apply your callbacks to each row of your the result as it is collected from the database. It does a lot yet can be very expressively written. It's also flexible and subtle. The function accepts the following parameters:

The function attempts to be smart about how it packages the return value from any SQL statement that would yield a result set. Under such circumstances, calls made in list context receive a list of results, whereas calls made in scalar context may or may not get the number of rows in the result set depending on the size of that result set. If the result set contains more or less than one row, then number of rows is returned; however, if the result set contains exactly one row then the row itself is returned. To correctly determine the number of rows in scalar context, the developer should use Perl's built-in scalar function.

DB-HANDLE

The database handle obtained using a call to DBI->connect(...), and sometimes it's optional.

The default behaviour is for process to remember the last used database handle and to use that remembered value if the parameter is omitted in subsequent calls. Obviously, it will complain if there is no suitable value to use.

It is also possible to pre-configure the process function to use a specific database handle and also to have it not remember the last used database handle (see CONFIGURATION).

STATEMENT

A string containing the SQL statement to be prepared or the statement handle of a statement that was prepared using DBI::db->prepare(...).

Be careful if you choose to pass statement handles instead of statement strings. When you pass a pre-prepared statement handle, you must also supply any bindable parameters in a manner that is consistent with your DBD driver's native support for placeholders, i.e. your statement is already prepared, there is no flexibility regarding placeholders.

By passing the SQL statement in as a string, the process function will normalize the query so that it always uses "?" placeholders internally and reformats any parameters accordingly. So you retain complete flexibility regarding placeholder styles.

PARAMETER-LIST

An optional list of one or more parameters to be bound to the prepared statement. Organised by placeholder style, the following are examples of well-formed parameter lists:

?-style

VALUE-1, VALUE-2, [ VALUE-3, \%ATTRS ], ... , VALUE-N
[ VALUE-1, VALUE-2, [ VALUE-3, \%ATTRS ], ... , VALUE-N ]

:1-style

VALUE-1, VALUE-2, [ VALUE-3, \%ATTRS ], ... , VALUE-N
[ VALUE-1, VALUE-2, [ VALUE-3, \%ATTRS ], ... , VALUE-N ]

:name-style

NAME-1 => VALUE-1, ':NAME-2' => VALUE-2, NAME-3 => [ VALUE-3, \%ATTRS ], ... , NAME-N => VALUE-N
{ NAME-1 => VALUE-1, ':NAME-2' => VALUE-2, NAME-3 => [ VALUE-3, \%ATTRS ], ... , NAME-N => VALUE-N }
[ NAME-1 => VALUE-1, ':NAME-2' => VALUE-2, NAME-3 => [ VALUE-3, \%ATTRS ], ... , NAME-N => VALUE-N ]

You're free to enclose your parameter list inside an appropriate anonymous list container, or not if you prefer. In the case of :name-style binding, you may prefix your keys with : but it's not necessary.

CALLBACK-LIST

An optional list containing code references or anonymous subroutines used to filter or otherwise process rows as they are collected. After it has been fetched, each row is passed to the first callback for processing. If there are additional callbacks, the result of that processing is passed along to the next callback until it reaches the end of the queue. The result returned from the final stage of the callback queue will be added to the set of results returned by the process function. It's perfectly acceptable for the callback queue to yield an empty list or a list of more than one value, as well as a single value.

CONFIGURATION

HASH-REF = DBI::Fetch->config( [ { KEY-VALUE-PAIR(S) } | KEY-VALUE-PAIR(S) ] );

HASH-REF = DBI::Fetch::config( [ { KEY-VALUE-PAIR(S) } | KEY-VALUE-PAIR(S) ] );

HASH-REF = DBI::Fetch->push_config( [ { KEY-VALUE-PAIR(S) } | KEY-VALUE-PAIR(S) ] );

HASH-REF = DBI::Fetch::push_config( [ { KEY-VALUE-PAIR(S) } | KEY-VALUE-PAIR(S) ] );

HASH-REF = DBI::Fetch->pop_config( [ { KEY-VALUE-PAIR(S) } | KEY-VALUE-PAIR(S) ] );

HASH-REF = DBI::Fetch::pop_config( [ { KEY-VALUE-PAIR(S) } | KEY-VALUE-PAIR(S) ] );

The config functions may be used to alter the behaviour of the process function by setting or resetting the following parameters:

auto_pop_config => BOOLEAN

When true, this setting causes the process function to discard the active frame from the configuration stack and restore the previous configuration.

If the active frame is the only frame in the configuration stack then no action is taken and the flag is cleared.

This setting will probably only be helpful when combined with push_config as it is in the following example:

DBI::Fetch->push_config(
    return_result_sets_as_ref => 1,
    auto_pop_config           => 1
);

my $result_set_ref = process($dbh, 'SELECT * FROM tracks');

The setting saves you having to call DBI::Fetch-pop_config()> when changing the behaviour of a single process call.

dbh => DATABASE-HANDLE

Sets which database handle the process function will fall back to when one is absent from the parameter list.

The default behaviour of process is to "remember" the last database handle used. Setting the dbh in this way automatically cancels that behaviour; clearing it reverts back to the default behaviour.

fetch_row_using => CODE-REFERENCE

Sets which code is used to fetch rows from the database.

The default behaviour is for process is to execute this code:

sub { $_[0]->fetchrow_hashref('NAME_lc') }

If you don't like it, change it; but make sure your callbacks process the correct type of structure.

remember_last_used_dbh => BOOLEAN

When true, the remember_last_used_dbh setting causes the process function to remember the last database handle it used, and this is the default behaviour.

It's useful in repeated interactions with the same database connection. The process function will fall back to the last used database handle one is omitted from its parameter list.

When false, the process function will not update the last used database handle (whether it is set or otherwise).

return_result_sets_as_ref => BOOLEAN

When true, this setting forces the process function to return result sets as array references, thereby removing the need for a potentially expensive copy operation on large sets. Note that this behaviour is restricted to result sets and the the return values from non-SELECT SQL statements.

The config functions come in three flavours: push_config, pop_config and plain old vanilla config. Visualize the configuration as a stack of configurations, in which the current or active frame dictates the behaviour of the process function.

Whereas config allows you to work with the active configuration, push_config will copy the active configuration into a new frame which then becomes the active configuration.

The pop_config function restores the previously active configuration. You are prevented from accidentally discarding the original configuration.

All three functions in this group take the same parameters (one of more active configuration settings) and yield a reference to the active configuration hash.

EXPORTS

None.

process

BUG REPORTS

Please report any bugs to http://rt.cpan.org/

AUTHOR

Iain Campbell <cpanic@cpan.org>

COPYRIGHT AND LICENCE

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.14.2 or, at your option, any later version of Perl 5 you may have available.

To install DBI::Fetch, copy and paste the appropriate command in to your terminal.

cpanm

cpanm DBI::Fetch

CPAN shell

perl -MCPAN -e shell
install DBI::Fetch

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)