Once a sql was parsed sucessfuly, all information are ready in the parse tree. So, it's very easy to fetch table and field information in the sql.
There is a key property in TSourceToken which is very useful to identify table or field: DBObjType :TDBObjType,
ttObjTable: table, ttObjTableAlias: table alias, ttObjField: field, ttObjFieldAlias: field alias.
This property is widely used when you find table and field in sql.
Quick access database object: Use properties in TGSqlParser.
Download this demo
We have introduced new properties in TGSqlParser to help you quick access database objects in sql,
especially,relationship between field and corresponding table is established.
These properties are: DBObjectList,TableList,FieldList,FunctionList,ProcedureList and TriggerList. Check
help file for detail information about those properties.
Take this sql for example:
select t1.f1,f2
from t1,t2
where t1.f3 = t2.f4
Output from following demo(in C#, other demos in VC, VB, and Delphi can be found in shipping demos ) like this:
Tables:
t1
t2
Fields:
t1.f1
t1.f3
t2.f2
t2.f4
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using gudusoft.gsqlparser;
using gudusoft.gsqlparser.Units;
namespace getdbobject
{
class getdbobject
{
static TGSqlParser sqlparser;
protected static void getdbobjectstr(TLzDBObjectList pdbobjectlist)
{
foreach(TLzDBObject obj in pdbobjectlist){
Console.WriteLine(obj.ObjectFullName);
}
}
static void Main(string[] args)
{
string inputfile = "";
string lcsqltext = "";
if (args.Length == 0)
{
Console.WriteLine("{0} scriptfile","getdbobject");
return;
}
inputfile = args[0];
//Reading from file
try
{
StreamReader re = File.OpenText(inputfile); //new StreamReader(inputfile,new UnicodeEncoding());//
// Console.WriteLine("The encoding used was {0}.", re.CurrentEncoding);
string input = null;
while ((input = re.ReadLine()) != null)
{
lcsqltext = lcsqltext + Environment.NewLine + input;
// Console.WriteLine(input);
}
}
catch (Exception e)
{
// Let the user know what went wrong.
Console.WriteLine("File could not be read:");
Console.WriteLine(e.Message);
return;
}
Console.WriteLine("Initialization,please wait...");
sqlparser = new TGSqlParser(TDbVendor.DbVMssql);
sqlparser.SqlText.Text = lcsqltext;// "select f1,f2,fx(f3) from t1 where f3 > any (select f4,t2.f5 from t2)";
Console.WriteLine("Processing..");
int i2 = sqlparser.Parse();
if (i2 == 0)
{
Console.WriteLine("tables:");
getdbobjectstr(sqlparser.TableList);
Console.WriteLine("fields:");
foreach (TLzDBObject lctable in sqlparser.TableList)
{
foreach (TLzDBObject lcfield in lctable.ChildDBObjects)
{
Console.WriteLine(lctable.ObjectFullName+'.'+lcfield.ObjectName);
}
}
if (sqlparser.ServerList.Count() > 0)
{
Console.WriteLine("Server:");
getdbobjectstr(sqlparser.ServerList);
}
if (sqlparser.DatabaseList.Count() > 0)
{
Console.WriteLine("Database:");
getdbobjectstr(sqlparser.DatabaseList);
}
if (sqlparser.SchemaList.Count() > 0)
{
Console.WriteLine("Schema:");
getdbobjectstr(sqlparser.SchemaList);
}
if (sqlparser.FunctionList.Count() > 0)
{
Console.WriteLine("Functions:");
getdbobjectstr(sqlparser.FunctionList);
}
if (sqlparser.IndexList.Count() > 0)
{
Console.WriteLine("Index:");
getdbobjectstr(sqlparser.IndexList);
}
if (sqlparser.ViewList.Count() > 0)
{
Console.WriteLine("view:");
getdbobjectstr(sqlparser.ViewList);
}
if (sqlparser.TriggerList.Count() > 0)
{
Console.WriteLine("Triggers:");
getdbobjectstr(sqlparser.TriggerList);
}
}
else
Console.WriteLine(sqlparser.ErrorMessages);
}
}
}
Use SourceTokenList of TGSqlParser to fetch table and field of all sql statements
Download this demo
using System;
using System.Collections.Generic;
using System.Text;
using gudusoft.gsqlparser;
using gudusoft.gsqlparser.Units;
namespace fetchdbobj_demo1
{
class fetchdb_demo1
{
static void Main(string[] args)
{
/* These C# code was generated from SQL automatically by SQL Pretty Printer at www.wangz.net */
StringBuilder inputsql1 = new StringBuilder("SELECT c.FirstName + ' ' + c.LastName AS Employee,\n");
inputsql1.Append(" Datepart(MONTH,Soh.OrderDate) AS OrderMonthnum,\n");
inputsql1.Append(" ps.Name AS SubCat,\n");
inputsql1.Append(" SUM(Sod.LineTotal) AS Sales,\n");
inputsql1.Append(" Soh.SaleSorderNumber,\n");
inputsql1.Append(" p.Name AS Product,\n");
inputsql1.Append(" SUM(Sod.Orderqty) AS Orderqty,\n");
inputsql1.Append(" Sod.UnitPrice,\n");
inputsql1.Append(" pc.Name AS ProdCat\n");
inputsql1.Append(" FROM Sales.SaleSorderHeader Soh\n");
inputsql1.Append(" INNER JOIN Sales.SaleSorderDetail Sod\n");
inputsql1.Append(" ON Soh.SaleSordeRid = Sod.SaleSordeRid\n");
/* End of code generated by SQL Pretty Printer at www.wangz.net */
TGSqlParser sqlparser = new TGSqlParser(TDbVendor.DbVMssql);
sqlparser.SqlText.Text = inputsql1.ToString();
int ret = sqlparser.Parse();
if (ret == 0)
{
for (int k = 0; k < sqlparser.SourceTokenList.Count(); k++)
{
TSourceToken st = sqlparser.SourceTokenList[k];
if ((st.DBObjType == TDBObjType.ttObjField) || (st.DBObjType == TDBObjType.ttObjFieldAlias))
{
Console.WriteLine("Field or Field alias: " + st.AsText+"("+st.XPosition+","+st.YPosition+")");
}
if ((st.DBObjType == TDBObjType.ttObjTable) || (st.DBObjType == TDBObjType.ttObjTableAlias))
{
Console.WriteLine("Table or Table alias: " + st.AsText + "(" + st.XPosition + "," + st.YPosition + ")");
}
}
}
else
{
Console.WriteLine(sqlparser.ErrorMessages);
}
return;
}
}
}
Use SourceTokenList of TCustomSqlStatement to fetch table and field of each sql statement
Download this demo
using System;
using System.Collections.Generic;
using System.Text;
using gudusoft.gsqlparser;
using gudusoft.gsqlparser.Units;
namespace fetchdbobj_demo2
{
class fetchdb_demo2
{
static void Main(string[] args)
{
/* These C# code was generated from SQL automatically by SQL Pretty Printer at www.wangz.net */
StringBuilder inputsql1 = new StringBuilder("SELECT c.FirstName + ' ' + c.LastName AS Employee,\n");
inputsql1.Append(" Datepart(MONTH,Soh.OrderDate) AS OrderMonthnum,\n");
inputsql1.Append(" ps.Name AS SubCat,\n");
inputsql1.Append(" SUM(Sod.LineTotal) AS Sales,\n");
inputsql1.Append(" Soh.SaleSorderNumber,\n");
inputsql1.Append(" p.Name AS Product,\n");
inputsql1.Append(" SUM(Sod.Orderqty) AS Orderqty,\n");
inputsql1.Append(" Sod.UnitPrice,\n");
inputsql1.Append(" pc.Name AS ProdCat\n");
inputsql1.Append(" FROM Sales.SaleSorderHeader Soh\n");
inputsql1.Append(" INNER JOIN Sales.SaleSorderDetail Sod\n");
inputsql1.Append(" ON Soh.SaleSordeRid = Sod.SaleSordeRid;\n");
inputsql1.Append(" select a,b from c;\n");
/* End of code generated by SQL Pretty Printer at www.wangz.net */
TGSqlParser sqlparser = new TGSqlParser(TDbVendor.DbVMssql);
sqlparser.SqlText.Text = inputsql1.ToString();
int ret = sqlparser.Parse();
if (ret == 0)
{
for (int l = 0; l < sqlparser.SqlStatements.Count(); l++)
{
TCustomSqlStatement sqlstmt = sqlparser.SqlStatements[l];
Console.WriteLine("\nfetch table and field from statement: "+l);
for (int k = 0; k < sqlstmt.SourceTokenList.Count(); k++)
{
TSourceToken st = sqlstmt.SourceTokenList[k];
if ((st.DBObjType == TDBObjType.ttObjField) || (st.DBObjType == TDBObjType.ttObjFieldAlias))
{
Console.WriteLine("Field or Field alias: " + st.AsText + "(" + st.XPosition + "," + st.YPosition + ")");
}
if ((st.DBObjType == TDBObjType.ttObjTable) || (st.DBObjType == TDBObjType.ttObjTableAlias))
{
Console.WriteLine("Table or Table alias: " + st.AsText + "(" + st.XPosition + "," + st.YPosition + ")");
}
}
}
}
else
{
Console.WriteLine(sqlparser.ErrorMessages);
}
return;
}
}
}
Field,Table in Select statement
You can fetch all fields information in property Fields : TLzFieldList of TCustomSqlStatement such as field name, field alias and prefix of the field.
All table informations are in two properties of Tables : TLzTableList of TCustomSqlStatement and
JoinTables : TlzJoinlist of TSelectSqlStatement, Tables save all table information in
a flat list structure, so it's much more easy to fetch table information from Tables property. While JoinTables save the hierarchical information of tables in
from clause, more info about JoinTables you can check
How to use join table
This demo shows how to find out what's a string means in the sql.
Download this demo
using System;
using System.Collections.Generic;
using System.Text;
using gudusoft.gsqlparser;
using gudusoft.gsqlparser.Units;
namespace getdbobject2
{
class getdbobject2
{
static TSourceTokenList lcTableTokens = new TSourceTokenList(false);
static TSourceTokenList lcFieldTokens = new TSourceTokenList(false);
static void Main(string[] args)
{
/* These C# code was generated from SQL automatically by SQL Pretty Printer at www.wangz.net */
StringBuilder inputsql1 = new StringBuilder("SELECT c.FirstName + ' ' + c.LastName AS Employee,\n");
inputsql1.Append(" Datepart(MONTH,Soh.OrderDate) AS OrderMonthnum,\n");
inputsql1.Append(" ps.Name AS SubCat,\n");
inputsql1.Append(" SUM(Sod.LineTotal) AS Sales,\n");
inputsql1.Append(" Soh.SaleSorderNumber,\n");
inputsql1.Append(" p.Name AS Product,\n");
inputsql1.Append(" SUM(Sod.Orderqty) AS Orderqty,\n");
inputsql1.Append(" Sod.UnitPrice,\n");
inputsql1.Append(" pc.Name AS ProdCat\n");
inputsql1.Append(" FROM Sales.SaleSorderHeader Soh\n");
inputsql1.Append(" INNER JOIN Sales.SaleSorderDetail Sod\n");
inputsql1.Append(" ON Soh.SaleSordeRid = Sod.SaleSordeRid\n");
inputsql1.Append(" INNER JOIN Sales.SalesPerson sp\n");
inputsql1.Append(" ON Soh.SalesPersonId = sp.SalesPersonId\n");
inputsql1.Append(" INNER JOIN HumanResources.Employee e\n");
inputsql1.Append(" ON sp.SalesPersonId = e.EmployeeId\n");
inputsql1.Append(" INNER JOIN Person.Contact c\n");
inputsql1.Append(" ON e.ContactId = c.ContactId\n");
inputsql1.Append(" INNER JOIN Production.Product p\n");
inputsql1.Append(" ON Sod.ProductId = p.ProductId\n");
inputsql1.Append(" INNER JOIN Production.ProductSubCategory ps\n");
inputsql1.Append(" ON p.ProductSubCategoryId = ps.ProductSubCategoryId\n");
/* following sql code was commented in order to make this demo works under
* trail version of general sql parser which has a query size limitation of 1000 characters
inputsql1.Append(" INNER JOIN Production.ProductCategory pc\n");
inputsql1.Append(" ON ps.ProductCategoryId = pc.ProductCategoryId\n");
inputsql1.Append(" WHERE (Datepart(YEAR,Soh.OrderDate) = @ReportYear)\n");
inputsql1.Append(" AND (Datepart(MONTH,Soh.OrderDate) = @ReportMonth)\n");
inputsql1.Append(" AND (Soh.SalesPersonId = @EmpID)\n");
inputsql1.Append(" GROUP BY c.FirstName + ' ' + c.LastName,Datepart(MONTH,Soh.OrderDate),\n");
inputsql1.Append(" Soh.SaleSorderNumber,p.Name,ps.Name,Sod.UnitPrice,\n");
inputsql1.Append(" pc.Name\n");
*/
/* End of code generated by SQL Pretty Printer at www.wangz.net */
String searchstr = "SubCat"; //replace this value to what you like to search in the sql
TGSqlParser sqlparser = new TGSqlParser(TDbVendor.DbVMssql);
sqlparser.SqlText.Text = inputsql1.ToString();
int ret = sqlparser.Parse();
if (ret == 0)
{
TSelectSqlStatement sqlstmt;
sqlstmt = (TSelectSqlStatement)sqlparser.SqlStatements[0];
// check searchstr is table or table alias
Console.WriteLine("Tables in this sql: " + sqlstmt.Tables.Count());
searchtable(sqlstmt.Tables, searchstr, true);
// check field or field alias
Console.WriteLine("Fields in this sql: " + sqlstmt.Fields.Count());
searchfield(sqlstmt.Tables, sqlstmt.Fields, searchstr);
}
else
{
Console.WriteLine(sqlparser.ErrorMessages);
}
return;
}
public static void searchtable(TLzTableList pTables, String pStr, Boolean listTable)
{
for (int k = 0; k < pTables.Count(); k++)
{
if (listTable)
Console.WriteLine("prefix: " + pTables[k].TablePrefix.ToString() + "\tname: " + pTables[k].TableName.ToString() + "\t\t alias: " + pTables[k].TableAlias.ToString());
if (pTables[k].TableName.ToString().ToLower().CompareTo(pStr.ToLower()) == 0)
{
Console.WriteLine(pStr + " is table name with prefix: " + pTables[k].TablePrefix.ToString());
}
if (pTables[k].TableAlias.ToString().ToLower().CompareTo(pStr.ToLower()) == 0)
{
Console.WriteLine(pStr + " is table alias of table " + pTables[k].TableName.ToString() + " with prefix " + pTables[k].TablePrefix.ToString());
}
}
}
public static void searchfield(TLzTableList pTables, TLzFieldList pFields, String pStr)
{
for (int k = 0; k < pFields.Count(); k++)
{
Console.WriteLine("prefix: " + pFields[k].FieldPrefix.ToString() + "\tname: " + pFields[k].FieldName.ToString() + "\talias: " + pFields[k].FieldAlias.ToString());
if (pFields[k].FieldName.ToString().ToLower().CompareTo(pStr.ToLower()) == 0)
{
Console.WriteLine(pStr + " is field name with prefix " + pFields[k].FieldPrefix.ToString());
if (pFields[k].FieldPrefix.ToString().Length > 0)
{
searchtable(pTables, pFields[k].FieldPrefix.ToString(), false);
}
fielddetail(pTables, pFields[k]);
}
if (pFields[k].FieldAlias.ToString().ToLower().CompareTo(pStr.ToLower()) == 0)
{
Console.WriteLine(pStr + " is field alias of " + pFields[k].FieldName.ToString() + " with prefix " + pFields[k].FieldPrefix.ToString());
if (pFields[k].FieldPrefix.ToString().Length > 0)
{
searchtable(pTables, pFields[k].FieldPrefix.ToString(), false);
}
fielddetail(pTables, pFields[k]);
}
}
}
public static void fielddetail(TLzTableList pTables, TLzField pField)
{
switch (pField.FieldType)
{
case TLzFieldType.lftExpression:
if (pField.FieldExpr.oper == TLzOpType.Expr_FuncCall) //field is a function
{
TLz_FuncCall func;
func = (TLz_FuncCall)pField.FieldExpr.lexpr;
Console.WriteLine("Function: " + func.FunctionName);
// check arguments of this function
for (int k = 0; k < func.args.Count(); k++)
{
if (func.args[k] is TSourceToken)
{
TSourceToken st = (TSourceToken)func.args[k];
if ((st.DBObjType == TDBObjType.ttObjField) || (st.DBObjType == TDBObjType.ttObjFieldAlias))
{
Console.WriteLine("Field: " + st.AsText);
}
if ((st.DBObjType == TDBObjType.ttObjTable) || (st.DBObjType == TDBObjType.ttObjTableAlias))
{
Console.WriteLine("Table: " + st.AsText);
searchtable(pTables, st.AsText, false);
}
}
else
{
lcTableTokens.Clear();
lcFieldTokens.Clear();
GetDbObjectsFromNode((TLz_Node)(func.args[k]));
for (int l = 0; l < lcTableTokens.Count(); l++)
{
Console.WriteLine("Table: " + lcTableTokens[l].AsText);
searchtable(pTables, lcTableTokens[l].AsText, false);
}
for (int m = 0; m < lcFieldTokens.Count(); m++)
{
Console.WriteLine("Field: " + lcFieldTokens[m].AsText);
}
}
}
}
break;
case TLzFieldType.lftSubquery:
Console.WriteLine("subquery");
TSelectSqlStatement st1;
st1 = (TSelectSqlStatement)pField.SubQuery;
Console.WriteLine(st1.AsText);
break;
}
}
public static void GetDbObjectsFromNode(TLz_Node pNode)
{
//Fetch table and field objects into lcTableTokens,lcFieldTokens
lcTableTokens.Clear();
lcFieldTokens.Clear();
TLzGetDbObjectsVisitor av = new TLzGetDbObjectsVisitor();
av.ParseTree = pNode;
av.TableTokens = lcTableTokens;
av.FieldTokens = lcFieldTokens;
av.Doit();
}
}
}
Find out table and field from any parse tree node from TLz_Node to TCustomSqlStatement
There is a class: TLzGetDbObjectsVisitor in general sql parser which is very useful to find out table and field information from any parse tree node.
TLzGetDbObjectsVisitor av = new TLzGetDbObjectsVisitor();
av.ParseTree = pNode; //pNode is any parse tree node that is all decendant class of TLz_Node
av.TableTokens = lcTableTokens; // type of TSourceTokenList which is used to store all SourceTokens that represent table or table alias
av.FieldTokens = lcFieldTokens; // type of TSourceTokenList which is used to store all SourceTokens that represent field or field alias
av.Doit();
Since all parse tree node except TSourceToken are all decendant class of TLz_Node, so you can get all table and information from the parse tree node
by using TLzGetDbObjectsVisitor. After execute Doit(), all table and field source token will be in corresponding source token list.
What's really a headache is that the field and table itself is subquery
Don't worry, General SQL Parser is powerful enough to deal such issue. If you need help, please contact us.
COPYRIGHT (C) 2001-2011 sqlparser.com , privacy policy ALL RIGHT RESERVED
