in reply to SQL Parsing

In general, this isn't possible from SQL alone -- you have to look at the database.

Given table foo containing columns (id, a, b) and table bar containing columns (id, c, d), what would you expect your program to return from this SQL? 

SELECT a, b, c, d from foo, bar where foo.id = bar.id;

[download]
What about this? 
SELECT * from foo, bar where foo.id = bar.id;

[download]

Of course, your codebase may not contain these types of statement.

Replies are listed 'Best First'.
Re^2: SQL Parsing
by jZed (Prior) on Jul 10, 2007 at 17:21 UTC
    Good points. SQL::Statement only checks those kinds of things on execute(), not on prepare(). For example it dies on prepare() if you use a column name that isn't a valid SQL identifier but prepare() succeeds if you use a non-existant column name - it only finds that out later. For example, it errors if a column name exists in two tables and your SQL doesn't specigy which table the name refers to, but it only checks for that and dies on execute(). If you have the database available and wanted to use it to check the column names without actually executing anything you'd probably be able to just grab the parts of execute() that check the database tables for actual column names.

    update :Another option might be to create a hash structure of the table/columns you expect to encounter and tweak the return from SQL::Statement to fill in table information from that when the module was not able to get it from the SQL.

[reply]

In Section Seekers of Perl Wisdom