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;
}
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