Using the Teiid query parser

[markaddleman]

Our application generates SELECT statements by piecing together strings that we read from config files.  I would like to programmatically alter the resulting strings.  I have tried a couple of Java SQL parsing libraries but none of them are quite right for Teiid's dialect of SQL.  I would like to use Teiid's query parser to generate the AST objects, modify them and then generate the resulting SELECT statements. 

Can someone point me to the classes that I should look at? 

[shawkins]

Mark,

org.teiid.query.parser.QueryParser is the parsing entry point.  It is backed by a JavaCC generated parser using the engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj file.  See TestParser for examples that call the parser and construct language objects (org.teiid.query.sql.lang...).  However the returned language objects do not represent a full AST, they only contain information relevant to Teiid - whitespace, some of the original keyword string forms, and non-hint comments will be lost.  The language objects are also not a public api so they can and will change as needed.

Steve

[gamvi01]

Steve,

Can you please let us know how to use QueryParser ? I want to extract the from clause , where clause , groupby etc  from the query passed. I looked at TestParser, SQLParser. SQLParser has methods i need like final public Query query(ParseInfo info) throws ParseException and from the query object i can retrieve from , where etc but how parseInfo has to be set with the query ?  How can i use query method in SQLParser ?

[gamvi01]

final SQLParser parser = new SQLParser(new StringReader("select * from abc where test='abc'"));

        try

        {

            final Query query = parser.query(new ParseInfo());

            System.err.println(query.getFrom());

            System.err.println(query.getCriteria().toString());

        } catch (final ParseException e)

        {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

I guess iam getting it this way.

[rareddy]

If in question take a look at org.teiid.query.parser.TestParser

[shawkins]

Vineela,

Seeing the test code should help out quite a bit.  You will want to use org.teiid.query.parser.QueryParser entry point rather than the lower level SQLParser.  For example:

QueryParser parser = QueryParser.getQueryParser();

Query query = (Query)parser.parseCommand(sqlString);

Steve

[gamvi01]

Hi

Thanks Steve , that worked for me . Is there any way to turn off the formatting teiid does when you use get*** methods from Query class ? Due to the formatting teiid does its getting complicated to do the SQL manipulation as search for my where or From clause returns -1.

For ex : Say my query is : SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons inner join Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName

When i say getFrom() - it returns FROM Persons INNER JOIN Orders ON Persons.P_Id = Orders.P_Id    i expect it to return FROM Persons inner join Orders ON Persons.P_Id=Orders.P_Id

and say if my query is SELECT Persons."LastName", Persons."FirstName", Orders."OrderNo" FROM Persons inner join Orders ON Persons."P_Id"=Orders."P_Id" ORDER BY Persons."LastName" where Person."FirstName" like '%test%'

when i say getCriteria it returns Person.FirstName like '%test%'    i expect it to retunr Person."FirstName" like '%test%'

[gamvi01]

I figured it out. We have to use the setter methods instead of doing all the formatting.

[bdtweed]

To expand upon Mark's question, I find myself in a situation where I need to programmatically rewrite a query and I'm making use of the Teiid QueryParser API to do it. To aid in the rewriting, I need to be able to inspect the columns named in the SELECT part of a query to figure out which table of a JOIN the columns originate from.

Consider the example:

SELECT a, b, c, y, z from TBL1 INNER JOIN TBL2 on a = x;

In this example a, b, and c are columns from TBL1. The columns x, y, and z are columns from TBL2. In looking at the just the SQL string, this isn't immediately obvious.

I need to be able to programmatically determine if a named column originates from TBL1 or TBL2. Is there a way to do such a thing either with the QueryParser APIs or via some other means?

[shawkins]

> Is there a way to do such a thing either with the QueryParser APIs or via some other means?

The parser by itself will just create the preliminary object form.  You need to use the QueryResolver to determine the metadata and expression type information.  This however is a little deeper into Teiid and you have to at least supply the metadata.  See TestResolver and the main entry method QueryResolver.resolveCommand