GetTablesFromSQLStatement

The GetTablesFromSQLStatement method parses the passed SQL statement and returns a collection of the table names referenced in the statement. This is more powerful than the GetTableFromSQLStatement, which returns only the table referenced in the FROM clause.

Parameters
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 ;
}