[dtp-sqldevtools-dev] SQL Query Parser - how to create instance of org.e

[Shabinas Koratt Chambolan <Shabinas_Chambolan@xxxxxxxxxxx>]

 

HI All,

   I would like to know how to create an instance of org.eclipse.wst.rdb.internal.models.sql.schema.Database .

I am using oracle database and version is 10.2.0.1.0.

Also what is the dbname and version name I should pass for this method in order to get vendor specific parser manager.

Please do the needful.

 

 

 

1.   Introduction

The SQL Query Parser (also referred to as ‘parser’ in this document) is a generated parser. LPG (Lexer Parser Generator) is used to generate the parser, which, based on a set of grammar rules, generates the Parser and Lexer source code in Java. The focus of this document is on how to use the generated parser.

 

The parser takes SQL DML statements as input and creates instances of SQL Query Model classes if the SQL is syntactically valid. In addition to the syntactic validation, the parser can also perform a semantic validation. The parser is extensible to support vendor specific dialects and custom source generation.

 

2.   Usage

// imports needed

import java.util.ArrayList;

import java.util.Iterator;

import java.util.List;

 

import org.eclipse.datatools.modelbase.sql.datatypes.DataType;

import org.eclipse.datatools.modelbase.sql.query.QueryStatement;

import org.eclipse.datatools.modelbase.sql.query.ValueExpressionColumn;

import org.eclipse.datatools.modelbase.sql.query.helper.StatementHelper;

import org.eclipse.datatools.modelbase.sql.query.util.SQLQuerySourceFormat;

import org.eclipse.datatools.modelbase.sql.schema.Database;

import org.eclipse.datatools.sqltools.parsers.sql.SQLParseErrorInfo;

import org.eclipse.datatools.sqltools.parsers.sql.SQLParserException;

import org.eclipse.datatools.sqltools.parsers.sql.SQLParserInternalException;

import org.eclipse.datatools.sqltools.parsers.sql.postparse.PostParseProcessor;

import org.eclipse.datatools.sqltools.parsers.sql.query.SQLQueryParseResult;

import org.eclipse.datatools.sqltools.parsers.sql.query.SQLQueryParserManager;

import org.eclipse.datatools.sqltools.parsers.sql.query.SQLQueryParserManagerProvider;

import org.eclipse.datatools.sqltools.parsers.sql.query.postparse.DataTypeResolver;

import org.eclipse.datatools.sqltools.parsers.sql.query.postparse.TableReferenceResolver;

 

2.1           Invoking the parser

This code snippet below shows how to use the parser without using the semantic validation (known as post parse processing) and source formatting.

 

                        try {

                                    // Create an instance the Parser Manager

                                    // SQLQueryParserManagerProvider.getInstance().getParserManager

                                    // returns the best compliant SQLQueryParserManager

                                    // supporting the SQL dialect of the database described by the given

                                    // database product information. In the code below null is passed for both the database and version

                                    // in which case a generic parser is returned

 

                                    SQLQueryParserManager parserManager = SQLQueryParserManagerProvider

                                                            .getInstance().getParserManager(null, null);

                                    //Sample query

                                    String sql = "SELECT * FROM TABLE1";

                                    //Parse

                                    SQLQueryParseResult parseResult = parserManager.parseQuery(sql);

                                    // Get the Query Model object from the result

                                    QueryStatement resultObject = parseResult.getQueryStatement();

                                    // Get the SQL text

                                    String parsedSQL = resultObject.getSQL();

                                    System.out.println(parsedSQL);

 

                        } catch (SQLParserException spe) {

                                    // handle the syntax error

                                    System.out.println(spe.getMessage());

                                    List syntacticErrors = spe.getErrorInfoList();

                                    Iterator itr = syntacticErrors.iterator();

                                    while (itr.hasNext()) {

                                                SQLParseErrorInfo errorInfo = (SQLParseErrorInfo) itr.next();

                                                // Example usage of the SQLParseErrorInfo object

                                                // the error message

                                                String errorMessage = errorInfo.getParserErrorMessage();

                                                // the line numbers of error

                                                int errorLine = errorInfo.getLineNumberStart();

                                                int errorColumn = errorInfo.getColumnNumberStart();

                                    }

 

                        } catch (SQLParserInternalException spie) {

                                    // handle the exception

                                    System.out.println(spie.getMessage());

                        }

2.2           Getting a parser manager for a specific vendor

The recommended way is to instantiate a parser based on the database vendor information so as to handle vendor specific variations of SQL. The following code snippet shows how to create the parser based on the vendor information

                        // TODO initialize the variable db with the

                        // org.eclipse.wst.rdb.internal.models.sql.schema.Database intance obtained from

                        // the database connection

                        // Database db;

 

                        // TODO  get the vendorname and version after the variable is initialized

                        // String dbName = db.getVendor();

                        // String dbVersion = db.getVersion();

 

                        // get the best matching parser manager depending on what parser extension are plugged in

                        SQLQueryParserManager parserManager = SQLQueryParserManagerProvider

                                                .getInstance().getParserManager(dbName, dbVersion);

 

If not matching parser is found, then the generic parser is retuned.

 

2.3           Using post parse processing and source formatting

The semantic validation of the parsed SQL is done in a step called the Post Parse Processing.

At present there are two types of semantic validations are supported.

• Table reference resolving – verifying that the tables referred to in the SQL are existing in the database
• Data type resolving – verifying the data types of the result columns

 

Appending the schema name to the database object (for example schema1.table1) is optional, when the parser generates the SQL after a successful parse.

The following code snippet shows how to configure post parse processors and the source formatting for the parser.

 

                                // TODO initialize the variable db with the

                                // org.eclipse.wst.rdb.internal.models.sql.schema.Database intance obtained from

                                // the database connection

                                // initialize the variable currentSchemaName to be the schema name

                                // Database database;

                                // String currentSchemaName;

                                // String dbName = db.getVendor();

                                // String dbVersion = db.getVersion();

 

                                String sql = "SELECT * FROM TABLE1";

 

                                // create the post parse processors, maybe wrapped in createPostParseProcessorList()

                                PostParseProcessor tableRefResolver = new TableReferenceResolver(

                                                                database, currentSchemaName);

                                PostParseProcessor dataTypeResolver = new DataTypeResolver();

 

                                // ordering is important for post parse processing! first we need to fill

                                // in the database information for table references and column types

                                List postParseProcessors = new ArrayList();

                                postParseProcessors.add(0, tableRefResolver);

                                postParseProcessors.add(1, dataTypeResolver);

 

                                // get the SQL source format options and set at least the current schema

                                // that is omited but implicit for any unqualified table references

                                // important for later resolving of table references!

                                SQLQuerySourceFormat sourceFormat = SQLQuerySourceFormat

                                                                .copyDefaultFormat();

                                sourceFormat.setOmitSchema(currentSchemaName);

 

                                SQLQueryParserManager parserManager = SQLQueryParserManagerProvider

                                                                .getInstance().getParserManager(dbName, dbVersion);

                                parserManager.configParser(sourceFormat, postParseProcessors);

 

                                // parse the SQL statement

                                try {

                                                SQLQueryParseResult parseResult = parserManager.parseQuery(sql);

                                                List semanticErrors = parseResult.getErrorList();

                                                Iterator itr = semanticErrors.iterator();

                                                while (itr.hasNext()) {

                                                                SQLParseErrorInfo errorInfo = (SQLParseErrorInfo) itr.next();

 

                                                                // the error message

                                                                String errorMessage = errorInfo.getParserErrorMessage();

 

                                                                // the line numbers of error

                                                                int errorLine = errorInfo.getLineNumberStart();

                                                                int errorColumn = errorInfo.getColumnNumberStart();

 

                                                                // Error processing for specific errors

                                                                String errorCode = errorInfo.getErrorCode();

                                                                if (TableReferenceResolver.ERROR_CODE_TABLE_UNRESOLVED

                                                                                                .equals(errorCode)) {

                                                                                // table not found

                                                                } else if (TableReferenceResolver.ERROR_CODE_COLUMN_UNRESOLVED

                                                                                                .equals(errorCode)

                                                                                                || TableReferenceResolver.ERROR_CODE_NONEXISTENT_COLUMN

                                                                                                                                .equals(errorCode)) {

                                                                                // column not found

                                                                }

                                                }

 

                                } catch (SQLParserException spe) {

                                                // handle the syntax exception

                                                System.out.println(spe.getMessage());

 

                                } catch (SQLParserInternalException spie) {

                                                // handle the exception

                                                System.out.println(spie.getMessage());

                                }

 

2.4           Getting result columns

                        // the effective result columns of a QuerySelectStatement are the columns that are returned by

                        // the select statement

 

                        // e.g.:   SELECT SUBQRY.* FROM (SELECT COL1, COL2 FROM TABLEA) AS SUBQRY

 

                        // QueryStatement queryStmt = parserManager.parseQuery("SELECT SUBQRY.* FROM (SELECT COL1, COL2 FROM TABLEA) AS SUBQRY");

 

                        //          get the List of effective result columns with data types

                        List columnExprList = StatementHelper

                                                .getEffectivesResultColumns(queryStmt);

                        Iterator columnIt = columnExprList.iterator();

                        while (columnIt.hasNext()) {

                                    ValueExpressionColumn colExpr = (ValueExpressionColumn) columnIt

                                                            .next();

                                    DataType dataType = colExpr.getDataType();

                                    System.out.println("effective result column: " + colExpr.getName()

                                                            + " with data type: " + dataType.getPrimitiveType());

                        }

 

                        // expected Output of example "SELECT SUBQRY.* FROM (SELECT COL1, COL2 FROM TABLEA) AS SUBQRY":

                        //     effective result column: COL1 with data type: VARCHAR

                        //     effective result column: COL2 with data type: VARCHAR

 

            }

 

3        Parser Plugins

 

 

Plugins	Description
org.eclipse.datatools.sqltools.parsers.sql.lexer org.eclipse.datatools.sqltools.parsers.sql.lexer.db2	SQL Lexer and  lexer to support DB2 specific keywords
org.eclipse.datatools.sqltools.parsers.sql
org.eclipse.datatools.sqltools.parsers.sql.query org.eclipse.datatools.sqltools.parsers.sql.query.db2	DML Parser and extension  to support SQL dialect specific to DB2
org.eclipse.datatools.sqltools.parsers.sql.xml.query	Parser for SQL/XML:
org.eclipse.datatools.sqltools.parsers.sql.query.db2.test org.eclipse.datatools.sqltools.parsers.sql.query.test org.eclipse.datatools.sqltools.parsers.sql.test org.eclipse.datatools.sqltools.parsers.sql.xml.query.test	JUint test cases for the parser

 

Regards

Shabinas kc

 

 

 

**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely 
for the use of the addressee(s). If you are not the intended recipient, please 
notify the sender by e-mail and delete the original message. Further, you are not 
to copy, disclose, or distribute this e-mail or its contents to any other person and 
any such actions are unlawful. This e-mail may contain viruses. Infosys has taken 
every reasonable precaution to minimize this risk, but is not liable for any damage 
you may sustain as a result of any virus in this e-mail. You should carry out your 
own virus checks before opening the e-mail or attachment. Infosys reserves the 
right to monitor and review the content of all messages sent to or from this e-mail 
address. Messages sent to or from this e-mail address may be stored on the 
Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***