Execute SQL File in Perl

Execute SQL file in Perl

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?

How to load the .sql file from perl script and execute the sql commands in the perl script

If I read your problem correctly, your biggest challenge is breaking apart the file into separate sql statements, SQL::SplitStatement is usefull but beware it's not bullet proof.

Here's an example of how you could accomplish this;

#!/usr/bin/env perl
use strict;
use warnings;
use diagnostics;

use DBI;
use File::Slurp;
use SQL::SplitStatement;

my $sql_blob = read_file( 'test1.sql' ) ;
my @sql_list = SQL::SplitStatement->new()->split($sql_blob);

my $dbh = DBI->connect( "dbi:mysql:my_database:localhost:3306", "username", "password" );
foreach my $sql (@sql_list) {
  print 'Executing ', $sql;
  $dbh->do($sql) or print "Can't do ", $dbh->errstr;
}

How to execute a Pl sql file from a perl script

Basically you need to

• use the DBI module with the appropriate driver (Oracle or whatever)
• slurp in the script into a variable by using plain perl
• open a DB connection
• prepare the slurped in script
• execute the statement handle
• disconnect from the DB

Here is an example (I am not showing how to slurp in the script):

use DBI;
use DBD::Oracle;

my $service="xxx";
my $user = "yyy";
my $pass = "zzz";

my $DBH = DBI->connect
  (
   "dbi:Oracle:$service", 
   "$user", "$pass",
   { 
    RaiseError => 0, 
    PrintError => 0, 
    AutoCommit => 0, 
    ShowErrorStatement  => 0
   }
  ) or die;

my $script = qq(
    declare
        x number := 1;
    begin
        insert into xxx values (x);
        commit;
    end;
);

my $sth = $DBH->prepare($script) or die;
$sth->execute() or die;

$DBH->disconnect();

How to run and link a .sql file from Perl

In Perl you use the DBI database interface. In your case, you will also be using something like the DBD::MySQL driver.

There is lots of help available on this topic (including lots of questions on this site).

As for the specific question of your .sql file, there are a few approaches you could take, depending on how fancy you want to get:

• You could just copy and paste the commands into your program as you write it.
• You could execute an external program that will run the .sql file (for example, by using system()).
• You could programmatically read in the .sql file and send the commands from within your program. A module could help you with this (I found SQL::Script on CPAN, which looks useful, though I don't have any experience with it).

I suggest you pick an approach, try it, and ask if you have any specific problems.

Perl DBI - run SQL Script with multiple statements

The database controls how many statements can be executed at a time. I can't remember if Oracle allows multiple statements per prepare or not (MySQL does). Try this:

my $dbh = DBI->connect(
    "dbi:Oracle:dbname",
    "username",
    "password",
    {
        ChopBlanks       => 1,
        AutoCommit       => 1,
        RaiseError       => 1,
        PrintError       => 1,
        FetchHashKeyName => 'NAME_lc',
    }
);
$dbh->do("
    CREATE TABLE test_dbi1 (
        test_dbi_intr_no    NUMBER(15),
        test_dbi_name       VARCHAR2(100)
    );

    UPDATE mytable 
        SET col1=1;

    CREATE TABLE test_dbi2 (
        test_dbi_intr_no    NUMBER(15),
        test_dbi_name       VARCHAR2(100)
    );
");

$dbh->disconnect;

Of course, you get better error handling if you break the statements up. You can use a simple parser to break the string up into individual statements:

#!/usr/bin/perl

use strict;
use warnings;

my $sql = "
    CREATE TABLE test_dbi1 (
        test_dbi_intr_no    NUMBER(15),
        test_dbi_name       VARCHAR2(100)
    );

    UPDATE mytable
        SET col1=';yes;'
        WHERE col2=1;

    UPDATE mytable
        SET col1='Don\\'t use ;s and \\'s together, it is a pain'
        WHERE col2=1;

    CREATE TABLE test_dbi2 (
        test_dbi_intr_no    NUMBER(15),
        test_dbi_name       VARCHAR2(100)
    );
";

my @statements = ("");
#split the string into interesting pieces (i.e. tokens):
#   ' delimits strings
#   \ pass on the next character if inside a string
#   ; delimits statements unless it is in a string
#   and anything else
# NOTE: the grep { ord } is to get rid of the nul
# characters the split seems to be adding
my @tokens     = grep { ord } split /([\\';])/, $sql; 
# NOTE: this ' fixes the stupid SO syntax highlighter
#this is true if we are in a string and should ignore ; 
my $in_string  = 0;
my $escape     = 0;
#while there are still tokens to process
while (@tokens) {
    #grab the next token
    my $token = shift @tokens;
    #if we are in a string
    if ($in_string) {
        #add the token to the last statement
        $statements[-1] .= $token;
        #setup the escape if the token is \
        if ($token eq "\\") {
                $escape = 1;
                next;
        }
        #turn off $in_string if the token is ' and it isn't escaped
        $in_string = 0 if not $escape and $token eq "'";
        $escape = 0; #turn off escape if it was on
        #loop again to get the next token

        next;
    }
    #if the token is ; and we aren't in a string
    if ($token eq ';') {
        #create a new statement
        push @statements, "";
        #loop again to get the next token
        next;
    }
    #add the token to the last statement
    $statements[-1] .= $token;
    #if the token is ' then turn on $in_string
    $in_string = 1 if $token eq "'";
}
#only keep statements that are not blank
@statements = grep { /\S/ } @statements;

for my $i (0 .. $#statements) {
    print "statement $i:\n$statements[$i]\n\n";
}

How to pass parameters from Perl script to SQL script and execute it from Perl script?

I used $sth->fetch instead of $sth->fetchrow_array after execution of .sql as below.

  $sth = $dbh->prepare( "select * from alleMagic;" );
  $sth->execute;
    
   # Column binding is the most efficient way to fetch data
   my $rv = $sth->bind_columns(\$lname, \$fname, \$ext );
   while ($sth->fetch) {
       print "$lname, $fname, $ext \n";

   }

---

Related Topics