.\" Automatically generated by Pod::Man 4.14 (Pod::Simple 3.40) .\" .\" Standard preamble: .\" ======================================================================== .de Sp \" Vertical space (when we can't use .PP) .if t .sp .5v .if n .sp .. .de Vb \" Begin verbatim text .ft CW .nf .ne \\$1 .. .de Ve \" End verbatim text .ft R .fi .. .\" Set up some character translations and predefined strings. \*(-- will .\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left .\" double quote, and \*(R" will give a right double quote. \*(C+ will .\" give a nicer C++. Capital omega is used to do unbreakable dashes and .\" therefore won't be available. \*(C` and \*(C' expand to `' in nroff, .\" nothing in troff, for use with C<>. .tr \(*W- .ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p' .ie n \{\ . ds -- \(*W- . ds PI pi . if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch . if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\" diablo 12 pitch . ds L" "" . ds R" "" . ds C` "" . ds C' "" 'br\} .el\{\ . ds -- \|\(em\| . ds PI \(*p . ds L" `` . ds R" '' . ds C` . ds C' 'br\} .\" .\" Escape single quotes in literal strings from groff's Unicode transform. .ie \n(.g .ds Aq \(aq .el .ds Aq ' .\" .\" If the F register is >0, we'll generate index entries on stderr for .\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index .\" entries marked with X<> in POD. Of course, you'll have to process the .\" output yourself in some meaningful fashion. .\" .\" Avoid warning from groff about undefined register 'F'. .de IX .. .nr rF 0 .if \n(.g .if rF .nr rF 1 .if (\n(rF:(\n(.g==0)) \{\ . if \nF \{\ . de IX . tm Index:\\$1\t\\n%\t"\\$2" .. . if !\nF==2 \{\ . nr % 0 . nr F 2 . \} . \} .\} .rr rF .\" ======================================================================== .\" .IX Title "DBIx::MultiStatementDo 3pm" .TH DBIx::MultiStatementDo 3pm "2020-12-27" "perl v5.32.0" "User Contributed Perl Documentation" .\" For nroff, turn off justification. Always turn off hyphenation; it makes .\" way too many mistakes in technical documents. .if n .ad l .nh .SH "NAME" DBIx::MultiStatementDo \- Multiple SQL statements in a single do() call with any DBI driver .SH "VERSION" .IX Header "VERSION" version 1.00009 .SH "SYNOPSIS" .IX Header "SYNOPSIS" .Vb 2 \& use DBI; \& use DBIx::MultiStatementDo; \& \& # Multiple SQL statements in a single string \& my $sql_code = <<\*(AqSQL\*(Aq; \& 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, \*(Aqconstraint failed;\*(Aq); \-\- Inlined SQL comment \& END; \& \-\- Standalone SQL; comment; w/ semicolons; \& INSERT INTO parent (a, b, c, d) VALUES (\*(Aqpippo;\*(Aq, \*(Aqpluto;\*(Aq, NULL, NULL); \& SQL \& \& my $dbh = DBI\->connect( \*(Aqdbi:SQLite:dbname=my.db\*(Aq, \*(Aq\*(Aq, \*(Aq\*(Aq ); \& \& 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) . \*(Aq statements successfully executed!\*(Aq; \& # 4 statements successfully executed! .Ve .SH "DESCRIPTION" .IX Header "DESCRIPTION" Some \s-1DBI\s0 drivers don't support the execution of multiple statements in a single \&\f(CW\*(C`do()\*(C'\fR call. This module tries to overcome such limitation, letting you execute any number of \s-1SQL\s0 statements (of any kind, not only \s-1DDL\s0 statements) in a single batch, with any \s-1DBI\s0 driver. .PP Here is how DBIx::MultiStatementDo works: behind the scenes it parses the \s-1SQL\s0 code, splits it into the atomic statements it is composed of and executes them one by one. To split the \s-1SQL\s0 code SQL::SplitStatement is used, which uses a more sophisticated logic than a raw \f(CW\*(C`split\*(C'\fR on the \f(CW\*(C`;\*(C'\fR (semicolon) character: first, various different statement terminator \fItokens\fR are recognized, then SQL::SplitStatement is able to correctly handle the presence of said tokens inside identifiers, values, comments, \f(CW\*(C`BEGIN ... END\*(C'\fR blocks (even nested), \&\fIdollar-quoted\fR strings, MySQL custom \f(CW\*(C`DELIMITER\*(C'\fRs, procedural code etc., as (partially) exemplified in the \*(L"\s-1SYNOPSIS\*(R"\s0 above. .PP Automatic transactions support is offered by default, so that you'll have the \&\fIall-or-nothing\fR behaviour you would probably expect; if you prefer, you can anyway disable it and manage the transactions yourself. .SH "METHODS" .IX Header "METHODS" .ie n .SS """new""" .el .SS "\f(CWnew\fP" .IX Subsection "new" .IP "\(bu" 4 \&\f(CW\*(C`DBIx::MultiStatementDo\->new( %options )\*(C'\fR .IP "\(bu" 4 \&\f(CW\*(C`DBIx::MultiStatementDo\->new( \e%options )\*(C'\fR .PP It creates and returns a new DBIx::MultiStatementDo object. It accepts its options either as an hash or an hashref. .PP The following options are recognized: .IP "\(bu" 4 \&\f(CW\*(C`dbh\*(C'\fR .Sp The database handle object as returned by \fBDBI::connect()\fR. This option \fBis required\fR. .IP "\(bu" 4 \&\f(CW\*(C`rollback\*(C'\fR .Sp A Boolean option which enables (when true) or disables (when false) automatic transactions. It is set to a true value by default. .IP "\(bu" 4 \&\f(CW\*(C`splitter_options\*(C'\fR .Sp This is the options hashref which is passed unaltered to \f(CW\*(C`SQL::SplitStatement\->new()\*(C'\fR to build the \fIsplitter object\fR, which is then internally used by DBIx::MultiStatementDo to split the given \s-1SQL\s0 string. .Sp It defaults to \f(CW\*(C`undef\*(C'\fR, which should be the best value if the given \s-1SQL\s0 string contains only standard \s-1SQL.\s0 If it contains contains also procedural code, you may need to fine tune this option. .Sp Please refer to \fBSQL::SplitStatement::new()\fR to see the options it takes. .ie n .SS """do""" .el .SS "\f(CWdo\fP" .IX Subsection "do" .IP "\(bu" 4 \&\f(CW\*(C`$batch\->do( $sql_string | \e@sql_statements )\*(C'\fR .IP "\(bu" 4 \&\f(CW\*(C`$batch\->do( $sql_string | \e@sql_statements , \e%attr )\*(C'\fR .IP "\(bu" 4 \&\f(CW\*(C`$batch\->do( $sql_string | \e@sql_statements , \e%attr, \e@bind_values | @bind_values )\*(C'\fR .PP This is the method which actually executes the \s-1SQL\s0 statements against your db. As its first (mandatory) argument, it takes an \s-1SQL\s0 string containing one or more \&\s-1SQL\s0 statements. The \s-1SQL\s0 string is split into its atomic statements, which are then executed one-by-one, in the same order they appear in the given string. .PP 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 \&\fIplaceholder numbers\fR listref, exactly as returned by the \&\fBSQL::SplitStatement::split_with_placeholders()\fR method. .PP Analogously to \s-1DBI\s0's \f(CW\*(C`do()\*(C'\fR, it optionally also takes an hashref of attributes (which is passed unaltered to \f(CW\*(C`$batch\->dbh\->do()\*(C'\fR for each atomic statement), and the \fIbind values\fR, either as a listref or a flat list (see below for the difference). .PP In list context, \f(CW\*(C`do\*(C'\fR returns a list containing the values returned by the \&\f(CW\*(C`$batch\->dbh\->do()\*(C'\fR call on each single atomic statement. .PP If the \f(CW\*(C`rollback\*(C'\fR 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). .PP If the \f(CW\*(C`rollback\*(C'\fR 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 \f(CW\*(C`$dbh\->{AutoCommit}\*(C'\fR is set). .PP In scalar context it returns, regardless of the value of the \f(CW\*(C`rollback\*(C'\fR option, \&\f(CW\*(C`undef\*(C'\fR if any of the atomic statements failed, or a true value if all of the atomic statements succeeded. .PP Note that to activate the automatic transactions you don't have to do anything more than setting the \f(CW\*(C`rollback\*(C'\fR option to a true value (or simply do nothing, as it is the default): DBIx::MultiStatementDo will automatically (and temporarily, via \f(CW\*(C`local\*(C'\fR) set \f(CW\*(C`$dbh\->{AutoCommit}\*(C'\fR and \&\f(CW\*(C`$dbh\->{RaiseError}\*(C'\fR as needed. No other \s-1DBI\s0 db handle attribute is ever touched, so that you can for example set \f(CW\*(C`$dbh\->{PrintError}\*(C'\fR and enjoy its effects in case of a failing statement. .PP If you want to disable the automatic transactions and manage them by yourself, you can do something along this: .PP .Vb 4 \& 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 \& }; .Ve .PP \fIBind Values as a List Reference\fR .IX Subsection "Bind Values as a List Reference" .PP 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 \fIinner\fR lists must match the corresponding atomic statements as returned by the internal \fIsplitter object\fR, with \f(CW\*(C`undef\*(C'\fR (or empty listref) elements where the corresponding atomic statements have no \fIplaceholders\fR. .PP Here is an example: .PP .Vb 10 \& # 7 statements (SQLite valid SQL) \& my $sql_code = <<\*(AqSQL\*(Aq; \& 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, \*(AqNevada\*(Aq ] , \& [] , # or undef \& [ 1, \*(AqLas Vegas\*(Aq , 1 ], \& [ 2, \*(AqCarson City\*(Aq, 1 ] \& ]; \& \& my $batch = DBIx::MultiStatementDo\->new( dbh => $dbh ); \& \& my @results = $batch\->do( $sql_code, undef, $bind_values ) \& or die $batch\->dbh\->errstr; .Ve .PP If the last statements have no placeholders, the corresponding \f(CW\*(C`undef\*(C'\fRs 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. .PP \fIBind Values as a Flat List\fR .IX Subsection "Bind Values as a Flat List" .PP This is a much more powerful feature of \f(CW\*(C`do\*(C'\fR: when it gets the bind values as a flat list, it automatically assigns them to the corresponding placeholders (no \&\fIinterleaving\fR \f(CW\*(C`undef\*(C'\fRs are necessary in this case). .PP In other words, you can regard the given \s-1SQL\s0 code as a single big statement and pass the bind values exactly as you would do with the ordinary \s-1DBI\s0 \f(CW\*(C`do\*(C'\fR method. .PP For example, given \f(CW$sql_code\fR from the example above, you could simply do: .PP .Vb 1 \& my @bind_values = ( 1, \*(AqNevada\*(Aq, 1, \*(AqLas Vegas\*(Aq, 1, 2, \*(AqCarson City\*(Aq, 1 ); \& \& my @results = $batch\->do( $sql_code, undef, @bind_values ) \& or die $batch\->dbh\->errstr; .Ve .PP and get exactly the same result. .PP \fIDifference between Bind Values as a List Reference and as a Flat List\fR .IX Subsection "Difference between Bind Values as a List Reference and as a Flat List" .PP If you want to pass the bind values as a flat list as described above, you must pass the first parameter to \f(CW\*(C`do\*(C'\fR 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). .PP 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 \f(CW\*(C`do\*(C'\fR throws an exception. .PP 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. .PP \fIRecognized Placeholders\fR .IX Subsection "Recognized Placeholders" .PP The recognized placeholders are: .IP "\(bu" 4 \&\fIquestion mark\fR placeholders, represented by the \f(CW\*(C`?\*(C'\fR character; .IP "\(bu" 4 \&\fIdollar sign numbered\fR placeholders, represented by the \&\f(CW\*(C`$1, $2, ..., $n\*(C'\fR strings; .IP "\(bu" 4 \&\fInamed parameters\fR, such as \f(CW\*(C`:foo\*(C'\fR, \f(CW\*(C`:bar\*(C'\fR, \f(CW\*(C`:baz\*(C'\fR etc. .ie n .SS """dbh""" .el .SS "\f(CWdbh\fP" .IX Subsection "dbh" .IP "\(bu" 4 \&\f(CW\*(C`$batch\->dbh\*(C'\fR .IP "\(bu" 4 \&\f(CW\*(C`$batch\->dbh( $new_dbh )\*(C'\fR .Sp Getter/setter method for the \f(CW\*(C`dbh\*(C'\fR option explained above. .ie n .SS """rollback""" .el .SS "\f(CWrollback\fP" .IX Subsection "rollback" .IP "\(bu" 4 \&\f(CW\*(C`$batch\->rollback\*(C'\fR .IP "\(bu" 4 \&\f(CW\*(C`$batch\->rollback( $boolean )\*(C'\fR .Sp Getter/setter method for the \f(CW\*(C`rollback\*(C'\fR option explained above. .ie n .SS """splitter_options""" .el .SS "\f(CWsplitter_options\fP" .IX Subsection "splitter_options" .IP "\(bu" 4 \&\f(CW\*(C`$batch\->splitter_options\*(C'\fR .IP "\(bu" 4 \&\f(CW\*(C`$batch\->splitter_options( \e%options )\*(C'\fR .Sp Getter/setter method for the \f(CW\*(C`splitter_options\*(C'\fR option explained above. .ie n .SS """split"" and ""split_with_placeholders""" .el .SS "\f(CWsplit\fP and \f(CWsplit_with_placeholders\fP" .IX Subsection "split and split_with_placeholders" .IP "\(bu" 4 \&\f(CW\*(C`$batch\->split( $sql_code )\*(C'\fR .IP "\(bu" 4 \&\f(CW\*(C`$batch\->split_with_placeholders( $sql_code )\*(C'\fR .PP These are the methods used internally to split the given \s-1SQL\s0 code. They call respectively \f(CW\*(C`split\*(C'\fR and \f(CW\*(C`split_with_placeholders\*(C'\fR on a SQL::SplitStatement instance built with the \f(CW\*(C`splitter_options\*(C'\fR described above. .PP Normally they shouldn't be used directly, but they could be useful if you want to see how your \s-1SQL\s0 code has been split. .PP If you want instead to see how your \s-1SQL\s0 code \fIwill be\fR split, that is before executing \f(CW\*(C`do\*(C'\fR, you can use SQL::SplitStatement by yourself: .PP .Vb 4 \& use SQL::SplitStatement; \& my $splitter = SQL::SplitStatement\->new( \e%splitter_options ); \& my @statements = $splitter\->split( $sql_code ); \& # Now you can check @statements if you want... .Ve .PP and then you can execute your statements preventing \f(CW\*(C`do\*(C'\fR from performing the splitting again, by passing \f(CW\*(C`\e@statements\*(C'\fR to it: .PP .Vb 2 \& my $batch = DBIx::MultiStatementDo\->new( dbh => $dbh ); \& my @results = $batch\->do( \e@statements ); # This does not perform the splitting again. .Ve .PP \&\fBWarning!\fR In previous versions, the \f(CW\*(C`split_with_placeholders\*(C'\fR (public) method documented above did not work, so there is the possibility that someone used the (private, undocumented) \f(CW\*(C`_split_with_placeholders\*(C'\fR 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. .SH "LIMITATIONS" .IX Header "LIMITATIONS" Please look at: SQL::SplitStatement \s-1LIMITATIONS\s0 .SH "DEPENDENCIES" .IX Header "DEPENDENCIES" DBIx::MultiStatementDo depends on the following modules: .IP "\(bu" 4 SQL::SplitStatement 0.10000 or newer .IP "\(bu" 4 Moose .SH "AUTHOR" .IX Header "AUTHOR" Emanuele Zeppieri, \f(CW\*(C`\*(C'\fR .SH "BUGS" .IX Header "BUGS" No known bugs so far. .PP Please report any bugs or feature requests to \&\f(CW\*(C`bug\-dbix\-MultiStatementDo at rt.cpan.org\*(C'\fR, 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. .SH "SUPPORT" .IX Header "SUPPORT" You can find documentation for this module with the perldoc command. .PP .Vb 1 \& perldoc DBIx::MultiStatementDo .Ve .PP You can also look for information at: .IP "\(bu" 4 \&\s-1RT: CPAN\s0's request tracker .Sp .IP "\(bu" 4 AnnoCPAN: Annotated \s-1CPAN\s0 documentation .Sp .IP "\(bu" 4 \&\s-1CPAN\s0 Ratings .Sp .IP "\(bu" 4 Search \s-1CPAN\s0 .Sp .SH "ACKNOWLEDGEMENTS" .IX Header "ACKNOWLEDGEMENTS" Matt S Trout, for having suggested a much more suitable name for this module. .SH "SEE ALSO" .IX Header "SEE ALSO" .IP "\(bu" 4 SQL::SplitStatement .IP "\(bu" 4 \&\s-1DBI\s0 .SH "LICENSE AND COPYRIGHT" .IX Header "LICENSE AND COPYRIGHT" Copyright 2010\-2011 Emanuele Zeppieri. .PP This program is free software; you can redistribute it and/or modify it under the terms of either: the \s-1GNU\s0 General Public License as published by the Free Software Foundation, or the Artistic License. .PP See http://dev.perl.org/licenses/ for more information.