I am working on creating a huge metadata dictionary and search application for workload automation processes, which in almost all Sybase and Oracle directly come from SQL scripts. I need to take an inventory for each SQL statement in each script and extract things like type of statement (e.g. INSERT, UPDATE, CREATE TABLE, etc.), Tables in tables, etc.
I have a prototype parsing application that does an OK job using mostly regex. Programming something like this is tiring beyond any nightmare, and to be honest, I'd rather dig 3 'ditches in the middle of summer than cover all possible use cases. Plus, I feel like I'm reinventing the wheel. I thought that there should be some kind of API, which, if you submit it only as an SQL statement, it will tell you No. 1 if it compiles and then extracts a structure for you, which you can access through regular POJO objects . Like getStatementType, getFromTables (), etc.
The scripts I'm dealing with are written in almost all coding styles, and there is no standard format, there are also all kinds of forms and syntax (aggregates, subqueries, you name it).
So my question is: is there an API that parses SQL, given SQL, and maybe which provider is specifically designed to return normalized data?
I know there are plugins for Eclipse that do similar things (DDL editor and Quantum DB), so I thought I could hide some of their APIs that deal with SQL recognition. Thoughts?
source
share