Static Data, SQL::Parser

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

I've run in to an issue using SQL::Parser that I'm not sure how to work around. The following code throws an error despite this being valid SQL.

$parser = SQL::Parser->new( 'ANSI', {RaiseError=>0,PrintError=>1} )
$stmt = SQL::Statement->new( 'SELECT 1 AS A FROM DUAL', $parser )
[download]

The result is:
SQL ERROR: Bad table or column name '1' starts with non-alphabetic character!

I'm currently using the most up-to-date versions of SQL::Parser (1.13) and SQL::Statement (1.15).

Any thoughts as to how I can get around this error? Is it possible to identify these types of statements as valid to the parser? Do I need to filter out all static columns from my query?

Thanks in advance!

Comment on Static Data, SQL::Parser Select or Download Code

Replies are listed 'Best First'.
Re: Static Data, SQL::Parser by renodino (Curate) on May 07, 2007 at 23:02 UTC
It appears SQL::Statement has a bug, as `my $parser = SQL::Parser->new( 'ANSI', {RaiseError=>0,PrintError=>1} ) or die "No parser\n"; my $stmt = SQL::Statement->new( "SELECT 1 + 0 AS A FROM DUAL", $parser + ) or die "No statement";` [download] does not produce the error. String literals also seem to be a problem. There's an rt.cpan ticket thats kinda similar, but you might want to open a more specific one. BTW: Keep in mind that 'ANSI' dialect ne 'Oracle' dialect; whatever storage engine you provide will have to supply its own interpretation of "DUAL". Perl Contrarian & SQL fanboy	[reply] [d/l]
Re^2: Static Data, SQL::Parser by CLovegren (Novice) on May 07, 2007 at 23:19 UTC
A little more background; the project I'm working on is classifying specific chunks of data (database, table, columns, etc.) from queries used in our ColdFusion environment. I chose ANSI just because it should cover all of our different JDBC connections (Oracle 9i, DB2, SyBase, TeraData, etc.). The DUAL actually works in the query; changing 1 to a fake column name parses correctly. Since it's possible (although somewhat pointless) to include a static value in a column definition I was hoping that there might be some way to get the parser to recognize that the column name comes after the AS. `SELECT [static_value1] [AS] c1, [static_value2] [AS] c2, ... cn` [download] The DUAL was just used as an example as we have many queries which include staticly defined values. I guess the fastest solution will be to strip out anything which may be a static define.	[reply] [d/l]
Re^3: Static Data, SQL::Parser by renodino (Curate) on May 08, 2007 at 02:36 UTC
The DUAL actually works in the query; changing 1 to a fake column name parses correctly. Sorry, I should've been more specific. Yes, any table name will parse OK, as will omitting the FROM clause entirely. I meant to indicate that you shouldn't rely on DUAL being supported by any underlying specialization. (Teradata ? As I live and breathe, I never expected to encounter someone on PM who actually knows what a Teradata is...) Perl Contrarian & SQL fanboy	[reply]
Re^4: Static Data, SQL::Parser by CLovegren (Novice) on May 08, 2007 at 08:21 UTC
Re: Static Data, SQL::Parser by jZed (Prior) on May 07, 2007 at 23:59 UTC
SQL::Parser currently only accepts column names and functions in the select list. Yes, numbers, string literals and numberic expressions in the select list are valid SQL but SQL::Parser does not pretend to parse all valid SQL (see the docs). Patches and/or collaborators are welcome.	[reply]
Re^2: Static Data, SQL::Parser by CLovegren (Novice) on May 08, 2007 at 08:28 UTC
jZed, first off, let me say a fantastic tool it is; fast and flexible, an absolute god-send. The post of this thread really was to see if there was a quick (and possibly dirty) way to get it to ignore this particular error and move on with parsing. In the future I'd love to rip in to the code and see what I can do with it. Since I'm unfamiliar with all of its in's and out's I was really hoping it was something I had overlooked (along the lines of tweaking a setting or loading a different file to get it to work). For now I'll look at another solution to get what I need done, but going forward I'd love to see what I can do with this; it'd make a fantastic data farming tool for our environment! Keep up the great work!	[reply]
Re: Static Data, SQL::Parser by FunkyMonk (Bishop) on May 07, 2007 at 22:31 UTC
Please disregard. I've answered a different question. My apologies Does quoting the column name help? It does in MySQL create table t ( id integer auto_increment primary key, `1` text ); [download] works fine here but didn't without the quoting. Edit: Clarification Edit^2: Quoting with `"` works with SQL::Statement Edit^3: Sorry, I've misunderstood your problem	[reply] [d/l] [select]