CODEWITHSUNDEEP
sqlantlr
snowguy
snowguy

Reputation: 911

should I use ANTLR to transform simple SQL select statements?

I need to write a program that accepts a basic SQL select statement and outputs an XML file which breaks down the statement into it's component parts (the output columns, the input tables, the join, group by, aggregations, etc.). At a minimum I need to support 2 tables in the from statement, different join types, where clause, group by and having. If more complex sql can be supported (e.g. subqueries) that's a bonus, but not necessary.

I checked out ANTLR and it looks like a steep learning curve. Given that I have the ability to require the SQL to be fairly simple, should I attempt to hand code or should I embark on the world of ANTLR?

Upvotes: 3

Views: 4036

Answers (2)

James Wang
James Wang

Reputation: 483

I don't think you need to reinvent such a sql parser with ANTLR. Here is a sample that decode select SQL statement into meta-data:

SELECT e.last_name      AS name,
   e.commission_pct comm,
   e.salary * 12    "Annual Salary"
FROM   scott.employees AS e
WHERE  e.salary > 1000
ORDER  BY
  e.first_name,
  e.last_name;

Meta info:

            Select statement:
                Select set type: none

            select clause:
                Columns
                    Fullname:e.last_name
                    Prefix:e    Column:last_name    alias:name
                    Fullname:e.commission_pct
                    Prefix:e    Column:commission_pct   alias:comm
                    Fullname:e.salary * 12
                    Prefix: Column:e.salary * 12    alias:"Annual Salary"

            from clause:
                scott.employees as e
                tableowner: scott
                tablename:  employees
                tablealias: e

            where clause:
                e.salary > 1000

            order by clause:
                e.first_name,
            e.last_name

If this is what you need, then you can check this article that illustrate how to use a sql parser to achieve this.

Upvotes: 3

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726809

Steep learning curve of ANTLR consists mostly of learning recursive descent parsing technique. The syntax and the idiosyncrasies of the tool contribute to the complexity, but they are secondary. In other words, you would need to learn how to do parsers no matter what, but with the checks built into automatic parser generator you minimize your chances of coding an invalid grammar.

You could also opt for a pre-built SQL parser (here is one for .NET and another one for Java). I tried them both, and they work fine. You may need TopCoder membership to download the components. Both these components use generated parsers, but they use JavaCC and its C# port instead of ANTLR. The grammars are reasonably close, though, so you may choose them as a starting point for your project.

Upvotes: 1

Related Questions