"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 isrequired.
• "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
splitterobject, 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
placeholdernumbers 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 bindvalues, 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 statements have actually been 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 statements
(successfully) 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 DBI db handle attribute is ever 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
};
BindValuesasaListReference
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 splitterobject, with "undef" (or empty
listref) elements where the corresponding atomic statements have no placeholders.
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 the bind values list
my $bind_values = [
undef , # or []
[ 1, 'Nevada' ] ,
[] , # or 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 "undef"s don't need to be present in the
bind values list, as shown above. The bind values list can also have more elements than the number of
the atomic statements, in which case the excess elements will simply be ignored.
BindValuesasaFlatList
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 "undef"s are necessary in
this case).
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 from 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.
DifferencebetweenBindValuesasaListReferenceandasaFlatList
If you want to pass the bind values as a flat list as described above, you must pass the first parameter
to "do" either as a string (so that the internal splitting is performed) or, if you want to disable the
internal splitting, as a reference to the two-elements list containing both the statements and the
placeholder numbers listrefs (as described above in do).
In other words, you can't pass the bind values as a flat list and pass at the same time the (already
split) statements without the placeholder numbers listref. To do so, you need to pass the bind values as
a list reference instead, otherwise "do" throws an exception.
To summarize, bind values as a flat list is easier to use but it suffers from this subtle limitation,
while bind values as a list reference is a little bit more cumbersome to use, but it has no limitations
and can therefore always be used.
RecognizedPlaceholders
The recognized placeholders are:
• questionmark placeholders, represented by the "?" character;
• dollarsignnumbered placeholders, represented by the "$1, $2, ..., $n" strings;
• namedparameters, such as ":foo", ":bar", ":baz" etc.
"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 willbe 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 "split_with_placeholders" (public) method documented above did not
work, so there is the possibility that someone used the (private, undocumented)
"_split_with_placeholders" method instead (which worked correctly). In this case, please start using the
public method (which now works as advertised), since the private method will be removed in future
versions.
Install Now
PNG Icons
