problems using SQL::Statement

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

Hi monks!

I want to parse a CREATE-Statement, but that doesn't work. I've tried the following code:

#!/usr/bin/perl

use strict;
use warnings;
use SQL::Statement;

my $statement = qq~CREATE TABLE test(
  ID int not null,
  Spalte1 varchar(255),
  Spalte2 int,
  Spalte3 int,
  Spalte4 int,
  Spalte5 blob,
  Spalte6 varchar(33),
  primary key(ID),
)~;

my $parser = SQL::Parser->new();
my $stmt = SQL::Statement->new($statement,$parser);
[download]

But I've got the following error-message:
SQL ERROR: 'BLOB' is not a recognized data type!

So I've changed for testing the module this data type, then I got an other error-message:
SQL ERROR: Bad table or column name 'primary' is a SQL reserved word!

I'm working with perl5.8.6 and module's version 1.14

So, how can I use the module for SQL92-standard-statements? Would you use an other module?

Thank you,
Renee

Comment on problems using SQL::Statement Download Code

Replies are listed 'Best First'.
Re: problems using SQL::Statement by Joost (Canon) on Jul 29, 2005 at 13:51 UTC
You can explicitly allow BLOBs by doing `$parser->feature('valid_data_types','BLOB',1);` [download] before parsing the statement. If you look at SQL::Statement::Syntax, you can see that specifying the primary key (or any other constraint) at the end of the column list is not supported. You might be able to extend the parser, but I don't know how difficult that would be. In stead, you can (in this instance) specify the primary key constraint as a column constraint. This is what works for me: `use strict; use SQL::Statement; my $statement = qq~CREATE TABLE test( ID int not null primary key, Spalte1 varchar(255), Spalte2 int, Spalte3 int, Spalte4 int, Spalte5 blob, Spalte6 varchar(33), )~; my $parser = SQL::Parser->new(); $parser->feature('valid_data_types','BLOB',1); my $stmt = SQL::Statement->new($statement,$parser);` [download] "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply] [d/l] [select]
Re^2: problems using SQL::Statement by reneeb (Chaplain) on Jul 29, 2005 at 14:35 UTC
Thanks for your answer, I will try to extend the parser. Your provided solution would not solve the whole problem. Any .sql-file that adhere to the SQL-standard should be accepted.	[reply]
Re^3: problems using SQL::Statement by Solo (Deacon) on Jul 29, 2005 at 15:09 UTC
Any .sql-file that adhere to the SQL-standard should be accepted. Exactly which standard(s) would be one of the bigger problems when extending the parser. --Solo -- You said you wanted to be around when I made a mistake; well, this could be it, sweetheart.	[reply]
Re^4: problems using SQL::Statement by jfroebe (Parson) on Jul 29, 2005 at 16:00 UTC
Re^5: problems using SQL::Statement by reneeb (Chaplain) on Jul 29, 2005 at 16:28 UTC
Re^5: problems using SQL::Statement by Solo (Deacon) on Jul 29, 2005 at 21:11 UTC
Re: problems using SQL::Statement by jZed (Prior) on Jul 30, 2005 at 01:45 UTC
SQL::Statement is under heavy development and we've been adding support for a variety of SQL features with each major release. The development versions are available at an SVN repository and I'll be creating a kwiki to discuss development soon as there are already several other developers working on it with me. I would very much welcome any suggestion for improvements and even more co-developers. The most recent version on CPAN includes all new POD on how to extend the SQL syntax via SQL, e.g. the newly introduced CREATE TYPE, DROP TYPE commands allow you to allow and disallow types in any way you'd like.	[reply]