The GetTablesFromSQLStatement method parses the passed SQL statement and returns a collection of the table names referenced in the statement.
Syntax
GetTablesFromSQLStatement(SQLStatement as String) as Collection
Parameters
SQLStatement
The SQL statement to parse.
Return Value
A collection of table names.
Example
This example shows code that could be used in the DataEngine.FinalizeSQLStatement script to automatically add a sales person ID to the filter of any report referencing the SALESHISTORY table.
Visual FoxPro
lparameters toApplication as SQApplication, tcSelect
local lcSalespersonID, loTables, lcTable, lcSelect
lcSalespersonID = toApplication.GetRegistryValue('ID', '', ;
'Software\MyCompany\SalesApplication')
loTables = toApplication.DataEngine.GetTablesFromSQLStatement(tcSelect)
for each lcTable in loTables
if upper(lcTable) = 'SALESHISTORY'
lcSelect = toApplication.DataEngine.AddToWhere(tcSelect, ;
"SalesHistory.SalespersonID = '" + lcSalespersonID + "'")
exit
endif
next
return lcSelect
VBScript
function Main(Application, SelectStatement)
SalespersonID = Application.GetRegistryValue("ID", "", _
"Software\MyCompany\SalesApplication")
set Tables = Application.DataEngine.GetTablesFromSQLStatement(SelectStatement)
for each Table in Tables
if ucase(Table) = "SALESHISTORY" then
Main = Application.DataEngine.AddToWhere(SelectStatement, _
"SalesHistory.SalespersonID = " + chr(34) + SalespersonID + chr(34))
end if
next
end function
JavaScript
function Main(Application, SelectStatement) {
var SalespersonID, Tables, Table, ReturnValue ;
SalespersonID = Application.GetRegistryValue('ID', ' ',
'Software\MyCompany\SalesApplication') ;
Tables = Application.DataEngine.GetTablesFromSQLStatement(SelectStatement) ;
for (Table in Tables) {
if Table = 'SALESHISTORY'
ReturnValue = Application.DataEngine.AddToWhere(SelectStatement,
'SalesHistory.SalespersonID = "' + SalespersonID + '"') ;
}
return ReturnValue ;
}
C#
public static string DataEngine_FinalizeSQLStatement(SFQApplication sfqApplication,
string selectStatement)
{
string salespersonID;
string returnValue = String.Empty;
salespersonID = sfqApplication.GetRegistryValue("ID", " ",
@"HKEY_CURRENT_USER\Software\MyCompany\SalesApplication");
Tables tables = sfqApplication.DataEngine.GetTablesFromSQLStatement(selectStatement);
foreach(Table table in tables)
{
if (table.Alias.ToUpper() == "SALESHISTORY")
{
returnValue = sfqApplication.DataEngine.AddToWhere(selectStatement,
"SalesHistory.SalespersonID = '" + salespersonID + "'") ;
}
}
return returnValue;
}
VB.NET
public shared function DataEngine_FinalizeSQLStatement(sfqApplication as
SFQApplication, selectStatement as string) as string
Dim salespersonID as string
Dim returnValue as string = String.Empty
salespersonID = sfqApplication.GetRegistryValue("ID", " ",
"HKEY_CURRENT_USER\Software\MyCompany\SalesApplication")
Dim tables as Tables = _
sfqApplication.DataEngine.GetTablesFromSQLStatement(selectStatement)
for each table as Table in tables
if table.Alias.ToUpper() = "SALESHISTORY"
returnValue = sfqApplication.DataEngine.AddToWhere(selectStatement, _
"SalesHistory.SalespersonID = '" + salespersonID + "'")
End If
Next
return returnValue
End Function
See also
DataEngine Object | GetFieldsFromSQLStatement© Stonefield Software Inc., 2023 • Updated: 01/02/19
Comment or report problem with topic