[Phoenix-pm] Running a SQL program within PERL DBI
Scott Walters
scott at illogics.org
Wed Mar 15 14:57:49 PST 2006
Hi Peter,
> My goal is to have a generic PERL sub_routine that I can pass one to
> many arguments (including the sql file name) and have it perform the SQL
> statement that is within the sql file dynamically so that we can reuse
> the same sub_routine for all select statement sql files. Does that make
> sense? I am all for reusing of code and not reinvent the wheel each
Stop repeating it this. If it doesn't make sense, then do something else.
If it does, then we get it already.
You have a choice: you can re-implement parts of the SQL shell in Perl,
or you can call the SQL shell.
You keep talking about reading the file and passing the SQL... you know
how to do both of these things already.
Funneling all SQL SELECT statements through one routine is silly.
Refactor your code however you like, but this artifical goal will only
hurt you. If you find you're writing a lot of similar routines, then
learn how to write closures. No, I don't want to talk about this or
debate it; no slight intended, but this kind of simplistic view of
"one routine to do all" is characteristic of novices. People who have
been doing this for a while know there's give and take. Routines
get split out, then made into object methods, and those objects get
configured with more objects they delegate to, then common logic
is re-grouped into a routine somewhere, and so on. Trying to do
all of any sort of thing in one places ignores the inherent complexity
in a program. That's like saying "I want to paint the world PINK!".
But, I said this isn't up for discussion. If I see it again, I'm
telling mutt to ignore this thread.
> time I have an need to run a select statement. I suppose I can read in
> the sql file like Michael Friedman had suggested earlier in the chain of
> emails. I was just hoping not to have to read in the sql statement
> contained in the sql file and assigning it to a variable before doing
> the $dbh->prepare.
You want to execute it in Perl and not read it in? I think you need to
focus on what you're really trying to accomplish and concentrate less
on how you do it, because you're starting to get silly. I can't imagine
the real problem actually suggests a reading/not-reading paradox.
> I need to do some brain storming. :) Not going to give up yet as I am
> dealing with one of my two favorite languages. :)
If you want to run it through Perl, read it in and DBI. If you want to run
it in the database shell, either do so directly or from a subprocess.
Either way, quit trying to do both-and-neither-at-the-same-time. I assure
you it will be entirely unproductive.
By the way, there's often no clear solution (at least the programmer
working on something), and in that case, don't fuss with it endlessly.
Do it one way and be done with it, and if a clear solution occurs to you
later, go back and change it.
-scott
>
> Peter Loo
> Wolters Kluwer Health
> (602) 381-9553
>
> -----Original Message-----
> From: Brock [mailto:awwaiid at thelackthereof.org]
> Sent: Wednesday, March 15, 2006 2:02 PM
> To: Loo, Peter # PHX
> Cc: Scott Walters; phoenix-pm at pm.org
> Subject: Re: [Phoenix-pm] Running a SQL program within PERL DBI
>
> Using DBI is completely different then piping things to sqlplus. I was
> just doing a direct translation of your example, not really trying to
> imply that it was a good idea.
>
> Perl DBI does NOT use sqlplus as the driver.
>
> Unfortunately your question doesn't make much sense... if your .sql has
> a select it could have many selects and it could have all sorts of
> things. The problem is that how can your program know what it contains?
> It seems what you need to do is put the select like queries directly
> into perl, as we demonstrated to you in earlier emails. Doing the whole
> fetchrow_arrayref thing that you were already doing.
>
> Make sense?
>
> --Brock
>
> On 2006.03.15.13.55, Loo, Peter # PHX wrote:
> |
> | Hi Brock,
> |
> | Is PERL DBI using "sqlplus" within Oracle driver? If so, it can't be
> | efficient. Secondly, what if you have a "SELECT" statement in the
> | .sql program and if you want to loop through each row?
> |
> | Peter Loo
> | Wolters Kluwer Health
> | (602) 381-9553
> |
> | -----Original Message-----
> | From: Brock [mailto:awwaiid at thelackthereof.org]
> | Sent: Wednesday, March 15, 2006 1:52 PM
> | To: Loo, Peter # PHX
> | Cc: Scott Walters; phoenix-pm at pm.org
> | Subject: Re: [Phoenix-pm] Running a SQL program within PERL DBI
> |
> | Sure... what you are doing here is opening an external program and
> | piping it data on STDIN. There are several ways to do this in Perl...
> |
> | Here's one (a rough guess):
> |
> | # Initiate those vars (pull them from %ENV?)
> | my $dbuser = ...;
> | my $dbpass = ...;
> | my $dbconn = ...;
> | my $mailProgFile = ...;
> |
> | open my $sqlplus, "|-", "sqlplus -s /NOLOG" or die "ERROR: $!\n";
> | print $sqlplus <<" HERE";
> | connect ${dbuser}/${dbpass}@${dbconn}
> | @${mailProgFile}.sql
> | HERE
> |
> | More or less. Eh?
> |
> | --Brock
> |
> | On 2006.03.15.13.19, Loo, Peter # PHX wrote:
> | |
> | | Hi Scott,
> | |
> | | So will it be correct to assume that PERL DBI can not execute an SQL
>
> | | program? For example, I can do this with Korn shell:
> | |
> | | sqlplus -s /NOLOG << EOF
> | | connect ${DBUSER}/${DBPASS}@${DBCONN}
> | | @${MailProgFile}.sql
> | |
> | | Is this not possible in PERL DBI?
> | |
> | | Peter Loo
> | | Wolters Kluwer Health
> | | (602) 381-9553
> | |
> | | -----Original Message-----
> | | From: Scott Walters [mailto:scott at illogics.org]
> | | Sent: Tuesday, March 14, 2006 9:36 PM
> | | To: Loo, Peter # PHX
> | | Cc: Michael Friedman; phoenix-pm at pm.org
> | | Subject: Re: [Phoenix-pm] Running a SQL program within PERL DBI
> | |
> | | Consider using GetOpt::Std, and most of the time you want this form
> | | of
> | | for:
> | |
> | | for my $thing (@things) { ... stuff with $thing ... }
> | |
> | | You can always try to read rows and trap errors.
> | |
> | | -scott
> | |
> | | On 0, "Loo, Peter # PHX" <Peter.Loo at source.wolterskluwer.com>
> wrote:
> | | >
> | | > 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.
> | |
> | |
> | | 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.
> | | _______________________________________________
> | | 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.
>
>
> 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.
> _______________________________________________
> Phoenix-pm mailing list
> Phoenix-pm at pm.org
> http://mail.pm.org/mailman/listinfo/phoenix-pm
More information about the Phoenix-pm
mailing list