The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
DBI-Fetch-1.00
++
/ DBI::Fetch

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:

  1. Help developers who need to interact with DBI directly do so but with less code that does much more.

  2. Remove the irritation of having to adjust to a different parameter placeholder style at the least convenient time.

  3. Provide a simple and more intuitive method for processing result sets.

PROCESSING RESULTS

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. The function accepts the following parameters:

DB-HANDLE

A database handle obtained using a call to DBI->connect(...).

The default behaviour is for process to remember the last database handle it used and to use that handle if the parameter is omitted in a call.

It is also possible to pre-configure the process function to use a specific database handle and prevent it from overwriting that value.

STATEMENT

A string containing the SQL statement to be prepared, or the handle of a statement that has already been prepared.

Passing the statement in as a string gives you more flexibility over which placeholder style you can choose to use. It may, however, not be the best performing choice because process will have it prepared each time.

Passing the statement in as a database handle gives you no flexibility over placeholder style and, as a consequence, how you must bind parameters. It does, however, give you better performance because process won't bother having it prepared.

PARAMETER-LIST

An optional list of parameters to be bound to a prepared statement. The type of list you will use will depend upon the placeholder style you prefer to use.

Organised by placeholder style, the following are all 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 ]

The choice to enclose your parameter list inside a list container is yours to make. Both options are acceptable.

When using :name-style placeholders, the choice of whether or not to prefix binding parameter names with a leading colon (:) is also yours to make. Again it doesn't matter.

CALLBACK-LIST

The Callback List is an optional list of code references or anonymous subroutines that will be used to process your results. Result sets are processed row-by-row as each row is fetched.

Each callback receives the result in $_[0] and returns a result to the next callback in the chain. The terminating result will be returned to the caller.

A result may be manipulated during callback processing, or eliminated altogether by returning an empty list.

The process function attempts to be smart about how it handles the return value from SQL statements that return result sets.

When called in List Context, process will return a list of rows. When called Scalar Context, on the other hand, things are somewhat trickier but predictable. If the result set contains a single row then that row is returned; any other outcome results in the number of rows being returned. You should reserve Scalar Context calls for situations in which you expect your result set to contain a single row, or you are performing another operation for which the number of affected rows needs to be known.

To correctly determine the number of rows, the developer should use Perl's built-in scalar function. For example:

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

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

Tag group ":default"

None.

Tag group ":all"

process

BUG REPORTS

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

AUTHOR

Iain Campbell <cpanic@cpan.org>

COPYRIGHT AND LICENCE

Copyright (C) 2012 by Iain Campbell

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.