Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Execute SQL file in Perl

Tags:

sql

perl

We have a Perl script which runs a SQL and puts data in the table. Now instead of supplying a single SQL statement, we want to pass bunch of them putting them together in a .sql file. We know that our program will fail because it expects a single SQL statement, not s bunch of them (that too from a .sql file). How do we make it work with a .sql file (having multiple INSERT statements?). We are using the DBI package.

A small snippet of code:

$sth = $dbh->prepare("/home/user1/tools/mytest.sql");
$sth->execute || warn "Couldn't execute statement";
$sth->finish();
like image 830
t0mcat Avatar asked Nov 18 '10 16:11

t0mcat


3 Answers

There is a sort of workaround for DDL. You need to slurp SQL file first and then enclose it's contents into BEGIN ... END; keywords. Like:

sub exec_sql_file {
    my ($dbh, $file) = @_;

    my $sql = do {
        open my $fh, '<', $file or die "Can't open $file: $!";
        local $/;
        <$fh>
    };

    $dbh->do("BEGIN $sql END;");
}

This subroutine allows to run DDL (SQL) scripts with multiple statements inside (e.g. database dumps).

like image 143
Elvenfighter Avatar answered Nov 01 '22 12:11

Elvenfighter


Not exactly sure what you want...

Once you create a DBI object, you can use it over and over again. Here I'm reading SQL statement after SQL statement from a file and processing each and every one in order:

use DBI;

my $sqlFile = "/home/user1/tools/mytest.sql"

my $dbh = DBI::Connect->new($connect, $user, $password)
    or die("Can't access db");

# Open the file that contains the various SQL statements
# Assuming one SQL statement per line

open (SQL, "$sqlFile")
    or die("Can't open file $sqlFile for reading");

# Loop though the SQL file and execute each and every one.
while (my $sqlStatement = <SQL>) {
   $sth = dbi->prepare($sqlStatement)
      or die("Can't prepare $sqlStatement");

   $sth->execute()
      or die("Can't execute $sqlStatement");
}

Notice that I'm putting the SQL statement in the prepare and not the file name that contains the SQL statement. Could that be your problem?

like image 28
David W. Avatar answered Nov 01 '22 13:11

David W.


You don't need perl for this at all. Just use the mysql command line client:

mysql -h [hostname] -u[username] -p[password] [database name] < /home/user1/tools/mytest.sql

replace the [variables] with your information.

Note no space after -u or -p. If your mysql server is running on the same machine you can omit -h[hostname] (it defaults to localhost)

like image 32
Cfreak Avatar answered Nov 01 '22 13:11

Cfreak