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

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 ?

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;
}