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