Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

validating SQL using DBI

by sflitman (Hermit)
on Jun 28, 2009 at 22:06 UTC ( [id://775507]=perlquestion: print w/replies, xml ) Need Help??

sflitman has asked for the wisdom of the Perl Monks concerning the following question:

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

Replies are listed 'Best First'.
Re: validating SQL using DBI
by ikegami (Patriarch) on Jun 28, 2009 at 22:10 UTC

    It works, but it prints the error twice, not sure why.

    Turn off PrintError

    Won't statements with placeholders (such as SELECT * FROM Table WHERE id = ?) result in a verdict of invalid? Your tool would tell me just about every statement I write is invalid. It discourages best practices.

    You shouldn't use a variable for two different purposes. Use a different variable other than $sth to hold the result. There's no reason $sth should even exist outside the eval.

      The tool will be used for validating static data definition statements, mostly CREATE TABLE statements. I did run into a problem with a file that was just a big COPY statement where it thought a time 17:30 was a :-placeholder, not sure how to handle that case.

      I thank you for the suggestion to set PrintError to 0, that of course did the trick! I updated the OP.

      SSF

Re: validating SQL using DBI
by Errto (Vicar) on Jun 29, 2009 at 13:48 UTC

    I don't know Postgres, but in databases I've used, DDL statements are indifferent to transactions and can't be rolled back. Not sure that will be an issue.

    But in general, using transactions to validate SQL is not a bad idea. I have an application that does something similar.

    If you're having data values interpreted as placeholders, it sounds like your SQL text may not actually be in a form that's valid for an individual statement. I don't know the COPY statement so I may be out of my league here.

      Yes, PostgreSQL does transactional DDL, see this page for an example. It is a handy feature but as you can see from that page, not widely implemented.

      It lets you try out changes (adding or dropping indexes, for instance) and evaluating their effects, before actually COMMITting them (or ROLLBACK, as the case may be). (Other sessions are not effected.)

      See also the -1 option of psql (the cli client):

              -1 ("one")      execute command file as a single transaction
      

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://775507]
Approved by ikegami
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others avoiding work at the Monastery: (6)
As of 2024-03-29 12:45 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found