in reply to Parse complex SQL statement

A CPAN search for parse sql yields a myriad of results, including SQL::Parser. I've never used the module, but it looks like it has a very long history and its most recent commits are only a few weeks old. Did you try this one? How didn't it work for you?

#11929 First ask yourself `How would I do this without a computer?' Then have the computer do it the same way.

Replies are listed 'Best First'.
Re^2: Parse complex SQL statement
by perl_help26 (Beadle) on Jun 12, 2014 at 06:09 UTC

    As I already mentioned: I tried it but it's giving me undef values for all parameters ! maybe because of nested queries? Thanksss

[reply]
      One possibility for why the parser is failing is I think your posted SQL is invalid: unless I'm reading it wrong, you have 7 parameters to insert but 8 terms because you have a repeated comma. I also note no nested queries in the query provided, so it's also possible that I can't diagnose the issue because you haven't posted the correct target.

      If this is an internal utility, you could accomplish your goal with something like 

      m/insert\s+into\s+(\S+)\s*\(\s*([^)]+?)\s*\)\s*
   (?:values\s*\(\s*([^)]+?)\s*\)
    |
      select\s*(.+?)\s+(?:where|order)
   )
 /ix

      [download]
      but this has a number of potential issues: nested queries will parse incorrectly, it fails to consider quoted names and string literals, ... Which is why I usually suggest canned code.

      Update: Or misparse by me; the regular expression should read 

      m/insert\s+into\s+(\S+)\s*\(\s*([^)]+?)\s*\)\s*
   (?:values\s*\(\s*([^)]+?)\s*\)
    |
      select\s*(.+?)\s+(?:from\b)
   )
 /ix

      [download]
      Of course, your posted SQL doesn't actually correspond to the SQL posted in the original_string field in Re^2: Parse complex SQL statement.

      #11929 First ask yourself `How would I do this without a computer?' Then have the computer do it the same way.

[reply]
[d/l]
[select]

        Hello Kenneth, Thanks for your reply... There was a typo in my sql the successive commas shd have had "null" in between... However, this is not the issue. I have a problem with the nested queries... Also, I can't use this regexp because it's too specific and I have various sql statements with different structures...
        I found a module at: https://searchcode.com/codesearch/view/836428/
        This seems to be parsing fine for now ... I just need to find a way to keep the hierarchy on the nested sqls...

[reply]

In Section Seekers of Perl Wisdom