I am working on building a huge meta data dictionary and lookup application for workload automation processes that are pretty much all Sybase and Oracle straight out SQL scripts. What I need to do is make an inventory for every SQL statement in every script and extract things like statement type (e.g. INSERT, UPDATE, CREATE TABLE etc), tables into, tables from etc.
I have a prototype parsing application that does an OK job using mostly regex. Programming something like that is tedious beyond any nightmare and, frankly, I'd rather be digging 3' ditches in the midst of summer than covering all the possible use case scenarios. Plus I feel like I am reinventing the wheel. I thought there must be some API out there that, if you feed it just a SQL statement, it will tell you #1 if it will compile and then extract the structure for you that you can access through the normal POJO facilities. Like getStatementType, getFromTables() etc.
The scripts I am dealing with are written in just about all coding styles and there is no standard format, there is also every kind of statement form and syntax represented (aggregates, subqueries, you name it).
So my question is: is there an API that parses SQL given SQL and maybe which vendor it is specific to to return normalized particulars?
I know that there are plugins for Eclipse that do similar things (DDL editor and Quantum DB) so I thought I could sneak some of their API that deal with SQL recognition. Thoughts?
