How to read batches of SQL from a file in Perl

Update: I fixed the code to actually use the iterator as an iterator 🙂

Scenario: You have a Perl application that performs lots of stuff but you are handed a SQL text file that you need to run on a regular basis from within your application.

Solution #1: Call isql from within your Perl application to run the SQL text file. This makes your code dependent on an external program.

`$SYBASE/$SYBASE_OCS/bin/isql -U $login -P $pass -S $DBMS -i $SQL_FILE`;

Solution #2: 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.

Our Perl module:

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(&get_batch);
}

our $FH;

#############################
sub _open_file {
    my $file = shift;

    my $FH;

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

    return $FH;
}

#############################
sub get_batch {
   my ($file, $delimiter) = @_;

   # The delimiter is a single or multiple characters
   #  that are on a line by itself.
   $delimiter = 'go' unless $delimiter;

   if ( my $FH = _open_file($file) ) {
     # Why use an iterator?  The SQL file may be huge
     #  so we want to retrieve only a single batch at
     #  a time.
      iterator {
         if (fileno $FH) {
           # Since this is an iterator, we need to
           #  verify that the $FH filehandle is valid.
           # fileno() will return a file descriptor
           #  if the $FH file handle is valid else
           #  it will return undef.
           my $query = "";

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

           return $query;
        }
    }
  }
}

1;

Obtaining the individual batches are now very easy. Note, that we are making several assumptions:

  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(get_batch);

....

if ( my $batch = dbS::Sybase::Parse::SQL_File::get_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);
        }
}

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.

Share Button

Leave a Reply

Your email address will not be published. Required fields are marked *