Perl DBI - run SQL Script with multiple statements
Solution 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";
}
Solution 2
Please, have a look at this new CPAN module: DBIx::MultiStatementDo
It has been conceived precisely for that.
Solution 3
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.
Comments
-
guigui42 almost 2 years
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.