Is there a better way to do this? My posted code is a command line program called sqlvalid which takes a file of SQL statements (data definition language) and runs it in a test database and inside BEGIN/ROLLBACK, reporting errors. This happens to run under PostgreSQL 8.4rc2

#!/usr/bin/perl
#
# sqlvalid:  validate SQL expressions
# Written by Stephen S. Flitman, M.D.
# requires a test database
######################################################################
+#

use strict 'vars';
use vars qw/$dbh $db %opts/;
use DBI;
use Getopt::Std;

$db='test';

sub error {
   my $text=shift;
   print STDERR "$app: $text\n";
   exit 1;
}

getopts('cd:h',\%opts);

if ($opts{h} or !@ARGV) {
   print <<"EOT";
Usage: sqlvalid [-chv] [-d database] [expr|file ...]
  Determine if files contain valid SQL; statements are never committed
  Switches:
    -c      Take expression from command line (joins spaces)
    -d DB   Alternative database than [$db]
    -h      This help
EOT
   exit 1;
}
$db=$opts{d} if $opts{d};
error "No database selected" unless $db;
$dbh=DBI->connect("dbi:Pg:dbname=$db","","");
unless ($dbh) { error "Couldn't select database $db"; }
$dbh->{RaiseError}=0;
$dbh->{PrintError}=0;

my $expr;
if ($opts{c}) {
   $expr=join(' ',@ARGV);
   print valid_sql($expr);
} else {
   local($/)=undef;
   for my $file (@ARGV) {
      if (open(FILE,$file)) {
         $expr=<FILE>;
         close FILE;
         print valid_sql($expr);
      } else {
         error "Cannot read $file: $!";
      }
   }
}
$dbh->disconnect();
exit;

sub valid_sql {
   my $expr=shift;
   my $ret;
   $dbh->begin_work();
   eval {
      my $sth=$dbh->prepare($expr);
      die $dbh->errstr."\n" if $sth->err;
      $sth->execute;
      die $dbh->errstr."\n" if $sth->err;
      $sth->finish;
   };
   if ($@) {
      $ret=$@;
   } else {
      $ret="SQL syntax OK\n";
   }
   $dbh->rollback();   # never commit
   $ret;
}
[download]

It works, but it prints the error twice, not sure why. As is typical, I expect I'm doing something silly which my fellow monks will be kind enough to point out to me!

Best regards,
SSF

Update: I added the call to set PrintError=0 which solves the double-report problem and used a separate var for the return value in valid_sql() per Ikegami's suggestion. Now it's a shweet tool, as Peter Griffin would say, if he was a Perl programmer and not just a character on Family Guy. - SSF

Update: I added the newlines to the die calls in the eval to suppress the program name and offending line showing up in the error message. - SSF