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

[Michael Friedman]

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>
---------------------------------------------------------------------