Execute SQL file in Perl

Question

We have a Perl script that runs SQL and puts the data in a table. Now, instead of delivering a single SQL query, we want to pass a bunch of them by putting them in a .sql file. We know that our program will fail because it expects a single SQL statement, and not because of them (this is also from the .sql file). How do we work with a .sql file (with several INSERT statements?). We use the DBI package.

A small piece of code:

$sth = $dbh->prepare("/home/user1/tools/mytest.sql");
$sth->execute || warn "Couldn't execute statement";
$sth->finish();

Answer 1

Not quite sure what you want ...

After creating the DBI object, you can use it again and again. Here I read the SQL statement after the SQL statement from the file and process everything 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");
}

, SQL prepare, , SQL. ?

Answer 2

perl. mysql:

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

[] .

-u -p. mysql , -h [_] ( localhost)

Answer 3

DDL . SQL, BEGIN ... END;. :

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 routine allows you to run DDL (SQL) scripts with multiple statements inside (for example, database dumps).

Answer 4

Here is how I did it. In my case, I do not accept one SQL per line, and I assume that my example is slightly better :)

sub get_sql_from_file {
    open my $fh, '<', shift or die "Can't open SQL File for reading: $!";
    local $/;
    return <$fh>;
};

my $SQL = get_sql_from_file("SQL/file_which_holds_sql_statements.sql");
my $sth1 = $dbh1->prepare($SQL);
$sth1->execute();