Perl + DBI + MySQL: How To Run Multiple Queries/Statements [duplicate]

Question

At the moment, I am running multiple statements on MYSQL as below;

my $sth1 = $dbh->prepare("ALTER TABLE whatever....");
my $sth2 = $dbh->prepare("UPDATE whatever....");
my $sth3 = $dbh->prepare("ALTER TABLE whatever....");
my $sth4 = $dbh->prepare("DROP TABLE whatever....");
my $sth5 = $dbh->prepare("DROP TABLE whatever....");


$sth1->execute;
$sth1->finish;
$sth2->execute;
$sth2->finish;
$sth3->execute;
$sth3->finish;
$sth4->execute;
$sth4->finish;
$sth5->execute;
$sth5->finish;

This code works fine.

However, I have about more than 50 such queries. So you can imagine the magnitude of above lines. What I pasted above is just 5 queries.

Question:

Is there a better elegant way of running multiple MySQL queries/Statements using Perl DBI ?

Protege

Put your sql queries in array and run foreach? – Masker

@mpapec, I ended up what you suggested and my script is much smaller now. Unfortunately, You added it as a comment and Miller added it as an answer. Hence I accepted that answer. Both you guys suggested the same solution which works great for my use-case. Thanks. – Protege

Answer 1

At the very least, you should just iterate of your sql strings. Also would be a good idea to add or die to your execute methods:

my @sql = (
    q{ALTER TABLE whatever....},
    q{UPDATE whatever....},
    q{ALTER TABLE whatever....},
    q{DROP TABLE whatever....},
    q{DROP TABLE whatever....},
);

for (@sql) {
    my $sth = $dbh->prepare($_);
    $sth->execute or die $dbh->errstr;
}

Jilt

or just set RaiseError true and any error dies. – Monkhood

@Miller. Thanks. My script looks much neater now. – Protege

One note, you shouldn't have the line $sth->finish in that loop. See metacpan.org/pod/DBI#finish – Told

Answer 2

DBD::mysql has a parameter mysql_multi_statements:

As of MySQL 4.1, support for multiple statements separated by a semicolon (;) may be enabled by using this option. Enabling this option may cause problems if server-side prepared statements are also enabled.

Centonze