[Phoenix-pm] Running a SQL program within PERL DBI
Scott Walters
scott at illogics.org
Wed Mar 15 18:10:06 PST 2006
Hi Bobby,
Brock would probably know... I don't get out much. It would probably be
folded in with another group, such as some Web group.
-scott
On 0, "Metz, Bobby W, WCS" <bwmetz at att.com> wrote:
> 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
> _______________________________________________
> 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