DBIx::...mentDo(3pm)

💖 DBIx::MultiStatementDo — the manual, but make it cute ✨

okay bestie, so this is the page for DBIx::MultiStatementDo, and it lives in User Contributed Perl Documentation. It's the perl doc you get with the module, and it is, lowkey, a whole gem. Let's get into it 🫶

---

✨ NAME ✨

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

---

🎀 VERSION 🎀

version 1.00009 — yes that's the one, no cap ⭐

---

🌈 SYNOPSIS 🌈

k so this is the whole vibe in one example, look:

use DBI;
use DBIx::MultiStatementDo;

# Multiple SQL statements in a single string
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!

not me putting ; inside strings and comments and trigger names and making it all just work 😭✨ the girlies love a parser that actually gets it.

---

💜 DESCRIPTION 💜

so here's the tea bestie: some DBI drivers are NOT it when it comes to running multiple statements in one single do() call. literally they just refuse. this module exists to fix that, letting you execute any number of SQL statements — of any kind, not just DDL statements, no cap — in a single batch, with any DBI driver. iconic behaviour, honestly 👑

now, the way DBIx::MultiStatementDo does it: behind the scenes it parses your SQL code, splits it into the atomic statements it's composed of, and executes them one by one. 🦋

and for the splitting part? it uses SQL::SplitStatement, which has way more sophisticated logic than a raw split on the ; (semicolon) character. bestie, that's the whole point. first, various different statement terminator tokens are recognized, and then SQL::SplitStatement is able to correctly handle said tokens showing up inside:

• identifiers
• values
• comments
• BEGIN ... END blocks (even nested — yes, NESTED 💀)
• dollar-quoted strings
• MySQL custom DELIMITERs
• procedural code

etc. — as (partially) exemplified in the SYNOPSIS above. so go back up and look at that trigger name with two semicolons in it. it survived. she ate 💅

and alsooo, automatic transactions support is offered by default, so you get that all-or-nothing behaviour you would probably expect 🫶 if you'd rather do it yourself, you can disable it and manage the transactions your own way. main character energy, your call, periodt ✨

---

🧚 lowkey a warning-vibe reminder: that all-or-nothing default means if something in the batch flops, the whole thing rolls back. don't say I didn't tell you, bestie.

---

✨ METHODS ✨

---

🎀 new

okay bestie, step one is literally just bringing the object into existence:

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

It creates and hands you back a shiny new DBIx::MultiStatementDo object. And because it's not a control freak, it'll take its options either as a plain hash or as a hashref — your call, whatever's cuter for your code. 💅

Here's what it recognizes:

• dbh — the database handle object you got back from DBI::connect(). This one is required, no skipping it, it's the whole point. 👑
• rollback — a Boolean. True = automatic transactions are on, false = they're off. Defaults to a true value, so if you say nothing, you're getting the safe ✨automatic✨ behavior. 🫶
• splitter_options — a hashref of options that gets passed unaltered to SQL::SplitStatement->new() to build the splitter object, which DBIx::MultiStatementDo then uses internally to chop up your SQL string. 🌸

It defaults to undef, which is legit the best value if your SQL string is just plain standard SQL. But if it's got procedural code in it too? Yeah, you're gonna want to fine-tune this one.

Peep SQL::SplitStatement::new() to see all the options it takes, iykyk.

---

🔥 do

The main character method. This is the one that actually executes your SQL against your db:

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

As its first argument — mandatory, bestie, you can't skip it — it takes an SQL string holding one or more SQL statements. That string gets split into its atomic statements, and then they get executed one-by-one, in the exact same order they appeared in your string. ✨

But wait, there's more. That first argument can also be a reference to a list of already-split statements, in which case no splitting happens at all and the statements just get executed as they show up in the list. 🦋 And that list can even be a two-element list, where element one is the statements listref (like above) and element two is the placeholder-numbers listref, exactly as SQL::SplitStatement::split_with_placeholders() would return it.

Just like DBI's own do(), it can also optionally take:

• an hashref of attributes — passed unaltered to $batch->dbh->do() for every single atomic statement, and
• the bind values, either as a listref or as a flat list (see below for why those two are not the same vibe).

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

If the rollback option is set (so automatic transactions are live) and one of the atomic statements fails, then every other statement that already succeeded so far gets rolled back, and the method returns an empty list, immediately. Makes sense — nothing actually got committed, so there's nothing to report. 😭

If rollback is set to a false value (automatic transactions off), the method still returns immediately at that first failing statement, but it does not roll anything back. So you get back a list of the values from the statements that did succeed, and — heads up — those statements are actually committed to the db if $dbh->{AutoCommit} is set. 🙈

In scalar context it returns, no matter what rollback is set to, undef if any atomic statement failed, or a true value if every single one of them succeeded.

Now here's the cute part: to turn on automatic transactions you literally don't have to do anything extra. Set rollback to a true value — or just do nothing at all, since that's the default. DBIx::MultiStatementDo will automatically (and temporarily, via local) set $dbh->{AutoCommit} and $dbh->{RaiseError} as needed. 🪄 And no other DBI db handle attribute is ever touched, so you can, for example, set $dbh->{PrintError} and have a great time with its effects whenever a statement fails.

Wanna disable the automatic transactions and run your own? Totally valid, do something like 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

You can pass the bind values as a reference to a list of listrefs, where each inner listref holds the bind values for the atomic statement it lines up with. Those inner lists must match the corresponding atomic statements as returned by the internal splitter object, with undef (or an empty listref) sitting in the spots where the matching atomic statement has no placeholders.

Example time:

# 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, those trailing undefs don't need to be there at all — see how the example above does it. 🌈 And the bind values list can have more elements than there are atomic statements, in which case the extra ones are just... ignored. Rude but efficient. 💀

💜 Bind Values as a Flat List

Okay this is the way more powerful feature of do: when it receives the bind values as a flat list, it automatically assigns them to the right placeholders for you. No interleaving undefs needed, periodt.

In other words — you can treat your SQL code as if it were one single big statement and pass bind values exactly the way you would with ordinary DBI do.

So with $sql_code from the example above, you could just 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. Slay. ✨

🧚 Difference between Bind Values as a List Reference and as a Flat List

If you want to pass bind values as a flat list like just described, you have to pass the first parameter to do either as a string (so the internal splitting actually happens) or — if you want the splitting off — as a reference to that two-element list holding both the statements and the placeholder-numbers listrefs (like we talked about up in do).

Put differently: you can't pass bind values as a flat list and hand over already-split statements without the placeholder-numbers listref. If you try, do throws an exception. 🚨 To do that, you need to pass the bind values as a list reference instead.

tl;dr — flat list = easier to use, but it's got this subtle little limitation. List reference = a touch more cumbersome, but it has no limitations and can therefore always be used. 🫶

👑 Recognized Placeholders

The placeholders it recognizes are:

• question mark placeholders, written as the ? character;
• dollar sign numbered placeholders, written as the $1, $2, ..., $n strings;
• named parameters, like :foo, :bar, :baz, etc. etc. 💅

---

💖 dbh

$batch->dbh
$batch->dbh( $new_dbh )

Getter/setter for the dbh option we explained up above. ✨

---

🌸 rollback

$batch->rollback
$batch->rollback( $boolean )

Getter/setter for the rollback option we explained up above. 😌

---

🦋 splitter_options

$batch->splitter_options
$batch->splitter_options( \%options )

Getter/setter for the splitter_options option we explained up above.

---

🌈 split and split_with_placeholders

$batch->split( $sql_code )
$batch->split_with_placeholders( $sql_code )

These are the methods used internally to split up the SQL code you give it. They call split and split_with_placeholders respectively on a SQL::SplitStatement instance that was built using the splitter_options described above.

Normally you shouldn't be calling these directly, but ngl they come in handy if you just want to peek at how your SQL code got split. 👀

And if you want to see how your SQL code will be split — i.e. before running do — you can just use SQL::SplitStatement 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 run your statements while stopping do from splitting them 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 public split_with_placeholders method documented right above did not work. So there's a real chance somebody out there used the (private, undocumented) _split_with_placeholders method instead, which did work correctly. If that's you — bestie, please switch to the public method (it works as advertised now 💅), because the private one is getting removed in future versions. No cap.

---

🚧 LIMITATIONS 🚧

Please look at: SQL::SplitStatement LIMITATIONS

---

📦 DEPENDENCIES 📦

DBIx::MultiStatementDo depends on these modules:

• SQL::SplitStatement 0.10000 or newer
• Moose

---

👑 AUTHOR 👑

Emanuele Zeppieri, "<emazep@cpan.org>"

---

🐛 BUGS 🐛

No known bugs so far. ✨ (iconic, honestly)

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

okay bestie, you need help? we got you, always 💖

You can find documentation for this module with the perldoc command. it's literally right there, chillin' in your terminal:

perldoc DBIx::MultiStatementDo

You can also look for information at:

• RT: CPAN's request tracker

<http://rt.cpan.org/NoAuth/Bugs.html?Dist=DBIx-MultiStatementDo>

• AnnoCPAN: Annotated CPAN documentation

<http://annocpan.org/dist/DBIx-MultiStatementDo>

• CPAN Ratings

<http://cpanratings.perl.org/d/DBIx-MultiStatementDo>

• Search CPAN

<http://search.cpan.org/dist/DBIx-MultiStatementDo/>

like, four whole places to vibe with other people who also wrote twelve SQL statements in one string and regret it 🙈 go say hi.

---

🎀 ACKNOWLEDGEMENTS

big shoutout to Matt S Trout, for having suggested a much more suitable name for this module. king behaviour honestly 👑 not all heroes wear capes, some of them just slide into your naming discourse with the correct answer.

---

👀 SEE ALSO

• SQL::SplitStatement
• DBI

yeah those two. the whole reason this module exists is bc DBI won't split your statements for you, and SQL::SplitStatement is doing the actual heavy lifting under the hood ✨ iykyk.

---

📜 LICENSE AND COPYRIGHT

Copyright 2010-2011 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.

---

tl;dr bestie — it's free, it's dual-licensed, go slay responsibly 💅

and that's a wrap on DBIx::MultiStatementDo, fr fr 💖 this page came from perl v5.36.0, dated 2022-08-28, so if you're reading this on some wild future perl version, just know the vibes travelled through time to reach you 🦋✨ mwah, go run some statements (all of them, at once, that's the whole point) 🌸