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

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.