There are lots of improvements since V0.4, below are some of the most important improvements:

• Oracle SQL and PLSQL query parse tree structure was fully accessible.
• toString() of all parse tree node classes was supported, so you can get text of parse tree node.
• All classes are documented.
• Add an XML demo to illustrate how to visit query parse tree node, and generate XML output of SQL statement.

You can download General SQL Parser Java version here:
http://www.sqlparser.com/download.php

If you have any questions or comments about this SQL Parser, feel free to contact me:  info@sqlparser.com

In this version, we can easily fetch table and column from stored procedures(packages) of Oracle and SQL Server.

Let’s take this create trigger statement(Oracle) for example:

CREATE TRIGGER hr.emp_permit_changes BEFORE DELETE OR INSERT OR UPDATE ON hr.employees BEGIN INSERT INTO customers (customer_id, cust_last_name, cust_first_name) VALUES ( :new.customer_id, :new.cust_last_name, :new.cust_first_name);

INSERT INTO orders (order_id, order_date, customer_id) VALUES ( :new.order_id, :new.order_date, :new.customer_id); EXCEPTION WHEN duplicate_info THEN Raise_application_error (num => -20107, msg => ‘Duplicate customer or order ID’); END;

/

Here are  table and column in above SQL Statement fetched by demo gettablecolumns shipped together with this component:

Tables: customers hr.employees orders

Fields: customers.cust_first_name customers.cust_last_name customers.customer_id hr.employees.cust_first_name hr.employees.cust_last_name hr.employees.customer_id hr.employees.order_date hr.employees.order_id orders.customer_id orders.order_date orders.order_id

Below is another create procedure SQL sample of SQL Server:

Here are table and column in above SQL Statement fetched by demo gettablecolumns shipped together with this component:

Tables: production.product purchasing.productvendor purchasing.vendor

Fields: production.product.name production.product.productid purchasing.productvendor.productid purchasing.productvendor.vendorid purchasing.vendor.activeflag purchasing.vendor.creditrating purchasing.vendor.name purchasing.vendor.vendorid

You can download General SQL Parser Java version here: http://www.sqlparser.com/download.php If you have any questions or comments about this SQL Parser, feel free to contact me:  info@sqlparser.com

In this release, General SQL Parser can fetch all tables and columns in select/delete/insert/update and create table statement.

Use this SQL Parser Java version, It’s very easy to find out the tables have Create, Read, Update, Delete and Insert operations against them

Take this SQL script for example:

select last_name,job_id,salary from employees where job_id=(select job_id from employees where employee_id=141) and salary > (select salary from employees where employee_id=141);

insert into emp e1 (e1.lastname,job) values(scott,10);

delete from department where department_name = ‘Finance’;

update employees set job_id = (select job_id from employees where employee_id = 205), salary = (select salary from employees where employee_id = 205) where employee_id = 114;

When you use demo getcrud.java shipped together with this Java SQL Parser, you will get this output:

employees 5(select)   0(create table)   0(delete)   0(insert)   1(update) emp 0(select)   0(create table)    0(delete)   1(insert)   0(update) department 0(select)   0(create table)    1(delete)   0(insert)   0(update)

Do you interested in this? You can download Java SQL Parser from here.

In next release, this SQL Parser will be able to fetch all table and columns from SQL Server stored procedure(procedure/function/trigger), Oracle stored procedure(procedure/function/trigger) and package.

Feel free to contact me at support@dpriver.com if you have any questions or suggestions about this SQL Parser.

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.

I’m very excited to let you know that General SQL Parser Java Version 0.1 was finally released in 01-22-2010. This is the first release of general sql parser java version.

This SQL parser had a solid infrastructure now, with the ability to parse most SQL statements used in Access, SQL Server, DB2, MySQL and Oracle, including nested queries, complex join clause, stored procedure, trigger, function, PLSQL packages and etc. Syntax check is the most basic feature which is available once I finished the infrastructure of this SQL parser. But I’m too busy to add more features like  SQL formatter, fetch table and column information of SQL statement, modify and rebuild SQL, query tree structure in xml(Those features are already available in .NET and VCL version).  So SQL syntax check is the main feature of this release, and I will add more features(some may not listed above) one by one based on requests from users. If you want to use this SQL parser in your project or product, please don’t hesitate to contact me(info@sqlparser.com) for further information about this SQL parser.

Ok, Here are some detailed information about this release.

All supported SQL statements are listed in those packages:

gudusoft.gsqlparser.stmt

gudusoft.gsqlparser.stmt.db2

gudusoft.gsqlparser.stmt.mysql

gudusoft.gsqlparser.stmt.mssql

gudusoft.gsqlparser.stmt.oracle

This SQL Parser can be used in several ways such as:

1. Check SQL syntax of Access, Db2, MySQL, SQL Server, Oracle dialect. Give detailed error message if found any syntax error in SQL script.

2. Get all SQL statements with type and fragment information in SQL script.

select last_name,job_id,salary from employees where job_id=(select job_id from employees where employee_id=141) and salary > (select salary from employees where employee_id=141);

insert into emp e1 (e1.lastname,job) values(scott,10);

delete from department where department_name = ‘Finance’;

update employees set job_id = (select job_id from employees where employee_id = 205), salary = (select salary from employees where employee_id = 205) where employee_id = 114;

SQL parser can analyze this script, and list 4 individual statement like this:

sstselect ========= select last_name,job_id,salary from employees where job_id=(select job_id from employees where employee_id=141) and salary > (select salary from employees where employee_id=141);

sstinsert ========= insert into emp e1 (e1.lastname,job) values(scott,10);

sstdelete ========= delete from department where department_name = ‘Finance’;

sstupdate ========= update employees set job_id = (select job_id from employees where employee_id = 205), salary = (select salary from employees where employee_id = 205) where employee_id = 114;

3. Tokenize SQL script, easy to check identifier, keyword, operator, constants, comment and special characters.

insert into emp e1 (e1.lastname,job) values(scott,10);

After tokenize this script, SQL parser will generate output like this:

ttkeyword insert ttwhitespace ttkeyword into ttwhitespace ttidentifier emp ttwhitespace ttidentifier e1 ttwhitespace ttleftparenthesis ( ttidentifier e1 ttperiod . ttidentifier lastname ttcomma , ttidentifier job ttrightparenthesis ) ttwhitespace ttkeyword values ttleftparenthesis ( ttidentifier scott ttcomma , ttnumber 10 ttrightparenthesis ) ttsemicolon ;

If you like to have a try of this library, you can download it here:

http://www.sqlparser.com/download.php

What kind of  features will be  in next release?

I will like to add feature to fetch table and column in select/delete/insert/update statement in next release, hopefully, this feature will be available in the end of Feb 2010.

If you have any suggestions or features request about next release, feel free to contact me(info@sqlparser.com), your feedback is always welcome.