Re: Parse and change an SQL statement

In fact, SQL::Statement appears to be unusable even for just parsing. The key function is column_defs(), which is supposed to return the columns in the statement; and it isn't defined. Trying to execute

our $SqlParser = SQL::Parser->new() or die "Could not create an SQL::P
+arser";
my $parse = SQL::Statement->new('SELECT foo FROM bar', $SqlParser);
my @columns = $parse->column_defs();
[download]

results in the error

Can't locate object method "column_defs" via package "SQL::Statement" 
+at /local/ifs_projects/prok/function/src/lib/pgoetzUtils.pm line 939.
[download]

Comment on Re: Parse and change an SQL statement Select or Download Code

Replies are listed 'Best First'.
Re^2: Parse and change an SQL statement by CountZero (Bishop) on Mar 20, 2012 at 15:59 UTC
I tried this and it works: `use Modern::Perl; use Data::Dump qw /dump/; use SQL::Parser; use SQL::Statement; my $SqlParser = SQL::Parser->new() or die "Could not create an SQL::Pa +rser"; my $parse = SQL::Statement->new('SELECT fie, max(foo), count(baz) FROM + bar JOIN fee WHERE foo > 10', $SqlParser); my @columns = $parse->column_defs(); say dump @columns;` [download] Output: { fullorg => "fie", type => "column", value => "fie" }, { alias => "MAX", arg => { fullorg => "foo", type => "column", value => "foo" }, argstr => "foo", distinct => "ALL", fullorg => "max (foo)", name => "MAX", type => "setfunc", }, { alias => "COUNT", arg => { fullorg => "baz", type => "column", value => "baz" }, argstr => "baz", distinct => "ALL", fullorg => "count (baz)", name => "COUNT", type => "setfunc", }, CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James My blog: Imperial Deltronics	[reply] [d/l]

Replies are listed 'Best First'.

Re^2: Parse and change an SQL statement
by CountZero (Bishop) on Mar 20, 2012 at 15:59 UTC

use Modern::Perl;
use Data::Dump qw /dump/;

use SQL::Parser;
use SQL::Statement;

my $SqlParser = SQL::Parser->new() or die "Could not create an SQL::Pa
+rser";
my $parse = SQL::Statement->new('SELECT fie, max(foo), count(baz) FROM
+ bar JOIN fee WHERE foo > 10', $SqlParser);
my @columns = $parse->column_defs();
say dump @columns;
[download]

Output:


  { fullorg => "fie", type => "column", value => "fie" },
  {
    alias    => "MAX",
    arg      => { fullorg => "foo", type => "column", value => "foo" },
    argstr   => "foo",
    distinct => "ALL",
    fullorg  => "max (foo)",
    name     => "MAX",
    type     => "setfunc",
  },
  {
    alias    => "COUNT",
    arg      => { fullorg => "baz", type => "column", value => "baz" },
    argstr   => "baz",
    distinct => "ALL",
    fullorg  => "count (baz)",
    name     => "COUNT",
    type     => "setfunc",
  },

CountZero

A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

My blog: Imperial Deltronics

[reply]
[d/l]