Browse Prior Art Database

Method for resolving column references and expression datatypes in a parsed SQL statement

IP.com Disclosure Number: IPCOM000016131D
Original Publication Date: 2002-Oct-03
Included in the Prior Art Database: 2003-Jun-21
Document File: 3 page(s) / 46K

Publishing Venue

IBM

Abstract

Disclosed is a program algorithm for resolving column references and value expression datatypes in a parsed structured query language (SQL) statement. The process involves three phases: syntactic parsing, resolution of table and column references, and resolution of all other value expressions. Each phase operates on a "model" of the syntactic structure of the SQL statement. An SQL parser can handle the syntactic aspect of parsing an SQL string by using information contained in the input SQL string alone. However, the parser cannot handle some of the more semantic aspects of parsing without extra information that is not contained in the SQL statement itself. These semantic aspects include associating column references that are contained in the SQL with the tables to which the columns belong, and assigning a datatype to each value expression contained in the SQL. (Value expressions include column references, literal values such as 2 or 'Smith', numeric expressions such AGE +10, string expressions such as LASTNAME CONCAT ', CONCAT FIRSTNAME, and the like.) The three phases of the process are defined as follows. Phase 1

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 46% of the total text.

Page 1 of 3

  Method for resolving column references and expression datatypes in a parsed SQL statement

   Disclosed is a program algorithm for resolving column references and value expression datatypes in a parsed structured query language (SQL) statement. The process involves three phases: syntactic parsing, resolution of table and column references, and resolution of all other value expressions. Each phase operates on a "model" of the syntactic structure of the SQL statement.

An SQL parser can handle the syntactic aspect of parsing an SQL string by using information contained in the input SQL string alone. However, the parser cannot handle some of the more semantic aspects of parsing without extra information that is not contained in the SQL statement itself. These semantic aspects include associating column references that are contained in the SQL with the tables to which the columns belong, and assigning a datatype to each value expression contained in the SQL. (Value expressions include column references, literal values such as 2 or 'Smith', numeric expressions such AGE +10, string expressions such as LASTNAME CONCAT ', ' CONCAT FIRSTNAME, and the like.)

The three phases of the process are defined as follows.

Phase 1

In the first phase, the input SQL string is syntactically parsed into an internal "model" form. The model used is a tree-structured representation of the syntactic elements of the SQL statement. For example, the following SQL statement

SELECT LASTNAME, SALARY * .05 FROM EMPLOYEE

can be represented in model form like this:

SQLQueryStatement

SQLSelectClause

SQLExpressionColumn (name: LASTNAME)

SQLExpressionCombined (operator: *)

SQLExpressionColumn (name: SALARY)

SQLExpressionLiteral (value: .05)

SQLFromClause

SQLTableReference (name: Employee)

where each named item represents a syntactic element of the SQL statement. The indentation above indicates a "contains" relationship. For example, a SQLQueryStatement element contains a SQLSelectClause and a SQLFromClause, and a SQLSelectClause contains a SQLExpressionColumn and a SQLExpressionCombined and so on.

1

Page 2 of 3

At the same time that the parser is producing this tree-structured model, it builds a simple list that contains references (or pointers) to the value expressions that it encounters. For the example after the SQL above is parsed, the value expression list contains the following elements.

SQLExpressionColumn (name: LASTNAME)

SQLExpressionCombined (operator: *)

SQLExpressionColumn (name: SALARY)

SQLExpressionLiteral (value: .05)

SQLExpressionTable (name: EMPLOYEE)

Attributes of the elements are shown in parenthesis following the element name. For example, the SQLExpressionColumn element has an attribute "name" with the value "LASTNAME".

Phase 2

In the second phase of the process, the value expressions that are of type SQLExpressionTable and SQLExpressionColumn in the expression list are semantically resolved by associating them with table (or view) and column entities in...