There are several ways you can use to identify database object in sql statement.
1. Iterate source tokens in sql statement,and check TSourceToken.DBObjType to find out database object in this sql.
This is the easiest way to find out database objects in sql statement after parse sql. You may read relationship between source token
and database object first to understand how this works.
You can iterate source tokens in several ways.
1.1 Iterate source tokens via TGSqlParser.SourceTokenList
All source tokens in input sql script are in this source token list, no matter how may sql statements in the input sql script.
1.2 Iterate source tokens via TCustomSqlStatement.SourceTokenList
If you want to visit source token per sql statement, this is preferred.
1.3 Iterate source tokens inside a sql clause, such as where clause, join clause.
You can visit source tokens only inside a sql clause. First, you need to find begin source token, and end source token of this sql clause,this can be done by using StartToken and EndToken property of TSourceToken, after that you can iterate source tokens between this 2 tokens in TCustomSqlStatement.SourceTokenList with range from StartToken.posinlist to EndToken.posinlist.
You can use this method to iterate source tokens in any parse tree nodes inherited from TLz_Node Class, such as Where clause,join clause.During visiting source tokens, you can find out any database objects by checking TSourceToken.DBObjType .
2. Using TCustomSqlStatement.Fields and TCustomSqlStatement.Tables to access fields and tables information in sql.
Only fields in select list and tables in from clause are listed in these 2 properties.
Fields in TCustomSqlStatement.Fields may not connect to columns in database directly, take this sql for example:
select f1+f2 from t1
there is one field in select list: f1+f2, which includes 2 columns f1 and f2. Please check TLzField.FieldType for type of field.
Tables in TCustomSqlStatement.Tables may not connect to tables in database directly, take this sql for example:
select f1 from (select f2 from t1)
Table in from clause is a subquery which is not a physical table in database.Please check TLzTable.TableType for type of table.
3. Using TCustomSqlStatement.OnFieldToken Event and TCustomSqlStatement.OnTableToken Event to access fields and tables information in sql.
During parsing sql statement, TCustomSqlStatement.OnFieldToken Event was fired when a source token represents a column was found, and TCustomSqlStatement.OnTableToken Event was fired when a source token represents a table wasa found.
You can find all columns and tables in sql statement that not limited in select list and from clause. But only column and table will be found, not other database object such as function will be found in current version. This limitation will be removed when more database objects event were introduced in new version.
4. Using Database object tokens property in TCustomSqlStatement such as ServerTokens,DatabaseTokens,SchemaTokens,TableTokens.
Database object tokens in TCustomSqlStatement such as ServerTokens,DatabaseTokens,SchemaTokens,TableTokens store all original information about different database objects in sql statement. You can find everything you need in those properties.
Meanwhile, information in those properties are grouped by each sql statement, and not sorted, same object may occurs serval time. Sometimes, we want to access thsse database objects in a script level, grouped by server,database,schema with sourted information.
So, we prepare all these raw information in TCustomSqlStatement into a new class: TLzDBObject, along with TLzDBObjectList, after that you have a more convenience way to access database object in sql scripts.
For how to achieve this, please check this demo: