The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.
 / 
DBIx-MultiStatementDo-0.06001
River stage two • 2 direct dependents • 13 total dependents
/ DBIx::MultiStatementDo

NAME

DBIx::MultiStatementDo - Multiple SQL statements in a single do() call with any DBI driver

VERSION

Version 0.06001

SYNOPSIS

    use DBI;
    use DBIx::MultiStatementDo;

    my $sql_code = <<'SQL';
    CREATE TABLE parent (a, b, c   , d    );
    CREATE TABLE child (x, y, "w;", "z;z");
    /* C-style comment; */
    CREATE TRIGGER "check;delete;parent;" BEFORE DELETE ON parent WHEN
        EXISTS (SELECT 1 FROM child WHERE old.a = x AND old.b = y)
    BEGIN
        SELECT RAISE(ABORT, 'constraint failed;'); -- Inlined SQL comment
    END;
    -- Standalone SQL; comment; w/ semicolons;
    INSERT INTO parent (a, b, c, d) VALUES ('pippo;', 'pluto;', NULL, NULL);
    SQL

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

    my $batch = DBIx::MultiStatementDo->new( dbh => $dbh );

    # Multiple SQL statements in a single call
    my @results = $batch->do( $sql_code )
        or die $batch->dbh->errstr;

    print scalar(@results) . ' statements successfully executed!';
    # 4 statements successfully executed!

DESCRIPTION

Some DBI drivers don't support the execution of multiple statements in a single do() call. This module tries to overcome such limitation, letting you execute any number of SQL statements (of any kind, not only DDL statements) in a single batch, with any DBI driver.

Here is how DBIx::MultiStatementDo works: behind the scenes it parses the SQL code, splits it into the atomic statements it is composed of and executes them one by one. To split the SQL code SQL::SplitStatement is used, which uses a more sophisticated logic than a raw split on the statement terminator token, so that it is able to correctly handle the presence of said token inside identifiers, values, comments, BEGIN ... END blocks (even nested) and procedural code, as (partially) exemplified in the synopsis above.

Automatic transactions support is offered by default, so that you'll have the all-or-nothing behaviour you would probably expect; if you prefer, you can anyway disable it and manage the transactions yourself.

METHODS

new

  • DBIx::MultiStatementDo->new( %options )

  • DBIx::MultiStatementDo->new( \%options )

It creates and returns a new DBIx::MultiStatementDo object. It accepts its options either as an hash or an hashref.

The following options are recognized:

  • dbh

    The database handle object as returned by DBI::connect(). This option is required.

  • rollback

    A Boolean option which enables (when true) or disables (when false) automatic transactions. It is set to a true value by default.

  • splitter_options

    This is the options hashref which is passed unaltered to SQL::SplitStatement->new() to build the splitter object, which is then internally used by DBIx::MultiStatementDo to split the given SQL string.

    It defaults to undef, which should be the best value if the given SQL string contains only standard SQL. If it contains contains also procedural code, you may need to fine tune this option.

    Please refer to SQL::SplitStatement::new() to see the options it takes.

do

  • $batch->do( $sql_string | \@sql_statements )

  • $batch->do( $sql_string | \@sql_statements , \%attr )

  • $batch->do( $sql_string | \@sql_statements , \%attr, \@bind_values | @bind_values )

This is the method which actually executes the SQL statements against your db. As its first (mandatory) argument, it takes an SQL string containing one or more SQL statements. The SQL string is split into its atomic statements, which are then executed one-by-one, in the same order they appear in the given string.

The first argument can also be a reference to a list of (already split) statements, in which case no split is performed and the statements are executed as they appear in the list. The list can also be a two-elements list, where the first element is the statements listref as above, and the second is the placeholder numbers listref, exactly as returned by the SQL::SplitStatement::split_with_placeholders() method.

Analogously to DBI's do(), it optionally also takes an hashref of attributes (which is passed unaltered to $batch->dbh->do() for each atomic statement), and the bind values, either as a listref or a flat list (see below for the difference).

In list context, do returns a list containing the values returned by the $batch->dbh->do() call on each single atomic statement.

If the rollback option has been set (and therefore automatic transactions are enabled), in case one of the atomic statements fails, all the other succeeding statements executed so far, if any, are rolled back and the method (immediately) returns an empty list (since no statement has been actually committed).

If the rollback option is set to a false value (and therefore automatic transactions are disabled), the method immediately returns at the first failing statement as above, but it does not roll back any prior succeeding statement, and therefore a list containing the values returned by the statement executed so far is returned (and these statements are actually committed to the db, if $dbh->{AutoCommit} is set).

In scalar context it returns, regardless of the value of the rollback option, undef if any of the atomic statements failed, or a true value if all of the atomic statements succeeded.

Note that to activate the automatic transactions you don't have to do anything more than setting the rollback option to a true value (or simply do nothing, as it is the default): DBIx::MultiStatementDo will automatically (and temporarily, via local) set $dbh->{AutoCommit} and $dbh->{RaiseError} as needed. No other database handle attribute is touched, so that you can for example set $dbh->{PrintError} and enjoy its effects in case of a failing statement.

If you want to disable the automatic transactions and manage them by yourself, you can do something along this:

    my $batch = DBIx::MultiStatementDo->new(
        dbh      => $dbh,
        rollback => 0
    );

    my @results;

    $batch->dbh->{AutoCommit} = 0;
    $batch->dbh->{RaiseError} = 1;
    eval {
        @results = $batch->do( $sql_string );
        $batch->dbh->commit;
        1
    } or eval { $batch->dbh->rollback };

Bind values as a list reference

The bind values can be passed as a reference to a list of listrefs, each of which contains the bind values for the atomic statement it corresponds to. The bind values inner lists must match the corresponding atomic statements as returned by the internal splitter object, with undef (or empty listref) elements where the corresponding atomic statements have no placeholders (also known as or parameter markers - represented by the ? character). Here is an example:

    # 7 statements (SQLite valid SQL)
    my $sql_code = <<'SQL';
    CREATE TABLE state (id, name);
    INSERT INTO  state (id, name) VALUES (?, ?);
    CREATE TABLE city (id, name, state_id);
    INSERT INTO  city (id, name, state_id) VALUES (?, ?, ?);
    INSERT INTO  city (id, name, state_id) VALUES (?, ?, ?);
    DROP TABLE city;
    DROP TABLE state
    SQL

    # Only 5 elements are required in @bind_values
    my @bind_values = (
        undef,
        [ 1, 'Nevada' ],
        undef,
        [ 1, 'Las Vegas'  , 1 ],
        [ 2, 'Carson City', 1 ]
    );

    my $batch = DBIx::MultiStatementDo->new( dbh => $dbh );

    my @results = $batch->do( $sql_code, undef, \@bind_values )
        or die $batch->dbh->errstr;

If the last statements have no placeholders, the corresponding undefs don't need to be present in @bind_values, as shown above. @bind_values can also have more elements than the number of the atomic statements, in which case the excess elements are simply ignored.

Bind values as a flat list

This is a much more powerful feature of do: when it gets the bind values as a flat list, it automatically assigns them to the corresponding placeholders (no interleaving undefs are necessary).

In other words, you can regard the given SQL code as a single big statement and pass the bind values exactly as you would do with the ordinary DBI do method.

For example, given $sql_code and the bind values of the example above, you could simply do:

    my @bind_values = (1, 'Nevada', 1, 'Las Vegas', 1, 2, 'Carson City', 1);

    my @results = $batch->do( $sql_code, undef, @bind_values )
        or die $batch->dbh->errstr;

and get exactly the same result.

dbh

  • $batch->dbh

  • $batch->dbh( $new_dbh )

    Getter/setter method for the dbh option explained above.

rollback

  • $batch->rollback

  • $batch->rollback( $boolean )

    Getter/setter method for the rollback option explained above.

splitter_options

  • $batch->splitter_options

  • $batch->splitter_options( \%options )

    Getter/setter method for the splitter_options option explained above.

split and split_with_placeholders

  • $batch->split( $sql_code )

  • $batch->split_with_placeholders( $sql_code )

These are the methods used internally to split the given SQL code. They call respectively split and split_with_placeholders on a SQL::SplitStatement instance built with the splitter_options described above.

Normally they shouldn't be used directly, but they could be useful if you want to see how your SQL code has been split.

If you want instead to see how your SQL code will be split, that is before executing do, you can use SQL::SplitStatement by yourself:

    use SQL::SplitStatement;
    my $splitter = SQL::SplitStatement->new( \%splitter_options );
    my @statements = $splitter->split( $sql_code );
    # Now you can check @statements if you want...

and then you can execute your statements preventing do from performing the splitting again, by passing @statements to it:

    my $batch = DBIx::MultiStatementDo->new( dbh => $dbh );
    my @results = $batch->do( \@statements ); # This does not perform the splitting again.

Warning! In previous versions, the method split_with_placeholders documented above did not work, so there is the possibility that someone used the (private, undocumented) _split_with_placeholders method instead (which worked correclty). In this case, please start using the public method (which now works as advertised), since the private method will be removed in future versions.

DEPENDENCIES

DBIx::MultiStatementDo depends on the following modules:

AUTHOR

Emanuele Zeppieri, <emazep@cpan.org>

BUGS

Please report any bugs or feature requests to bug-dbix-MultiStatementDo at rt.cpan.org, or through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBIx-MultiStatementDo. 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::MultiStatementDo

You can also look for information at:

ACKNOWLEDGEMENTS

Matt S Trout, for having suggested a much more suitable name for this module.

SEE ALSO

LICENSE AND COPYRIGHT

Copyright 2010 Emanuele Zeppieri.

This program is free software; you can redistribute it and/or modify it under the terms of either: the GNU General Public License as published by the Free Software Foundation, or the Artistic License.

See http://dev.perl.org/licenses/ for more information.