[Phoenix-pm] Running a SQL program within PERL DBI
Loo, Peter # PHX
Peter.Loo at source.wolterskluwer.com
Tue Mar 14 15:08:28 PST 2006
Thanks Michael and Scott. What I am trying to do is creating a generic
PERL program that will take in multiple arguments such as:
for (my $cnt = -1; $cnt < $#ARGV; $cnt++) {
my ($flag, $value) = split(/=/, $ARGV[$cnt]);
switch ($flag) {
case "-dd" { $d_dbName = lc($value) }
case "-dt" { $d_tblName = lc($value) }
case "-ds" { $d_SQL = $value }
case "-sd" { $s_dbName = lc($value) }
case "-st" { $s_tblName = lc($value) }
case "-ss" { $s_SQL = $value }
case "-cp" { $commitPoint = lc($value) }
case "-sf" { $s_funcToPerf = lc($value) }
case "-df" { $d_funcToPerf = lc($value) }
case "-d1" { $s_dbDriver = lc($value) }
case "-d2" { $d_dbDriver = lc($value) }
else { print "Unknown flag: $flag\n" }
}
}
Then execute accordingly, however, I would like to execute an external
SQL program that is passed to this generic program. In the case of an
external SQL program that does SELECT instead of UPDATE or INSERT, I
want to loop through the returned rows. Here is my first block of "if"
statement.
if ($s_funcToPerf eq "update") {
if (!$s_dbName || !$s_dbDriver || !$s_tblName || !$commitPoint ||
!$s_SQL) {
print "ERROR: Not enough arguments. Require arguments are:\n";
print "Example: \n";
print " Database Name: -sd=dv26\n";
print " Database Driver: -d1=Oracle\n";
print " Table Name: -st=p_falcon_projections\n";
print " SQL Statement: -ss=/usr/local/sql/ppv_update.sql\n";
print " Commit Point: -cp=5000\n";
exit(666);
}
else {
print "Calling sub_update()\n";
}
}
elsif ($s_funcToPerf eq "insert") {
if (!$s_dbName || !$s_dbDriver || !$s_tblName || !$commitPoint ||
!$s_SQL) {
print "ERROR: Not enough arguments. Require arguments are:\n";
print "Example: \n";
print " Database Name: -sd=dv26\n";
print " Database Driver: -d1=Oracle\n";
print " Table Name: -st=p_falcon_projections\n";
print " SQL Statement: -ss=/usr/local/sql/ppv_insert.sql\n";
print " Commit Point: -cp=5000\n";
exit(666);
}
else {
print "Calling sub_insert()\n";
}
}
elsif ($d_funcToPerf eq "update") {
if (!$d_dbName || !$d_dbDriver || !$d_tblName ||
!$s_dbName || !$s_dbDriver || !$s_tblName ||
!$s_funcToPerf || !$commitPoint || !$s_SQL || !$d_SQL) {
print "ERROR: Not enough arguments. Require arguments are:\n";
print "Example:\n";
print " Destination Database Name: -dd=pv26\n";
print " Destination Database Driver: -d2=ODBC\n";
print " Destination Table Name: -dt=p_falcon_projections\n";
print " Source Database Name: -sd=dv26\n";
print " Source Database Driver: -d1=Oracle\n";
print " Source Table Name: -st=p_falcon_projections\n";
print " SQL Statement:
-ss=/usr/local/sql/ppv_select.sql\n";
print " SQL Statement:
-ds=/usr/local/sql/ppv_update.sql\n";
print " Source Function to Perform: -sf=select\n";
print " Commit Point: -cp=5000\n";
exit(666);
}
else {
print "Calling sub_select()\n";
print "Calling sub_update()\n";
}
}
elsif ($d_funcToPerf eq "insert") {
if (!$d_dbName || !$d_dbDriver || !$d_tblName ||
!$s_dbName || !$s_dbDriver || !$s_tblName ||
!$s_funcToPerf || !$commitPoint || !$s_SQL || !$d_SQL) {
print "ERROR: Not enough arguments. Require arguments are:\n";
print "Example:\n";
print " Destination Database Name: -dd=pv26\n";
print " Destination Database Driver: -d2=ODBC\n";
print " Destination Table Name: -dt=p_falcon_projections\n";
print " Source Database Name: -sd=dv26\n";
print " Source Database Driver: -d1=Oracle\n";
print " Source Table Name: -st=p_falcon_projections\n";
print " SQL Statement:
-ss=/usr/local/sql/ppv_select.sql\n";
print " SQL Statement:
-ds=/usr/local/sql/ppv_insert.sql\n";
print " Source Function to Perform: -sf=select\n";
print " Commit Point: -cp=5000\n";
exit(666);
}
else {
print "Calling sub_select()\n";
print "Calling sub_insert()\n";
}
}
elsif ($s_funcToPerf eq "select") {
if (!$s_dbName || !$s_dbDriver || !$s_tblName || !$commitPoint ||
!$d_dbName || !$d_dbDriver || !$d_tblName || !$d_funcToPerf ||
!$s_SQL || !$d_SQL) {
print "ERROR: Not enough arguments. Require arguments are:\n";
print "Example:\n";
print " Destination Database Name: -dd=pv26\n";
print " Destination Database Driver: -d2=ODBC\n";
print " Destination Table Name: -dt=p_falcon_projections\n";
print " Source Database Name: -sd=dv26\n";
print " Source Database Driver: -d1=Oracle\n";
print " Source Table Name: -st=p_falcon_projections\n";
print " SQL Statement:
-ss=/usr/local/sql/ppv_select.sql\n";
print " SQL Statement:
-ds=/usr/local/sql/ppv_insert.sql\n";
print " Source Function to Perform: -sf=select\n";
print " Commit Point: -cp=5000\n";
exit(666);
}
else {
print "Calling sub_select()\n";
print "Calling sub_insert()\n";
}
}
else {
print "ERROR: Unknown value for database action to perform.\n";
exit(666);
}
Peter Loo
Wolters Kluwer Health
(602) 381-9553
-----Original Message-----
From: Scott Walters [mailto:scott at illogics.org]
Sent: Tuesday, March 14, 2006 3:28 PM
To: Michael Friedman
Cc: Loo, Peter # PHX; phoenix-pm at pm.org
Subject: Re: [Phoenix-pm] Running a SQL program within PERL DBI
Hi Peter,
Surely you're trying to accomplish more than just running the SQL or you
would just read it in an feed it to DBI. There's no reason you couldn't
call to the database command shell:
if(my $pid = fork) {
waitpid $pid;
} else {
close STDIN;
open STDIN, '<', 'foo.sql' or die $!;
exec 'mysql', $dbname or die $!;
}
... or something like that. If you want to use special features of the
database command shell or just cash in on its speed, this might be
handy. Of course, you don't want to try to read values from the
database back into Perl over a pipe between two processes... that's just
nasty.
-scott
On 0, Michael Friedman <friedman at highwire.stanford.edu> wrote:
> Peter,
>
> What I did in that situation was write a couple of methods to read in
> the file, put it into an array, and then loop through the array and
> make each call. The good news is you only have to write that once and
> then you can reuse it...
>
> My only example is using Sybase::DBlib, though, not DBI, but the logic
> would be the same. Sybase uses 'go' on a line by itself to end a SQL
> command, so we just use that to split up the lines in the file into
> commands into the array.
>
> You could make this a lot fancier, if you had the need, but it works
> for me.
>
> Good luck,
> -- Mike
>
> (sub db_run_script and sub db_run_command_list, below)
>
> sub db_run_script #($$)
> {
> my $dbh = shift;
> my $script = shift;
> my $saveresults = shift;
>
> open (SQL_SCRIPT, $script) || die "Could not open input file
$script:
> $!";
>
> my @commands = ();
> my $j = 0;
> my ($line);
>
> # read script file into a variable (array of commands)
> while ($line = <SQL_SCRIPT>)
> {
> if ($line =~ /^go/)
> {
> # make new command
> $j++;
> }
> elsif ($line =~ /^\s*$/)
> {
> # ignore blank lines
> }
> else
> {
> $commands[$j] .= $line;
> }
> }
> close SQL_SCRIPT;
>
> return db_run_command_list($dbh, \@commands, $saveresults); }
>
> sub db_run_command_list
> {
> my $dbh = shift;
> my $cmdlist = shift;
> my $saveresults = shift;
>
> my @resultlist;
>
> # run commands from array
> for $j(0..$#$cmdlist)
> {
> $dbh->dbcmd($cmdlist->[$j]);
> my $status;
> eval {
> $status = $dbh->dbsqlexec();
> };
>
> if ($@ || $status != SUCCEED)
> {
> # don't always die, because drop will fail
sometimes
> if ($cmdlist->[$j] =~ /drop/i)
> {
> warn "$cmdlist->[$j] failed.\n This is
OK - item probably didn't
> exist before installation.\n";
>
> $dbh->dbcancel(); # so that we can move
on to the next command?
> }
> else
> {
> die "+++ Could not run command
$cmdlist->[$j]\nbecause of this
> problem:\n$@";
> }
> }
>
> if (!$saveresults) {
> db_ignore_results($dbh);
> } else {
> # Count the total number of rows that were updated,
> # and capture the output of any SELECT statements
> #
> # Each update/insert statement will have its own
update
> # count (a separate call to DBCOUNT()) but we will
> # just add them all together
> my $totalupdatecount = 0;
> while ($dbh->dbresults() != NO_MORE_RESULTS) {
> my $rcount = $dbh->DBCOUNT();
> if ($rcount != -1) {
> $totalupdatecount += $rcount;
> }
>
> my @res;
> while (@res = $dbh->dbnextrow()) {
> my @copyres = @res; # make a copy of the
array
> push @resultlist, \@copyres;
> }
> }
>
> push @resultlist, $totalupdatecount;
> }
> }
>
> if ($saveresults) {
> return \@resultlist;
> } else {
> return;
> }
> }
>
>
> On Mar 14, 2006, at 1:47 PM, Loo, Peter # PHX wrote:
>
> > Hi,
> >
> > I know that you are able to issue a SQL statement within PERL DBI,
> > but is there anyway that I can issue an external SQL program? For
> > example, I have a SQL program called ppv_insert.sql that I would
> > like to execute within PERL DBI.
> >
> > Thanks in advance.
> >
> > Peter Loo
> >
> >
> >
> >
> > This E-mail message is for the sole use of the intended recipient
> > (s) and may contain confidential and privileged information. Any
> > unauthorized review, use, disclosure or distribution is prohibited.
> > If you are not the intended recipient, please contact the sender by
> > reply E-mail, and destroy all copies of the original message.
> > _______________________________________________
> > Phoenix-pm mailing list
> > Phoenix-pm at pm.org
> > http://mail.pm.org/mailman/listinfo/phoenix-pm
>
> ---------------------------------------------------------------------
> Michael Friedman HighWire Press
> Phone: 650-725-1974 Stanford University
> FAX: 270-721-8034 <friedman at highwire.stanford.edu>
> ---------------------------------------------------------------------
>
>
> _______________________________________________
> Phoenix-pm mailing list
> Phoenix-pm at pm.org
> http://mail.pm.org/mailman/listinfo/phoenix-pm
This E-mail message is for the sole use of the intended recipient(s) and
may contain confidential and privileged information. Any unauthorized
review, use, disclosure or distribution is prohibited. If you are not
the intended recipient, please contact the sender by reply E-mail, and
destroy all copies of the original message.
This E-mail message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply E-mail, and destroy all copies of the original message.
More information about the Phoenix-pm
mailing list