Update: I fixed the code to actually use the iterator this time :)

Read the SQL text file and send each batch to the database using Perl. In this case, we aren’t performing any real parsing of the SQL itself, we are simply retrieving the individual SQL batches. I’m using Rintaro Ishizaki’s Iterator::Simple Perl module so we can very easily get the next SQL batch.

package dbS::Sybase::Parse::SQL_File;

use warnings;
use strict;

use Iterator::Simple qw(iterator);

BEGIN {
    use Exporter ();

    our ($VERSION, @ISA, @EXPORT, @EXPORT_OK, %EXPORT_TAGS);
    $VERSION = 1.0.0;

    @ISA = qw(Exporter);
    @EXPORT_OK = qw(&batch);
}

our $FH;

#############################

sub batch {
 my $file = shift;

 open ($FH, "<", $file)
   or die ("unable to open sql file\n");

 iterator {
   my $query = "";

   while (my $line = <$FH>) {
     chomp $line;
     last if ($line =~ m/^go\s*$/i);
     $query .= $line . " ";
   }

   return $query;
 }
}

1;
[download]

1. SQL batches end with a go (case insensitive)
2. SQL code is valid
3. security of the SQL text file is handled by the operating system (we're not going to worry about SQL injection attacks at this level)

use dbS::Sybase::Parse::SQL_File qw(open_file next_batch);

....

if ( my $batch = dbS::Sybase::Parse::SQL_File::batch("SQL/SNAP.sql") )
+ {
  print "-"x40 . "\n";
  print " Performing IGOR\n";
  print "-"x40 . "\n";

  while ( my $sql_batch = $batch->next ) {
     dbh_do($local_dbh, $sql_batch);
  }
}
[download]

Granted, we could have performed this without the iterator, but this is just the first revision. I expect to be adding a lot more to it (e.g. T-SQL verifier) so that I can hide the complexity behind the iterator.