How to read batches of SQL from a file in Perl

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.

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]

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)

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.

Comment on How to read batches of SQL from a file in Perl Select or Download Code

Replies are listed 'Best First'.
Re: How to read batches of SQL from a file in Perl by mpeppler (Vicar) on Aug 29, 2007 at 11:25 UTC
You can also make the iterator a little simpler by changing the record separator: `local $/="\ngo\n"; while(<IN>) { chomp; # now the entire statement is in $_... ... execute the SQL statement, with error checking, etc. }` [download] The disadvantage is that $/ can't be a regex, so you can't handle upper/lower case, for example. Michael	[reply] [d/l]
Re^2: How to read batches of SQL from a file in Perl by KurtSchwind (Chaplain) on Oct 25, 2007 at 02:05 UTC
Wouldn't it be nicer and more flexible to allow people to specify the seperator? I like the idea of essentially slurping the files in and specifying the break as a NEWLINEgoNEWLINE as you did, but I think that a semi-colon is more common. I realize that the stated assumption was that the statements end with a 'go', but it would be such a minor change to make it so much more flexible.	[reply]
Re^3: How to read batches of SQL from a file in Perl by erix (Prior) on Oct 25, 2007 at 14:32 UTC
A similar module is SQL::Script, which seems like a good idea (and it has the separator :)	[reply]
Re^3: How to read batches of SQL from a file in Perl by mpeppler (Vicar) on Oct 26, 2007 at 14:02 UTC
The default for Sybase isql scripts is to end with "go", so it makes sense to do it this way. Other separators can of course be used - it all depends on what your purpose is. Michael	[reply]


Welcome to the Monastery
	PerlMonks