General SQL Parser Java v0.2 release

In this release, General SQL Parser can fetch all tables and columns in select statement. It  supports SQL dialect of Oracle and SQL Server database . This feature will be very useful if you need to analyze  tons of SQL scripts during database migration, apply some new SQL scripts to a production database, review other peoples scripts,   SQL trouble shooting.

This SQL Parser will analyze SQL scripts in-depth,  all SQL clauses such as select list, from clause, where predicate, group by clause, order by clause, update clause will be analyzed. Of course, nested subquery, complex join clause will be processed correctly. Columns in expression, predicate, and function parameter will be pick up correctly. Common Table Expression(CTE) will also be handled without any problems.

Let’s take some SQL for example, and see how general sql parser works:

select employee_id,last_name,
(case department_id when department_id =
(select department_id from departments
where location_id = 1800)
then ‘Canada’ else ‘Usa’ end) location
from employees;

This is a simple SQL, but still it not that easy to find out all tables and columns involved, General SQL Parser Java version can do it quickly and automatically:

Tables:
departments
employees

Columns:
departments.department_id
departments.location_id
employees.department_id
employees.employee_id
employees.last_name

Here is a three way joins SQL:

select employee_id,city,department_name
from employee e
join department d
on d.department_id = e.department_id
join locations l
on d.location_id = l.location_id;

Tables and columns involved:

Tables:
employee
department
locations

Columns:
employee.city
employee.department_id
employee.department_name
employee.employee_id
department.department_id
department.location_id
locations.location_id

General SQL Parser can also process CTE correctly,

with MyCTE(x)
as
(select x=’hello’)
select x from MyCTE;

General SQL Parser will tell you that there is no physical table involved in this SQL.

You can download a demo (gettablecolumns.java) here to check how it works with your own SQL.

Support for Delete, Insert, Update and Create table statement will be added soon in next release. After that, General SQL Parser will be able to get tables and columns information from stored procedures such as function/procedure in SQL Server and trigger, package in Oracle. Currently, all those features are available in .NET version. So I think it will not take too long to migrate those features to Java version.

Introduce a new method getrawsqlstatements of TGSqlParser

By using this method, you can find out all SQL statements in script file even if  there are syntax error. This method is useful if you only need to fetch individual statement from a big SQL script file. You can know what’s kind of SQL statement(Select/Delete/Insert/Update) before send it to your database server will prevent you from SQL injection.

You can check demo: getstatement.java in this SQL Parser library for more information.

Feel free to to contact me(info@sqlparser.com)  if you have any questions or comments about this SQL Parser.