How to catch SQL::Parser errors in Perl

som.nitk has asked for the wisdom of the Perl Monks concerning the following question:

I have an update statement which I am parsing with SQL::Parser

uPdate scott.emp 
set ename='SCT%',emp_date=TO_DATE('04/16/2011 00:00:00', 'MM/DD/YYYY H
+H24:MI:SS'),empno='15645' 
WHERE dept=20 and ename IN(select ename from emp where empno='1111');
[download]

But since TO_DATE function cannot be parsed with SQL::Parser hence it throws error:

Incomplete SET clause! at ./post_audit.pl line 173
Incomplete SET clause! at ./post_audit.pl line 173
[download]

How do I catch such errors? Does eval do the trick? Did not find proper documentation for the same. Code to parse the SQL statements:

   +12  use SQL::Parser;

   +34  my $statement = "uPdate scott.emp set ename='SCT%',emp_date=TO
+_DATE('04/16/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),empno='15645' W
+HERE dept=20 and ename IN(select ename from emp where empno='1111')";

  +172      my $parser = SQL::Parser->new('AnyData', {RaiseError=>1} )
+;
  +173      $parser->parse($statement);
[download]

Error is thrown at line 173 while parsing the statement. Initially I thought the problem comes from here inside Parser.pm

sub SET_CLAUSE_LIST
{
    my ( $self, $set_string ) = @_;
    my @sets = split( /,/, $set_string );
    my ( @cols, @vals );
    for my $set (@sets)
    {
        my ( $col, $val ) = split( m/ = /, $set );
        return $self->do_err('Incomplete SET clause!') unless ( define
+d($col) && defined($val) );
        push( @cols, $col );
        push( @vals, $val );
    }
    return undef
      unless ( $self->{struct}->{column_defs} = $self->ROW_VALUE_LIST(
+ join ',', @cols ) );
    return undef unless ( $self->LITERAL_LIST( join ',', @vals ) );
    return 1;
}
[download]

But the problem is not with regex. The problem is with TO_DATE function only. My question is not to fix the given parsed SQL to the Parser, but how to catch such errors and probably print a statement. I could'nt find proper documentation for this.

Thanks!!

Comment on How to catch SQL::Parser errors in Perl Select or Download Code

Replies are listed 'Best First'.
Re: How to catch SQL::Parser errors in Perl by tobyink (Canon) on May 15, 2013 at 12:24 UTC
Untested, but peeking at the source code, it appears that SQL::Parser has two settings for error messages which can be set independently... `my $parser = SQL::Parser->new('AnyData', { PrintError => 0, # off RaiseError => 1, # on });` [download] If both are false, then when SQL::Parser hits an error it will plough on without notifying you (though you can call `$parser->errstr` to obtain the most recent error message. If `PrintError` is switched on (and it is on by default), then it will `warn` you when an error occurs. This can be caught using `$SIG{__WARN__}`. If `RaiseError` is switched on (but it is off by default), then it will `die` when an error occurs. This can be caught using `$SIG{__DIE__}` or `eval` or something like Try::Tiny. If both are switched on, it will `warn` and `die` when an error message occurs. This is why you're getting a double error message: `Incomplete SET clause! at ./post_audit.pl line 173 Incomplete SET clause! at ./post_audit.pl line 173` [download] This is also why your `eval` isn't appearing to work. It's catching the `die`, but allowing the `warn`to pass through, because although you've switched `RaiseError` on, you haven't switched `PrintError` off. `package Cow { use Moo; has name => (is => 'lazy', default => sub { 'Mooington' }) } say Cow->new->name`	[reply] [d/l] [select]
Re^2: How to catch SQL::Parser errors in Perl by som.nitk (Novice) on May 16, 2013 at 07:37 UTC
Bingo! Thanks !	[reply]
Re: How to catch SQL::Parser errors in Perl by LanX (Saint) on May 15, 2013 at 11:17 UTC
Either you catch errors with block `eval {}` or you locally manipulate the `%SIG` handlers. (click Search to see older threads=) See documentation in perlvar: %SIG and eval BLOCK Cheers Rolf ( addicted to the Perl Programming Language)	[reply] [d/l] [select]
Re^2: How to catch SQL::Parser errors in Perl by som.nitk (Novice) on May 15, 2013 at 11:44 UTC
Tried this but it still shows the Defaut msgs from Parser.pm : Incomplete SET clause! at ./post_fjs_audit.pl line 173 `+173 my $result = eval { $parser->parse($statement) }; +174 unless($result) { +175 print "Caught!\n"; +177 }` [download] `Incomplete SET clause! at ./post_fjs_audit.pl line 173 Caught!` [download] Any way to suppress these default msgs from Parser.pm ? One more problem is that the program exits on the caught exception. What if there are statements below to process. I dont want the program to exit.	[reply] [d/l] [select]

Back to Seekers of Perl Wisdom