DBI::Statement parsing

Heya DBI-Users

Can anyone help a DBI::Statement newbie with the following problems:

1. When parsing a SQL string, is there a preferred way for initially testing
whether the statement did or did not parse successfully?

I am currently testing the $stmt->{original_string} element in the return
hash from creating the statement object; this element seems to only be
instantiated when the parsing succeeds. Would this be considered a valid way
to test?

Personally, I would prefer a method that could return a (documented) success
indication (like $stmt->isOK() == TRUE, or summit ...), but this seems
somewhat esoteric; the doco for the return hash is rather light-on, however,
causing quite a bit of guesswork on part of the developer.

2. The parsing seems to be very weak? I tried the ANSI dialect, and the
parser seemed to accept just about any string without raising an error. I am
having more success with the Anydata dialect, but even this seems to let a
lot of things slip through ...

For instance:

"SELECT c1, c2 FROM x WHERE" seems to parse successfully, no error raised
for the empty WHERE-clause ... and things like that.

"SELECT *, junk FROM x" parses successfully!?

Is this anyone else's experience also?

Code frag below sig.

Cheers and ta if you can help,

John.
_______
+------------------ ////////------+----------------------------+
| John V Cougar __/ / | Voice: +61 2 6208 1683 |
| System Engineer / / /\ DBA +----------------------------+
| Telstra Internet \/_/ \ Devel | E-Mail: cougar@telstra.net |
+-------------------\____/ -------+----------------------------+


use SQL::Statement;
use strict;
use Data::Dumper;

my $parser = SQL::Parser->new('AnyData', {RaiseError=>0, PrintError=>0});
my $sql = "SELECT *, junk FROM x";
my $stmt = SQL::Statement->new( $sql, $parser );

if( defined $stmt->{original_string} ) {
print "Success: [$sql] looks like an SQL String\n";
} else {
print "ERROR: [$stmt->{errstr}]\n";
}

print Dumper( $stmt );

Re: DBI::Statement parsing

John Cougar wrote:

>Heya DBI-Users
>
>Can anyone help a DBI::Statement newbie with the following problems:
>
>
Not that it matters, but the module is called SQL::Statement, not
DBI::Statement.

>1. When parsing a SQL string, is there a preferred way for initially testing
>whether the statement did or did not parse successfully?
>
>
The errstr() method returns true if there was a parsing error, so this
is how to manually trap errors:

my $stmt = SQL::Statement->new($sql);
die if $stmt->errstr;

You can also use the RaiseError and PrintError flags just as in DBI. The
default behavior, like DBI is for RaiseError=0 and PrintError=1. In
other words, it will not automatically die on error. If you want to
automatically die on error, do this instead:

my $parser = SQL::Parser->new('AnyData',{RaiseError=>1,PrintError=>0});
for my $sql(@sqls) {
my $stmt = SQL::Statement->new($sql,$parser);
# will die here automatically if there's a parsing error
}

>I am currently testing the $stmt->{original_string} element in the return
>hash from creating the statement object; this element seems to only be
>instantiated when the parsing succeeds. Would this be considered a valid way
>to test?
>
>
No, use the errstr() method.

>the doco for the return hash is rather light-on, however,
>causing quite a bit of guesswork on part of the developer.
>
>
Documentation patches gratefully accepted :-). Just email them directly
to me or send them to rt.cpan.org. But don't use the return hash, use
the SQL::Statement methods wherever possible.

>2. The parsing seems to be very weak?
>
The main purpose of the module is to support DBD::CSV and the eight
other DBDs that depend on it. It attempts to also serve as a general SQL
parser, but is admittedly weak in some areas (though not in the areas
you seem to be having problems with now).

>seems to let a
>lot of things slip through ...
>
>For instance:
>
>"SELECT c1, c2 FROM x WHERE" seems to parse successfully, no error raised
>for the empty WHERE-clause ... and things like that.
>
>
With RaiseError set to 1 or with manually trapping using
$stmt->errstr(), that causes an error for me.

>"SELECT *, junk FROM x" parses successfully!?
>
>
Why shouldn't it parse successfully? It's a valid SQL statement, AFAIK.

--
Jeff

RE: SQL::Statement parsing

Hiya Jeff

Thanks for the reply.

Yep and whoops, that should have read SQL::Statement.

--

I was Dumper-ing the returned $stmt from the SQL::Statement instantiation,
so got mislead by the fact that the errstr hash element appeared to remain
populated across invocations, but I did as you said, and I concur that it
appears to work fine. I got confused, it's not the first time.

IMHO, the API would be neater is it contained "parse_ok()" method - or
something similar - to provide an explicit test for successful parsing,
without having to enquire against errstr() as a (almost) side-effect.

--

WRT the "SELECT *, junk FROM x" parses successfully? question, it appears I
have been mislead for years ... I have seen uses for combining wildcards
with explicit field definitions in SELECT lists many times in the past, but
almost always on Oracle platforms.

So I re-checked my facts, and indeed, in documented definitions and railroad
diagrams, SQL parse trees do appear to allow wildcards in combo with
explicit field references. I was mislead by Oracle's brain-dead parser
always disallowing such statements (and here's me thinking that Oracle MUST
be one of the leading-edge implementations of ANSI/Standardised SQL syntax
.... appears I was mistaken - again - DOH!) ... witness:

SQL> select *, col1 from generated_router;
select *, col1 from generated_router
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

So much for completeness. I couldn't get past this, so I checked
DBI/DBD::Oracle to see if it had the same weirdness: Yep! Same ORA-00923
error, so it must be an Oracle peculiarity.

--

On the other hand, why do you (as, indeed, does PostgreSQL) enforce the use
of the 'AS' keyword in column aliasing? Just a question, AS is optional in
SQL'92.

--

The doco issue was NOT a criticism, only an observation, and I will
contributed patches as requested ... chat to you offline about this, dude!

Thanks heaps for your efforts, Jeff. Keep up the great work.

Regards,

John.

PS> On the Structure.pod, it says users should instantiate a parser and pass
the instance variable into the Statement constructor as an argument. In your
reply, you just created Statement->new($sql). I know that this works, but
what is the default dialect?

I can tell that it's not ANSI ;-) ... happy to expand on this if needs be!
_______
+------------------ ////////------+----------------------------+
| John V Cougar __/ / | Voice: +61 2 6208 1683 |
| System Engineer / / /\ DBA +----------------------------+
| Telstra Internet \/_/ \ Devel | E-Mail: cougar@telstra.net |
+-------------------\____/ -------+----------------------------+

Re: SQL::Statement parsing

John Cougar wrote:

>IMHO, the API would be neater is it contained "parse_ok()" method - or
>something similar - to provide an explicit test for successful parsing,
>without having to enquire against errstr() as a (almost) side-effect.
>
>
I think the best thing is for me to follow DBI and have an err() method
which returns true if an error has occurred, e.g.

my_error_handler( $stmt->errstr ) if $stmt->err;

In fact, I should probably allow users to pass an error-handling coderef
to the parser.

>On the other hand, why do you (as, indeed, does PostgreSQL) enforce the use
>of the 'AS' keyword in column aliasing? Just a question, AS is optional in
>SQL'92.
>
>
Hmm, darn, I'm inconsistent - the AS is optional on table aliases but
required on column aliases. I'll add that to the to-do list, thanks.

>I will
>contributed patches as requested ... chat to you offline about this, dude!
>
>
Thanks much!

>PS> On the Structure.pod, it says users should instantiate a parser and pass
>the instance variable into the Statement constructor as an argument. In your
>reply, you just created Statement->new($sql). I know that this works, but
>what is the default dialect?
>
>
AnyData.

--
Jeff