NAME DBIx::MultiStatementDo - Multiple SQL statements in a single do() call with any DBI driver VERSION Version 0.02000 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 employes a more sophisticated logic than a raw "split" on the ";" (semicolon) character, so that it is able to correctly handle the presence of the semicolon inside identifiers, values, comments or "BEGIN..END" blocks (even nested blocks), as shown 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 to "SQL::SplitStatement->new()" to build the *splitter object*, which is then internally used by "DBIx::MultiStatementDo" to split the given SQL code. It defaults to "undef", which is the value that ensures the maximum portability across different DBMS. You should therefore not touch this option, unless you really know what you are doing. Please refer to SQL::SplitStatement::new() to see the options it takes. "do" * "$batch->do( $sql_string )" * "$batch->do( $sql_string, \%attr )" * "$batch->do( $sql_string, \%attr, \@bind_values )" This is the method which actually executes the SQL statements against your db. It takes a string containing one or more SQL statements and executes them one by one, in the same order they appear in the given SQL string. 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 a reference to a list of list refs, 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 bind values. 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 bind values, the corresponding "undef"s 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. 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 exists, 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 other 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 }; "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" * "DBIx::MultiStatementDo->split( $sql_string )" *WARNING* - This method is DEPRECATED and IT WILL BE REMOVED SOON! If you just want to split your SQL code, please use SQL::SplitStatement instead. This is a class method which splits the given SQL string into its atomic statements. Note that it is not the (instance) method used internally by DBIx::MultiStatementDo to split the SQL code, but a class method exposed here just for convenience. It does that by simply calling: SQL::SplitStatement->new->split($sql_string) It therefore returns a list of strings containing the code of each atomic statement, in the same order they appear in the given SQL string. Note that "SQL::SplitStatement->new()" is called with its default options, and that tha value of "splitter_options" has no effect on it. You shouldn't use it, unless you want to bypass all the other functionality offered by this module and do it by yourself, in which case you can use it like this: $dbh->do($_) foreach DBIx::MultiStatementDo->split( $sql_string ); (but, again, to do this it is better to directly use SQL::SplitStatement). DEPENDENCIES DBIx::MultiStatementDo depends on the following modules: * SQL::SplitStatement 0.01001 or newer * Moose AUTHOR Emanuele Zeppieri, "" BUGS Please report any bugs or feature requests to "bug-dbix-MultiStatementDo at rt.cpan.org", or through the web interface at . 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: * RT: CPAN's request tracker * AnnoCPAN: Annotated CPAN documentation * CPAN Ratings * Search CPAN ACKNOWLEDGEMENTS Matt S Trout, for having suggested a much more suitable name for this module. SEE ALSO * SQL::SplitStatement * DBI 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.