get-started-cn

SQL Object and TObjectName

SQL Object and TObjectName类

数据库中的Object,例如table, column, view, schema, database 在ANSI SQL中用<identifier>表示。 <identifier>是一个字符串,最大长度为128个字符。

<regular identifier>

<regular identifier> ::=
    Object name

<SQL language identifier>

<SQL language identifier> ::=
    Object name

An <SQL language identifier> is a <regular identifier> that consists only of simple Latin letters, digits, and underscore characters. It must begin with a simple Latin letter.

<delimited identifier>

<delimited identifier> ::=
    "Object name"

ANSI SQL中,<delimited identifier>用 double quote marks(双引号),而在SQL Server中,使用[ and ]. MySQL使用 `.

Qualification of <identifier>s

qualified object name, 是指几个identifier的组合,形成有层次结构的object name。 一般是

server.db.schema.table.column

SQL object name and TObjectName

在GSP中,用TObjectName表示SQL object name,用TSourceToken类表示identifier。 因此TObjectName 包含一个或多个TSourceToken,有下列可用的token:

public TSourceToken getMethodToken()    -- 表示method
public TSourceToken getPartToken()      -- 表示column
public TSourceToken getObjectToken()    -- 表示 tableview, index等schema level的object
public TSourceToken getSchemaToken()
public TSourceToken getDatabaseToken()
public TSourceToken getServerToken()

Column

  • Only column name

      select empNo
      from emp
    

    表示empNo column 的TObjectName中的各种值如下:

      getDbObjectType() = column
      toString() = empNo
      getPartToken() = empNo
    
  • qualified column name

      select t.empNo
      from emp t
    

    表示empNo column 的TObjectName中的各种值如下:

      getDbObjectType() = column
      toString() = t.empNo
      getPartToken() = empNo
      getObjectToken() = t
    

Table

  • Only the table name
      select empNo
      from emp
    

    表示emp table的TObjectName中的各种值如下:

      getDbObjectType() = table
      toString() = emp
      getObjectToken() = emp
    
  • qualified table name with schema
      select t.empNo
      from scott.emp t
    

    表示emp table的TObjectName中的各种值如下:

      getDbObjectType() = table
      toString() = scott.emp
      getObjectToken() = emp
      getSchemaToken() = scott
    
  • qualified table name with database and schema
      select t.empNo
      from hrdb.scott.emp t
    

    表示emp table的TObjectName中的各种值如下:

      getDbObjectType() = table
      toString() = scott.emp
      getObjectToken() = emp
      getSchemaToken() = scott
      getDatabaseToken() = hrdb
    
  • qualified table name with server, database and schema
      select t.empNo
      from server1.hrdb.scott.emp t
    

    表示emp table的TObjectName中的各种值如下:

      getDbObjectType() = table
      toString() = scott.emp
      getObjectToken() = emp
      getSchemaToken() = scott
      getDatabaseToken() = hrdb
      getServerToken() = server1
    

Function

SELECT * FROM Department D
CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)

表示fn_GetAllEmployeeOfADepartment function的TObjectName中的各种值如下:

getDbObjectType() = function
toString() = dbo.fn_GetAllEmployeeOfADepartment
getObjectToken() = fn_GetAllEmployeeOfADepartment
getSchemaToken() = dbo

Method

Method like nodes() of SQL Server xml Data Type.

select	doc.c.value('level', 'int') as [Level]
from	N.ReformattedSegments.nodes('/path/segment') doc(c)

表示N.ReformattedSegments.nodes method 的TObjectName中的各种值如下:

getDbObjectType() = method
toString() = N.ReformattedSegments.nodes
getMethodToken() = nodes
getPartToken() = ReformattedSegments
getObjectToken() = N

SELECT and TSelectSqlStatement

SELECT 和 TSelectSqlStatement类

各种数据库中使用的SELECT语句, 在SQL ANSI中对应<query specifcation>,或更复杂的<query expression>。 我们常说的SUBQUERY,是<left paren> <query expression> <right paren>,代表从<query expression>衍生出来的一个标量、一行记录、或一个表。

一些基本的术语

1. table expression

<table expression>te1, Specify a table or a grouped table.

<table expression> ::=
    <from clause>
    [ <where clause> ]
    [ <group by clause> ]
    [ <having clause> ]
    [ <window clause> ]

2. table reference

<table reference>tr1,tr2, Reference a table.

Since the result of a query is a Table derived by evaluating that query, not all SQL Tables have an explicit <Table name> – so SQL allows you to refer to any Table using a <Table reference>. (重点是SQL中的table不仅仅是数据库中存在的base table, 还有其它例如subquery, VALUES CLAUSE, function table等)。

<table reference> ::=
    <table factor>
    | <joined table>
    
<table factor> ::=
    <table primary> [ <sample clause> ]

3. query specifcation

<query specifcation>qs1, Specify a table derived from the result of a <table expression>.

<query specification> ::=
    SELECT [ <set quantifier> ] <select list> <table expression>

4. query expression

<query expression>qe1, Specify a table.

<query specifcation>的基础上,支持<with clause> 和 SET OPERATOR(UNION,EXCEPT,INTERSECT),同时支持<table value constructor>,<explicit table>.

<query expression>用括号括起来后,可以用在SQL中任何需要Table的地方。

<query expression> ::=
    [ <with clause> ] <query expression body>
    
<query expression body> ::=
    <query term>
    | <query expression body> UNION [ ALL | DISTINCT ]
    [ <corresponding spec> ] <query term>
    | <query expression body> EXCEPT [ ALL | DISTINCT ]
    [ <corresponding spec> ] <query term>
    
<query term> ::=
    <query primary>
    | <query term> INTERSECT [ ALL | DISTINCT ] [ <corresponding spec> ] <query primary>
    
<query primary> ::=
    <simple table>
    | <left paren> <query expression body> <right paren>
    
<simple table> ::=
    <query specification>
    | <table value constructor>
    | <explicit table>    

5. subquery

<subquery>s1, Specify a scalar value, a row, or a table derived from a <query expression>.

<scalar subquery> ::=
    <subquery>

<row subquery> ::=
    <subquery>

<table subquery> ::=
    <subquery>
    
<subquery> ::=
    <left paren> <query expression> <right paren>

GSP 中的对应类

SQL语句在GSP中都有对应的类,其中对应<query expression>的是TSelectSqlStatementTSelectSqlStatement也可以表示<query specification>,<table value constructor>及用SET OPERATOR连接的<query specification>

1. TSelectSqlStatement表示<query specification>

这是最常见的用法,TSelectSqlStatement类的属性包含<table expression>的各个组成部分<from clause>,<where clause>,<group by clause>,<having clause>,<window clause>,也可以包含<with clause>

2. TSelectSqlStatement表示 SET OPERATOR连接的<query specification>

这种情况下,只有TSelectSqlStatement getLeftStmt() and TSelectSqlStatement getRightStmt()可用,分别表示

SELECT boss_name, salary FROM Bosses
UNION
SELECT employee_name, salary FROM Employees;

中的

SELECT boss_name, salary FROM Bosses
SELECT employee_name, salary FROM Employees;

其中,getLeftStmt()表示的TSelectSqlStatement可以再次表示SET OPERATOR连接的<query specification>,形成递归。而getRightStmt()表示的TSelectSqlStatement只能是<query specification>

public boolean isCombinedQuery()可以用来确定TSelectSqlStatement是否为SET OPERATOR连接。

3. TSelectSqlStatement表示<table value constructor>

<table value constructor>最长见的用法是在INSERT语句中,但也可以用在需要SELECT的地方。 这就是为什么<table value constructor>在GSP中是用TSelectSqlStatement类来表示的原因。

VALUES (NEWAAA, new), (NEWBBB, new)

因此,当我们处理表示<table value constructor>TSelectSqlStatement类实例时,必须要小心, 因为很多SELECT语句中的元素这时在TSelectSqlStatement类实例中不存在, 访问这些类属性将会导致NullPointerException的错误。

isValueClause() 可以用来检查一个TSelectSqlStatement类是否表示<table value constructor>,如果是,用 TValueClause getValueClause()来获取进一步的详细内容。

Reference

  • te1. ISO/IEC 9075-2 (SQL/Foundation) 2006. p318, 7.4
  • tr1, ISO/IEC 9075-2 (SQL/Foundation) 2006. p321, 7.6
  • tr2, crate-sql99.pdf, p462
  • qs1, ISO/IEC 9075-2 (SQL/Foundation) 2006. p361, 7.12
  • qe1, ISO/IEC 9075-2 (SQL/Foundation) 2006. p371, 7.13
  • s1, ISO/IEC 9075-2 (SQL/Foundation) 2006. p390, 7.15
Back to Top ↑

changelog

General SQL Parser Java version 1.9.5.5 released (2018-10-22)

  • GSP Java version 1.9.5.5(2018-10-21)
    • [Oracle] able to recognize and parser noneditionable trigger
    • [Teradata] support at time zone clause of DATE datatype.
    • [Teradata] support with return only clause in declare cursor statement.
    • [SQL Server] support OBJECT:: in grant statement.
    • [SQL Server] support xmlnamespaces clause used together with CTE.

General SQL Parser dotnet version 3.2.7.2 released (2018-10-22)

  • dotnet version 3.2.7.2(2018-10-21)
    • [SQL Server] support NO_PERFORMANCE_SPOOL, MIN_GRANT_PERCENT, HINT_MAX_GRANT_PERCENT
    • [API] add new enum elements: EQueryHint.E_QUERY_HINT_NO_PERFORMANCE_SPOOL, E_QUERY_HINT_MIN_GRANT_PERCENT, E_QUERY_HINT_MAX_GRANT_PERCENT
    • [SQL Server/scriptWriter] support call target expression of function call.
    • [Oracle] able to recognize create or repleace context statement.
    • [DB2] able to link column in call statement to table of create trigger statement.
  • dotnet version 3.2.7.1(2018-10-09)
    • [SQL Server] Doesn’t treat [DATE] as a regular column name.
  • dotnet version 3.2.7.1(2018-10-08)
    • [API] TTypeName.isCharUnit(), TTypeName.ByteUnit is no longer used, replaced by TTypeName.charUnitToken
    • [Oracle] able to get label name after loop/while statement.
    • [Oracle] support on null clause in default clause.
    • [API] add new property: TColumnDefinition.onNull
  • dotnet version 3.2.7.0(2018-10-08)
    • [sql formatter] Able to format subquery or CASE statement inside of CAST function.
  • dotnet version 3.2.6.9(2018-10-03)
    • [API] add new property: TColumnDefinition.persistedColumn.
    • [SQL Server] able to get persisted information from column definition.
    • [API] add new property: TColumnDefinition.filestream.
    • [SQL Server] support FILESTREAM keyword in create table.
    • [API] add new property: TColumnDefinition.sparseColumn.
    • [SQL Server] Able to get SPARSE column information from TColumnDefinition.
  • dotnet version 3.2.6.8(2018-09-28)
    • [MySQL] Able to get comment/KEY_BLOCK_SIZE/parser name index option in MySQL create index statement.
    • [API] TMySQLIndexOption.indexOptionType
    • [API] add new enum: EIndexOptionType
    • [API] add new property : TCreateIndexSqlStatement.IndexOptionList to access index option of MySQL create index statement.
    • [SQL Server] able to fetch include column list from create index statement.

General SQL Parser Java version changelog 2018-08-01

version history of general sql parser:

  • GSP Java version 1.9.4.4(2018-08-22)
    • [API] Add new method TSourceToken TSourceToken.nextSolidToken(boolean treatCommentAsSolidToken)
    • [API] Add new method TSourceToken TSourceToken.prevSolidToken(boolean treatCommentAsSolidToken)

General SQL Parser .NET version changelog 2018-08-01

  • dotnet version 3.2.5.6(2018-08-01)
    • [Oracle] fix a bug can’t parse plsql block not ended by a semicolon.
    • [general] avoid System.ArgumentNullException when call isvalidsqlpluscmd().
  • dotnet version 3.2.5.6(2018-07-27)
    • [scriptWriter] fix a bug when new TObjectName with object and part source token.
    • [API] add new method: TSourceToken.toScript()
Back to Top ↑

sql-syntax

SQL Object and TObjectName

SQL Object and TObjectName类

数据库中的Object,例如table, column, view, schema, database 在ANSI SQL中用<identifier>表示。 <identifier>是一个字符串,最大长度为128个字符。

<regular identifier>

<regular identifier> ::=
    Object name

<SQL language identifier>

<SQL language identifier> ::=
    Object name

An <SQL language identifier> is a <regular identifier> that consists only of simple Latin letters, digits, and underscore characters. It must begin with a simple Latin letter.

<delimited identifier>

<delimited identifier> ::=
    "Object name"

ANSI SQL中,<delimited identifier>用 double quote marks(双引号),而在SQL Server中,使用[ and ]. MySQL使用 `.

Qualification of <identifier>s

qualified object name, 是指几个identifier的组合,形成有层次结构的object name。 一般是

server.db.schema.table.column

SQL object name and TObjectName

在GSP中,用TObjectName表示SQL object name,用TSourceToken类表示identifier。 因此TObjectName 包含一个或多个TSourceToken,有下列可用的token:

public TSourceToken getMethodToken()    -- 表示method
public TSourceToken getPartToken()      -- 表示column
public TSourceToken getObjectToken()    -- 表示 tableview, index等schema level的object
public TSourceToken getSchemaToken()
public TSourceToken getDatabaseToken()
public TSourceToken getServerToken()

Column

  • Only column name

      select empNo
      from emp
    

    表示empNo column 的TObjectName中的各种值如下:

      getDbObjectType() = column
      toString() = empNo
      getPartToken() = empNo
    
  • qualified column name

      select t.empNo
      from emp t
    

    表示empNo column 的TObjectName中的各种值如下:

      getDbObjectType() = column
      toString() = t.empNo
      getPartToken() = empNo
      getObjectToken() = t
    

Table

  • Only the table name
      select empNo
      from emp
    

    表示emp table的TObjectName中的各种值如下:

      getDbObjectType() = table
      toString() = emp
      getObjectToken() = emp
    
  • qualified table name with schema
      select t.empNo
      from scott.emp t
    

    表示emp table的TObjectName中的各种值如下:

      getDbObjectType() = table
      toString() = scott.emp
      getObjectToken() = emp
      getSchemaToken() = scott
    
  • qualified table name with database and schema
      select t.empNo
      from hrdb.scott.emp t
    

    表示emp table的TObjectName中的各种值如下:

      getDbObjectType() = table
      toString() = scott.emp
      getObjectToken() = emp
      getSchemaToken() = scott
      getDatabaseToken() = hrdb
    
  • qualified table name with server, database and schema
      select t.empNo
      from server1.hrdb.scott.emp t
    

    表示emp table的TObjectName中的各种值如下:

      getDbObjectType() = table
      toString() = scott.emp
      getObjectToken() = emp
      getSchemaToken() = scott
      getDatabaseToken() = hrdb
      getServerToken() = server1
    

Function

SELECT * FROM Department D
CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)

表示fn_GetAllEmployeeOfADepartment function的TObjectName中的各种值如下:

getDbObjectType() = function
toString() = dbo.fn_GetAllEmployeeOfADepartment
getObjectToken() = fn_GetAllEmployeeOfADepartment
getSchemaToken() = dbo

Method

Method like nodes() of SQL Server xml Data Type.

select	doc.c.value('level', 'int') as [Level]
from	N.ReformattedSegments.nodes('/path/segment') doc(c)

表示N.ReformattedSegments.nodes method 的TObjectName中的各种值如下:

getDbObjectType() = method
toString() = N.ReformattedSegments.nodes
getMethodToken() = nodes
getPartToken() = ReformattedSegments
getObjectToken() = N

SELECT and TSelectSqlStatement

SELECT 和 TSelectSqlStatement类

各种数据库中使用的SELECT语句, 在SQL ANSI中对应<query specifcation>,或更复杂的<query expression>。 我们常说的SUBQUERY,是<left paren> <query expression> <right paren>,代表从<query expression>衍生出来的一个标量、一行记录、或一个表。

一些基本的术语

1. table expression

<table expression>te1, Specify a table or a grouped table.

<table expression> ::=
    <from clause>
    [ <where clause> ]
    [ <group by clause> ]
    [ <having clause> ]
    [ <window clause> ]

2. table reference

<table reference>tr1,tr2, Reference a table.

Since the result of a query is a Table derived by evaluating that query, not all SQL Tables have an explicit <Table name> – so SQL allows you to refer to any Table using a <Table reference>. (重点是SQL中的table不仅仅是数据库中存在的base table, 还有其它例如subquery, VALUES CLAUSE, function table等)。

<table reference> ::=
    <table factor>
    | <joined table>
    
<table factor> ::=
    <table primary> [ <sample clause> ]

3. query specifcation

<query specifcation>qs1, Specify a table derived from the result of a <table expression>.

<query specification> ::=
    SELECT [ <set quantifier> ] <select list> <table expression>

4. query expression

<query expression>qe1, Specify a table.

<query specifcation>的基础上,支持<with clause> 和 SET OPERATOR(UNION,EXCEPT,INTERSECT),同时支持<table value constructor>,<explicit table>.

<query expression>用括号括起来后,可以用在SQL中任何需要Table的地方。

<query expression> ::=
    [ <with clause> ] <query expression body>
    
<query expression body> ::=
    <query term>
    | <query expression body> UNION [ ALL | DISTINCT ]
    [ <corresponding spec> ] <query term>
    | <query expression body> EXCEPT [ ALL | DISTINCT ]
    [ <corresponding spec> ] <query term>
    
<query term> ::=
    <query primary>
    | <query term> INTERSECT [ ALL | DISTINCT ] [ <corresponding spec> ] <query primary>
    
<query primary> ::=
    <simple table>
    | <left paren> <query expression body> <right paren>
    
<simple table> ::=
    <query specification>
    | <table value constructor>
    | <explicit table>    

5. subquery

<subquery>s1, Specify a scalar value, a row, or a table derived from a <query expression>.

<scalar subquery> ::=
    <subquery>

<row subquery> ::=
    <subquery>

<table subquery> ::=
    <subquery>
    
<subquery> ::=
    <left paren> <query expression> <right paren>

GSP 中的对应类

SQL语句在GSP中都有对应的类,其中对应<query expression>的是TSelectSqlStatementTSelectSqlStatement也可以表示<query specification>,<table value constructor>及用SET OPERATOR连接的<query specification>

1. TSelectSqlStatement表示<query specification>

这是最常见的用法,TSelectSqlStatement类的属性包含<table expression>的各个组成部分<from clause>,<where clause>,<group by clause>,<having clause>,<window clause>,也可以包含<with clause>

2. TSelectSqlStatement表示 SET OPERATOR连接的<query specification>

这种情况下,只有TSelectSqlStatement getLeftStmt() and TSelectSqlStatement getRightStmt()可用,分别表示

SELECT boss_name, salary FROM Bosses
UNION
SELECT employee_name, salary FROM Employees;

中的

SELECT boss_name, salary FROM Bosses
SELECT employee_name, salary FROM Employees;

其中,getLeftStmt()表示的TSelectSqlStatement可以再次表示SET OPERATOR连接的<query specification>,形成递归。而getRightStmt()表示的TSelectSqlStatement只能是<query specification>

public boolean isCombinedQuery()可以用来确定TSelectSqlStatement是否为SET OPERATOR连接。

3. TSelectSqlStatement表示<table value constructor>

<table value constructor>最长见的用法是在INSERT语句中,但也可以用在需要SELECT的地方。 这就是为什么<table value constructor>在GSP中是用TSelectSqlStatement类来表示的原因。

VALUES (NEWAAA, new), (NEWBBB, new)

因此,当我们处理表示<table value constructor>TSelectSqlStatement类实例时,必须要小心, 因为很多SELECT语句中的元素这时在TSelectSqlStatement类实例中不存在, 访问这些类属性将会导致NullPointerException的错误。

isValueClause() 可以用来检查一个TSelectSqlStatement类是否表示<table value constructor>,如果是,用 TValueClause getValueClause()来获取进一步的详细内容。

Reference

  • te1. ISO/IEC 9075-2 (SQL/Foundation) 2006. p318, 7.4
  • tr1, ISO/IEC 9075-2 (SQL/Foundation) 2006. p321, 7.6
  • tr2, crate-sql99.pdf, p462
  • qs1, ISO/IEC 9075-2 (SQL/Foundation) 2006. p361, 7.12
  • qe1, ISO/IEC 9075-2 (SQL/Foundation) 2006. p371, 7.13
  • s1, ISO/IEC 9075-2 (SQL/Foundation) 2006. p390, 7.15

values-clause and TSelectSqlStatement

values-clause
	Derives a result table by specifying the actual values, using expressions, for
	each column of a row in the result table. Multiple rows may be specified.
SELECT EMPNO, emp_act
FROM EMP_ACT
WHERE PROJNO IN(MA2100, MA2110, MA2112)
UNION
VALUES (NEWAAA, new), (NEWBBB, new)

values-clause最长见的用法是在INSERT语句中,但也可以用在需要SELECT语句的地方。 这就是为什么values-clause在GSP中是用TSelectSqlStatement类来表示的原因。

values-clause的语法构成和SELECT语句非常的不同,

VALUES (1),(2),(3)

因此,当我们处理表示values-clauseTSelectSqlStatement类实例时,必须要小心, 因为很多SELECT语句中的元素这时在TSelectSqlStatement类实例中不存在, 访问这些类属性将会导致NullPointerException的错误。

访问TSelectSqlStatement表示的values-clause

Use this method to get values-clause

public TValueClause getValueClause()

if the return value is not null, then this TSelectSqlStatement instance represents a values-clause.

Since GSP Java version 2.0.6.9(2020-06-18), TSelectSqlStatement.isValueClause() is added to help determine whether a TSelectSqlStatement class represents a values-clause or not.

Duplicate column in create view

CREATE TABLE t1(f1 int , f3 int);
CREATE TABLE t2(f1 int , f3 int);

CREATE VIEW v1
as
SELECT 
	t1.f1,  -- please note the column name will be f1 in v1
	t2.f1,  -- please note the column name will be f1 in v1
	t1.f2, t2.f3
FROM t1, t2
WHERE t1.f1 = t2.f1;

If you executed the above SQL against Oracle database, there will be a ORA-00957 error which means duplicate column name.

But the following SQL query will be executed succefully, because the view column name is specified.

CREATE VIEW v1 (t1f1, t2f1, t1f2, t2f3)
as
SELECT 
	t1.f1,
	t2.f1,
	t1.f2, t2.f3
FROM t1, t2
WHERE t1.f1 = t2.f1;
Back to Top ↑

gsp-dotnet

Dotnet Parser Throws Exception

General SQL Parser .NET version

We have have confirmed, that the issue appears strictly only with .NET framework 4.6.1586.0, but happens just sometimes.

With the higher .NET framework 4.7.2053.0 all works fine.

2019-10-02 14:02:56,506 ERROR [11] ParserBase:0 Failed to parse vendor MsSql sql string 
CREATE TABLE COPS_ZT (
                                REC_ID     BIGINT IDENTITY NOT NULL,
                                REF_REC_ID BIGINT NOT NULL,
                                REC_DATE DATETIME2 NOT NULL,
                                REC_STATUS       NUMERIC NOT NULL,
                                REC_ERROR        NVARCHAR(255),
                                ZT_CREATION_USER NVARCHAR(255),
                                ZT_CREATION_DATE DATETIME2)
2019-10-02 14:02:56,513 ERROR [11] ParserBase:0 System.ArgumentException: Destination array was not long enough. Check destIndex and length, and the array's lower bounds.
   at System.Array.Copy(Array sourceArray, Int32 sourceIndex, Array destinationArray, Int32 destinationIndex, Int32 length, Boolean reliable)
   at System.Collections.Generic.Dictionary`2.Resize(Int32 newSize, Boolean forceNewHashCodes)
   at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
   at gudusoft.gsqlparser.nodes.TTypeName.searchTypeByName(String typenameStr)
   at gudusoft.gsqlparser.nodes.TTypeName.set_DataTypeByToken(TSourceToken value)
   at gudusoft.gsqlparser.nodes.TTypeName.setDataTypeInTokens()
   at gudusoft.gsqlparser.TParserMssqlSql.yyaction(Int32 yyruleno)
   at gudusoft.gsqlparser.TParserMssqlSql.yyparse()
   at gudusoft.gsqlparser.TCustomSqlStatement.dochecksyntax(TCustomSqlStatement psql)
   at gudusoft.gsqlparser.TCustomSqlStatement.parsestatement(TCustomSqlStatement pparentsql, Boolean isparsetreeavailable)
   at gudusoft.gsqlparser.TGSqlParser.doparse()
   at CopsDatabasePatcher.SqlParser.SqlParser.Parse(String sql, DatabaseSystem system)
2019-10-02 14:02:56,522 ERROR [11] ParserBase:0 SQL 'CREATE TABLE COPS_ZT (
                                REC_ID     BIGINT IDENTITY NOT NULL,
                                REF_REC_ID BIGINT NOT NULL,
                                REC_DATE DATETIME2 NOT NULL,
                                REC_STATUS       NUMERIC NOT NULL,
                                REC_ERROR        NVARCHAR(255),
                                ZT_CREATION_USER NVARCHAR(255),
                                ZT_CREATION_DATE DATETIME2)' cannot be parsered! Reason 

Code to produce this error:

Back to Top ↑

how-to

General SQL Parser FAQ

Table of Contents

  1. Technical support
    1. Q: Does general SQL parser depend on any third party library/software/DLLs?
    2. Q: In order to use GSP to validate SQL syntax, do I need to connect to a database instance such as Oracle?
    3. Q: How long will my feature request or bug report be processed?
    4. Q: When database vendor add new SQL syntax, how long will those SQL syntaxes be supported in general SQL parser?
    5. Q: Is GSP .NET version a .NET Standard library?
  2. Licensing and billing
    1. Q: What’s kind of General SQL Parser license do I need?
    2. Q: Will the license expire?
    3. Q: if I were to buy support for one database to start – can I add additional ones at a later date?
    4. Q: We have a need to create a parsing service. Can we use the components to develop a parser and then deploy the service to a web-like Tomcat for other to consume via API or is this specific to a specific user and a specific user’s machine?
    5. Q: May I use GSP in more than one product?
    6. Q: Payment term?
  3. Sales and reseller
    1. Q: We are resellers. can we purchase your products for our customers?
    2. Q: Can we purchase via emailed PO

Technical support

Q: Does general SQL parser depend on any third party library/software/DLLs?

General SQL Parser(GSP) doesn’t depend on any third party library/software/DLLs. In order to run GSP Java version, JRE 1.5 is needed. In order to run GSP .NET version, .NET Framework 4.5 or higher is needed.

Q: In order to use GSP to validate SQL syntax, do I need to connect to a database instance such as Oracle?

GSP can validate SQL syntax without any connection to the database instance, no internet connection. GSP includes all SQL parser engines itself, no additional file or connection is needed.

Q: How long will my feature request or bug report be processed?

It depends. Various from weeks to months according to the complexity of the issue. In addition to providing you with our free tech support, we also offer customized services which fix the bug and implement the feature in time, please check info@sqlparser.com for the detailed information.

Q: When database vendor add new SQL syntax, how long will those SQL syntaxes be supported in general SQL parser?

General SQL Parser supports both PL/SQL and SQL. Although we try to add support for all SQL syntax of the database, it’s quite difficult to make sure all SQL syntax of the database is supported especially keep up with the recent version.

The goal of General SQL Parser is NOT to support all SQL syntax of the database, but support the most used SQL syntax. So, our strategy is to add support for the new SQL syntax when it is requested by the user.

Q: Is GSP .NET version a .NET Standard library?

Yes. General SQL Parser .NET version is .NET Standard compatible which means it can run on all .NET platforms that implement .NET Standard.

Licensing and billing

Q: What’s kind of General SQL Parser license do I need?

General SQL Parser is licensed as per user/developer. There are 3 types of developer license: single user license, team license(2-5 developers) and site license(more than 5 developers). All those licenses grant the developer the right to install and use multiple copies of the Software. You also need to specify the database platforms that need to be included when you purchase the license. There is no distribution or deployment license fee which means you can deploy general SQL parser library together with your product to the end user without any limitation. The only licensed developer can access APIs of General SQL Parser library, any third party developer or programs can’t access APIs of General SQL Parser even it’s wrapped by your own program.

Q: Will the license expire?

You can use the software without any time limitation. It never expired. Furthermore, you can upgrade to the latest version of the software within 12 months after purchase. However, if you like to upgrade to the latest version after 12 months, you need purchase our yearly subscription which enables another 12 months upgrade and free tech support. the price for annual subscription is 30% of the original purchase. You will be notified when it’s time to renew your license, You need to renew this annual subscription yourself.

Q: if I were to buy support for one database to start – can I add additional ones at a later date?

Yes, of course. You only need to pay the price for the additional database when you need.

Q: We have a need to create a parsing service. Can we use the components to develop a parser and then deploy the service to a web-like Tomcat for other to consume via API or is this specific to a specific user and a specific user’s machine?

General SQL Parser is licensed as per user/developer. A developer license is needed If any user/developer/machine need to access API, even a wrapper is created and API of GSP is not accessed directly.

Q: May I use GSP in more than one product?

Yes. There is no limitation of how many products GSP can be used in.

Q: Payment term?

The full licensed version will be available to download from the official site within 2 working days after we receive the payment. You need to send the payment first.

Sales and reseller

Q: We are resellers. can we purchase your products for our customers?

Yes, you can purchase software from our online shop on behalf of your customer. After purchasing software, email us detailed information about your customer. The full licensed version should be available to download from our site within 48 hours after we receive your order. There is no discount for reseller within the first 50 licenses.

Q: Can we purchase via emailed PO

Yes, our award-winning payment processor support emailed PO

Back to Top ↑

gsp-api

TExpression and acceptChildren

User should be careful When use acceptChildren() method in the public void preVisit(TExpression node) or public void postVisit(TExpression node) method of your customzied visitor class that extends TParseTreeVisitor.

class TreeVisitor extends TParseTreeVisitor {

    public void preVisit(TExpression node) {
        switch (node.getExpressionType()) {
                case logical_and_t:
                case logical_or_t:
                        node.getLeftOperand().acceptChildren(this);
                        node.getRightOperand().acceptChildren(this);
                    break;
                default:
                    break;        
        }
    }

The above code will cause the left and right sub-expression repeatedly visited, if the expression itself is deeply nested, then the performance will be impacted severely.

Since the acceptChildren() method of TExpression will iterate all sub-expression for you automatically, you shouldn’t call acceptChildren() in the preVisit() method of your own visitor. Only add your business code in the preVisit() method and let the acceptChildren() method of TExpression do the iteration for you.

If you want to iterate all sub-expression in your own code, please use accept() method instead of acceptChildren().

Below is the code of the acceptChildren() method of TExpression for your reference.

    public void acceptChildren(TParseTreeVisitor v){
        v.preVisit(this);
        switch(expressionType){
            case simple_object_name_t:
                objectOperand.acceptChildren(v);
                break;
            case list_t:
            case collection_constructor_list_t:
            case collection_constructor_multiset_t:
            case collection_constructor_set_t:
                if (exprList != null){
                    for(int i=0;i<exprList.size();i++){
                        exprList.getExpression(i).acceptChildren(v);
                    }
                }
                break;
            case function_t:
            case new_structured_type_t:
            case type_constructor_t:
                functionCall.acceptChildren(v);
                break;
            case cursor_t:
            case subquery_t:
            case multiset_t:
                subQuery.acceptChildren(v);
                break;
            case case_t:
                caseExpression.acceptChildren(v);
                break;
            case pattern_matching_t:
                leftOperand.acceptChildren(v);
                rightOperand.acceptChildren(v);
                if (likeEscapeOperand != null){
                    likeEscapeOperand.acceptChildren(v);
                }
                break;
            case exists_t:
                subQuery.acceptChildren(v);
                break;
            case new_variant_type_t:
                newVariantTypeArgumentList.acceptChildren(v);
                break;
            case unary_plus_t:
            case unary_minus_t:
            case unary_prior_t:
                rightOperand.acceptChildren(v);
                break;
            case arithmetic_plus_t:
            case arithmetic_minus_t:
            case arithmetic_times_t:
            case arithmetic_divide_t:
            case power_t:
            case range_t:
            case concatenate_t:
            case period_ldiff_t:
            case period_rdiff_t:
            case period_p_intersect_t:
            case period_p_normalize_t:
            case contains_t:
                leftOperand.acceptChildren(v);
                rightOperand.acceptChildren(v);
                break;
            case assignment_t:
                leftOperand.acceptChildren(v);
                rightOperand.acceptChildren(v);
                break;
            case sqlserver_proprietary_column_alias_t:
                rightOperand.acceptChildren(v);
                break;
            case arithmetic_modulo_t:
            case bitwise_exclusive_or_t:
            case bitwise_or_t:
            case bitwise_and_t:
            case bitwise_xor_t:
            case exponentiate_t:
            case scope_resolution_t:
            case at_time_zone_t:
            case member_of_t:
            case arithmetic_exponentiation_t:
                leftOperand.acceptChildren(v);
                rightOperand.acceptChildren(v);
                break;
            case at_local_t:
            case day_to_second_t:
            case year_to_month_t:
                leftOperand.acceptChildren(v);
                break;
            case parenthesis_t:
                leftOperand.acceptChildren(v);
                break;
            case simple_comparison_t:
                leftOperand.acceptChildren(v);
                rightOperand.acceptChildren(v);
                break;
            case group_comparison_t:
                leftOperand.acceptChildren(v);
                rightOperand.acceptChildren(v);
                break;
            case in_t:
                leftOperand.acceptChildren(v);
                rightOperand.acceptChildren(v);
                break;
            case floating_point_t:
                leftOperand.acceptChildren(v);
                break;
            case logical_and_t:
            case logical_or_t:
            case logical_xor_t:
            case is_t:
                leftOperand.acceptChildren(v);
                rightOperand.acceptChildren(v);
                break;
            case logical_not_t:
                rightOperand.acceptChildren(v);
                break;
            case null_t:
                leftOperand.acceptChildren(v);
                break;
            case between_t:
                if (betweenOperand != null){
                    betweenOperand.acceptChildren(v);
                }
                leftOperand.acceptChildren(v);
                rightOperand.acceptChildren(v);
                break;
            case is_of_type_t:
                leftOperand.acceptChildren(v);
                break;
            case collate_t: //sql server,postgresql
                leftOperand.acceptChildren(v);
                rightOperand.acceptChildren(v);
                break;
            case left_join_t:
            case right_join_t:
                leftOperand.acceptChildren(v);
                rightOperand.acceptChildren(v);
                break;
            case ref_arrow_t:
                leftOperand.acceptChildren(v);
                rightOperand.acceptChildren(v);
                break;
            case typecast_t:
                leftOperand.acceptChildren(v);
                break;
            case arrayaccess_t:
                arrayAccess.acceptChildren(v);
                break;
            case unary_connect_by_root_t:
                rightOperand.acceptChildren(v);
                break;
            case interval_t:
                intervalExpr.acceptChildren(v);
                break;
            case unary_binary_operator_t:
                rightOperand.acceptChildren(v);
                break;
            case left_shift_t:
            case right_shift_t:
                leftOperand.acceptChildren(v);
                rightOperand.acceptChildren(v);
                break;
            case array_constructor_t:
                if (subQuery != null){
                    subQuery.acceptChildren(v);
                }else if (exprList != null){
                    exprList.acceptChildren(v);
                }else if (arrayConstruct != null){
                    arrayConstruct.acceptChildren(v);
                }
                break;
            case row_constructor_t:
                if (exprList != null){
                    exprList.acceptChildren(v);
                }
                break;
            case unary_squareroot_t:
            case unary_cuberoot_t:
            case unary_factorialprefix_t:
            case unary_absolutevalue_t:
            case unary_bitwise_not_t:
                getRightOperand().acceptChildren(v);
                break;
            case unary_factorial_t:
                getLeftOperand().acceptChildren(v);
                break;
            case bitwise_shift_left_t:
            case bitwise_shift_right_t:
                getLeftOperand().acceptChildren(v);
                getRightOperand().acceptChildren(v);
                break;
            case multiset_union_t:
            case multiset_union_distinct_t:
            case multiset_intersect_t:
            case multiset_intersect_distinct_t:
            case multiset_except_t:
            case multiset_except_distinct_t:
                getLeftOperand().acceptChildren(v);
                getRightOperand().acceptChildren(v);
                break;
            case json_get_text:
            case json_get_text_at_path:
            case json_get_object:
            case json_get_object_at_path:
            case json_left_contain:
            case json_right_contain:
            case json_exist:
            case json_any_exist:
            case json_all_exist:
                getLeftOperand().acceptChildren(v);
                getRightOperand().acceptChildren(v);
                break;
            case interpolate_previous_value_t:
                getLeftOperand().acceptChildren(v);
                getRightOperand().acceptChildren(v);
                break;
            case submultiset_t:
                leftOperand.acceptChildren(v);
                rightOperand.acceptChildren(v);
                break;
            case overlaps_t:
                leftOperand.acceptChildren(v);
                rightOperand.acceptChildren(v);
                break;
            case is_a_set_t:
                leftOperand.acceptChildren(v);
                break;
            case array_t:
                if (getExprList() != null){
                    getExprList().acceptChildren(v);
                }
                break;
            default:;
        }

        v.postVisit(this);
    }

Back to Top ↑

data-lineage

The star column (*) in the process of the column level lineage

How to handle the star column (*) during the process of the column level lineage

create view v as select * from t;

The SQL will create a view: v by using the column name in the table t. However, without the additional metadata, the GSP doesn’t know the column name in table t. So, use * to link the relation between view and table like this: t.* -> RS-1.* -> v.*

<relation id="1" type="fdd" effectType="select">
  <target id="6" column="*" parent_id="5" parent_name="RS-1" coordinate="[1,25],[1,26]"/>
  <source id="3" column="*" parent_id="2" parent_name="t" coordinate="[1,25],[1,26]"/>
</relation>
<relation id="2" type="fdd" effectType="create_view">
  <target id="9" column="*" parent_id="8" parent_name="v" coordinate="[1,25],[1,26]"/>
  <source id="6" column="*" parent_id="5" parent_name="RS-1" coordinate="[1,25],[1,26]"/>
</relation>

The GSP can do a little better if the SQL is something like this:

create view v as select * from t where column1 + column2 > 0;

Visualize the SQL

The GSP will know column1 and column2 must belonged to table t, so this relation is created:

<relation id="1" type="fdd" effectType="select">
  <target id="7" column="*" parent_id="6" parent_name="RS-1" coordinate="[1,25],[1,26]"/>
  <source id="3" column="column1" parent_id="2" parent_name="t" coordinate="[1,40],[1,47]"/>
  <source id="4" column="column2" parent_id="2" parent_name="t" coordinate="[1,50],[1,57]"/>
</relation>

and the resultset outputed in the select * must include column1 and column2.

<relation id="1_0" type="fdd" effectType="select">
  <target id="7_0" column="column1" parent_id="6" parent_name="RS-1" coordinate="[1,25],[1,26]"/>
  <source id="3" column="column1" parent_id="2" parent_name="t" coordinate="[1,40],[1,47]"/>
</relation>
<relation id="1_1" type="fdd" effectType="select">
  <target id="7_1" column="column2" parent_id="6" parent_name="RS-1" coordinate="[1,25],[1,26]"/>
  <source id="4" column="column2" parent_id="2" parent_name="t" coordinate="[1,50],[1,57]"/>
</relation>

Please note that the relation id is in the format of 1_index, where the index is 0,1. This means that those columns are not really listed in the select list, but derived from the star column in the select list whose relation id is 1.

Also, column1 and column2 listed in the view: t is derived from the star column with the following relation map.

<relation id="3_0" type="fdd" effectType="create_view">
  <target id="10_0" column="column1" parent_id="9" parent_name="v" coordinate="[1,25],[1,26]"/>
  <source id="7_0" column="column1" parent_id="6" parent_name="RS-1" coordinate="[1,25],[1,26]"/>
</relation>
<relation id="3_1" type="fdd" effectType="create_view">
  <target id="10_1" column="column2" parent_id="9" parent_name="v" coordinate="[1,25],[1,26]"/>
  <source id="7_1" column="column2" parent_id="6" parent_name="RS-1" coordinate="[1,25],[1,26]"/>
</relation>
<relation id="3" type="fdd" effectType="create_view">
  <target id="10" column="*" parent_id="9" parent_name="v" coordinate="[1,25],[1,26]"/>
  <source id="7" column="*" parent_id="6" parent_name="RS-1" coordinate="[1,25],[1,26]"/>
</relation>
Back to Top ↑

get-started

Back to Top ↑