How to read batches of SQL from a file in Perl
ASE, DBI, Databases, How To, Perl, Sybase Add commentsUpdate: 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.
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:
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:
- SQL batches end with a go (case insensitive)
- SQL code is valid
- 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)
….
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.
August 29th, 2007 at 4:33 pm
[...] A benefit of handling each batch separate entity instead of all the batches as a whole in isql, we have the ability to isolate each batch. That means, the we can place logic around each batch or run them in parallel or a number of other reasons. This doesn’t actually require Perl and you could definitely do this in Java, but you would probably want to shy away from using isql for executing each batch. You may have hundreds or thousands of batches and starting isql for each batch would be very time consuming. [...]
August 30th, 2007 at 1:11 pm
[...] The following is the output of $SYBASE/$SYBASE_ASE/scripts/installmontables using our little application. Note that we are reading the batches one by one (just because we can) even though it isn’t necessary for this example. Also notice that the parsing of the installmontables SQL file isn’t perfect as the author of the SQL Template says that this is still a work in progress. It’s good enough for what I need. [...]
September 20th, 2007 at 3:08 pm
[...] How to read batches of SQL from a file in Perl, we learned how to read a SQL file and extract individual batches and created the Perl module [...]