[Phoenix-pm] Running a SQL program within PERL DBI

Metz, Bobby W, WCS bwmetz at att.com
Wed Mar 15 16:41:19 PST 2006


Scott,
	If you keep talking about dict's we'll have to rebadge the group
as Phoenix Python Mongers. ;-)

Silly joke aside, anyone know if such an entity exists in town?  I have
to wear both Perl and Python hats at work and Python is definitely my
weaker language.

Bobby


-----Original Message-----
From: phoenix-pm-bounces+bwmetz=att.com at pm.org
[mailto:phoenix-pm-bounces+bwmetz=att.com at pm.org]On Behalf Of Scott
Walters
Sent: Wednesday, March 15, 2006 5:24 PM
To: Loo, Peter # PHX
Cc: phoenix-pm at pm.org
Subject: Re: [Phoenix-pm] Running a SQL program within PERL DBI


Hi Peter,

If it's your last reply, I'm truly sorry.  Brock will yell at me if I
scare people away.  But on the flip side of the token, when you ask
get free advice, well, it's not really, it's at the expense of the
askee rather than the asker.  So, it's important to never treat free
advice as if its free.  Here's one we hear all the time in EFNET
#perlhelp... "If you're going to be like that, I'm going to use PHP!"
(except without the punctuation).  Yeah, that'll teach me a lesson.
Maybe 
I offended you, but you're going to offend people here if you take
advice, 
throw it away, ask for more, throw that away, repeat.  Don't beleive
what 
you're being told?  That's fine, but why would you come back and ask for

more?

Also, as I said, I yelled a little bit to get your attention; I didn't
yell at you to hurt your feelings.  I'm sorry if I miss-pegged your
experience.  It happens.  It's a theme I hear from novices a lot;
it doesn't mean you're a novice.  You also may not even have the
attitude towards "one size fits all routines" that I thought you had --
maybe I mispegged you twice.  You're a grown man.  You can handle it.

But I don't think this was about my using stronger language; I think it
was about me (possibily) mis-pegging you as a novice.  By the way, it's
Perl, not PERL.  People only think it's "PERL" because book titles
tend to be printed in all-caps.  If you actually open the book, though,
and it isn't one of the several that completely sucks, you'll see that
title case isn't used inside.

> Don't think for a second that you have done it all and have
experienced
> it all.

Heh, now you're accusing me of attitudes, but in a far less hypothetical
phrasing.  But that's okay.  

> I am quite disappointed by your attitude and certainly won't have too
> much good to spread about you.  If you were frustrated by the emails,

Wait, you've "been in the business for 15 years and make a six figure
salary" and you're going to tell people bad things about me?  Isn't
that a more gradeschool level tactic?  Everyone who knows me knows
I'm a dict.  The channel bots have a long list of nasty things novices
have said about me.  I like to show it to novices before they get a 
screenfull from me, but they're always still shocked to discover I'm
a dict, and act like they just discovered something.  Seriously.  I'm
a bad person.  I don't have any natural coding abilities.  I just 
made a bargain with Satan.  Let me know when you've come to terms with
this.

> why didn't you just delete them and let whoever else to respond.  You
> are rude, conceded and arrogant.

I could have told you that ;)  If you're going to hell, you might as 
well do it right.

And don't take me to represent the group.  There are a lot of very nice
people there.

Cheers,
-scott

On  0, "Loo, Peter # PHX" <Peter.Loo at source.wolterskluwer.com> wrote:
> 
> Scott,
> 
> This will be my last reply.
> 
> I may be novice in PERL, but I make six figures yearly so I can't be
too
> dumb to be talked down to like you have.  You are absolutely correct.
> It is NOT acceptable.  I have been in IT for 15+ years and have only
met
> a handful of people like yourself who think the only right way is your
> way and that you are dreaming of being GOD.  I am afraid you have
missed
> the boat there.  GOD DON'T WRITE PERL OR ANY OTHER LANGUAGE.  Nothing
in
> life is static.  Everything is different.  My way or no way is
certainly
> not the right approach.  Seen it all and know it all is even worse.
> Don't think for a second that you have done it all and have
experienced
> it all.
> 
> I am quite disappointed by your attitude and certainly won't have too
> much good to spread about you.  If you were frustrated by the emails,
> why didn't you just delete them and let whoever else to respond.  You
> are rude, conceded and arrogant.
>  
> Peter Loo
> Wolters Kluwer Health
> (602) 381-9553
> 
> -----Original Message-----
> From: Scott Walters [mailto:scott at illogics.org] 
> Sent: Wednesday, March 15, 2006 4:49 PM
> To: Loo, Peter # PHX
> Cc: phoenix-pm at pm.org
> Subject: Re: [Phoenix-pm] Running a SQL program within PERL DBI
> 
> Hi Peter,
> 
> I thought I was "the greatest".  You changed your mind in a hurry.
> 
> Sorry if I hurt your feelings.  From my point of view, several people
> were telling you things that were absolutely correct and helpful, but
> they weren't "hitting their mark", so to speak.  But it's often this
> way.  Novices ask for advice; they reject what they're given,
> mistakeningly thinking their case is special and they're just
> misunderstood and then repeat their question, over and over; people
> repeat their answers, correct in their assessment of the situation;
> everyone gets frustrated.  I just hurry the process along when I 
> see things starting to go in circles.   Sometimes the novice in
question
> suddenly "gets it"; sometimes pride gets in the way, and being yelled
at
> is considered unacceptable, even if it really is necessary.  Some will
> disagree as to its necessity; for them, I offer a few years on a Perl
> help channel on IRC >=)  We'll talk about it after then. 
> 
> Anyway, I'm not aggitated at you.  I just thought the answers needed
> some more emphasis.  I'm sorry that you read it that way.  It is true
> that I'm a grumpy old cuss.  If you don't post on any list with a
grupy
> old cuss, well, you're limiting yourself to not many lists ;)
> 
> -scott
> 
> 
> On  0, "Loo, Peter # PHX" <Peter.Loo at source.wolterskluwer.com> wrote:
> > 
> > Hi Scott,
> > 
> > Sorry that is I have gotten into your skin, however, I thought this 
> > was a group to discuss and share ideas without any limitation of the

> > level of PERL knowledge.  It appear to me that you just don't like 
> > anything that is not your way.  Don't worry about blocking me out of

> > this group as I don't plan to return.  Brock, Mike and the rest, I
am 
> > sincerely grateful for your inputs.
> > 
> > Best regards.
> >  
> > Peter Loo
> > Wolters Kluwer Health
> > (602) 381-9553
> > 
> > -----Original Message-----
> > From: Scott Walters [mailto:scott at illogics.org]
> > Sent: Wednesday, March 15, 2006 3:58 PM
> > To: Loo, Peter # PHX
> > Cc: Brock; phoenix-pm at pm.org
> > Subject: Re: [Phoenix-pm] Running a SQL program within PERL DBI
> > 
> > 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
> > 
> > 
> > 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.
> _______________________________________________
> Phoenix-pm mailing list
> Phoenix-pm at pm.org
> http://mail.pm.org/mailman/listinfo/phoenix-pm
_______________________________________________
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