Perl DBI - run SQL Script with multiple statements

Question

I have a sql file test.sql used to run some SQL (create object / update / delete / insert) that can look like this

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

Usually, i would just use SQLPLUS (from within Perl) to execute this test.sql using this command : @test.sql

Is there a way to do the same thing, using DBI in Perl ? So far, i found DBI can only execute one statement at a time, and without the ";" at the end.

Answer 1

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

Answer 2

Oracle can run multiple SQL statements in one prepare using an anonymous PL/SQL block.

eg

$dbh->do("
    BEGIN
      UPDATE table_1 SET col_a = col_a -1;
      DELETE FROM table_2 where id in (select id from table_1 where col_a = 0);
    END;
");

DDL (creating or dropping objects) is more complicated, mostly because it is something you shouldn't be doing on an ad-hoc basis.

Answer 3

You may add yet another layer of logic in Perl which parses the SQL script, splits it into statements and execute it one by one using the technique above

   --sql file
   -- [statement1]
   SQLCODE...

   -- [statement2]
   SQLCODE...

#Gets queries from file.
sub sql_q {
     my ($self) = @_;
     return $self->{sql_q} if $self->{sql_q};
     my $file = $self->{sql_queries_file};

     $self->{sql_q} || do {
         -e $file || croak( 'Queries file ' . $file . ' can not be found.' );
         my $fh = IO::File->new("< $file");
         my @lines;
         ( $fh->binmode and @lines = $fh->getlines and $fh->close ) or croak $!;

         my ($key);
         foreach ( 0 .. @lines - 1 ) {
             next if ( $lines[$_] =~ /^;/ );
             if ( $lines[$_] =~ /^--\s*?\[(\w+)\]/ ) {
                 $key = $1;
             }
             $self->{sql_q}{$key} .= $lines[$_] if $key;
         }
     };
     return $self->{sql_q};
 }
 #then in your script
 #foreach statement something like
 $dbh->prepare($sql_obj->{sql_q}->{statement_name})->execute(@bindvars);

Answer 4

Please, have a look at this new CPAN module: DBIx::MultiStatementDo

It has been conceived precisely for that.