Run multiple sql scripts in perl

Question

I have 5 .sql scripts that create tables and insert into the tables. . I want to run them together as a transaction in PERL. I have searched for it in Google and SO , but only I could find about running the scripts using Batch script. I have no idea how to run the scripts together using PERL DBI as a transaction . Can anyone please help. I am new to perl and mysql and I have no idea how to do that .

This is what I am trying to do based on the suggestions by Barmar:

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Data::Dumper;

my $user="root";
my $password="M3m01r!@#";
my $db="DBI:$driver:database=$database";

my $dbh = DBI->connect("DBI:mysql:database=testdb;mysql_socket=/tmp/mysql.sock",$user,$password) or die "could not connect $DBI::errstr\n";
my $st = $dbh->prepare("mysql -u root -p < rr.sql") or die "$DBI::errstr\n";
$st->execute();

But it throws this error

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql -u root -p < rr.sql' at line 1 at dbi.pl line 15.

Can anyone help please?

Just read the files into Perl variables, and then execute them using Perl DBI. — Barmar
– Barmar, Commented Nov 8, 2013 at 5:30
Or is there a way , where I can have all the 5 sql scripts in one sql script and execute that single sql script that runs all the other sql script — Newbie
– Newbie, Commented Nov 8, 2013 at 5:40
Forget about scripts. They're just SQL queries. Once you put them in Perl variables, it doesn't matter where they came from. You use Perl DBI the same as if you'd put the queries in Perl directly. — Barmar
– Barmar, Commented Nov 8, 2013 at 6:00
You cannot "share" a transaction over multiple batched files. Put them together and execute it with DBI as @Barmar said. — smartmeta
– smartmeta, Commented Nov 8, 2013 at 7:36

Len Jaffe · Accepted Answer · 2013-11-08 15:09:52Z

1

 ############################################################################
 my $st = $dbh->prepare("mysql -u root -p < rr.sql") or die "$DBI::errstr\n";
 ############################################################################

Line 2 is where you're broken.

The string that you feed to prepare is supposed to be the actual SQL. You've fed it a shell command intended to run mysql. So take the contents of rr.sql, put it in a variable (either have your program read it in, or copy/paste it), and then call prepare() on the variable.

 my $ferret_query = "select name, dob, type from ferret order by dob";
 my $sth = $dbh->prepare($ferret_query);
 $sth->execute()

answered Nov 8, 2013 at 15:09

Len Jaffe

3,4941 gold badge24 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Run multiple sql scripts in perl

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related