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

[Scott Walters]

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