Although Stonefield Query automatically retrieves the data required for a query from a table, there may be times when you want to script this behavior instead. For example, if you want to call a stored procedure or a Web Service, or if the table is a virtual table rather than a real one, you have to tell Stonefield Query how to retrieve the data.

This script is specific for a table, so its actual name is table.Select, where table is the name of the table. The easiest way to create this script is to click the Create 'data access' script link when the table is selected in the TreeView.

You'll get the best performance if you use Visual FoxPro for the script language and have it create a cursor for the data. The name of the cursor Stonefield Query expects this script to create is passed as one of the parameters. If you use VBScript or JavaScript, your script has to return the data as XML or an ADO RecordSet, and Stonefield Query has to process the XML or RecordSet, which takes extra time.

Note that Stonefield Query passes a SQL statement to Select scripts. Your code must respect the field names passed in that statement. For example, if the user selects a field call CustomerID from the Customers table and CustomerID from your virtual table, the SQL statement passed to your Select script will likely rename the CustomerID field since there can't be two fields in the result set with the same name. In that case, the SQL statement may be something like:

select CustomerID as CustomerID_A from MyVirtualTable

Your code must ensure the result set it returns to Stonefield Query respects the names Stonefield Query expects. Rather than parsing the SQL statement passed to your script, an easy way to handle that is to execute the passed SQL statement against the result set you created. In order for this to work, put your result set into a cursor named the same as your virtual table and execute the passed statement into the cursor whose name is specified in the last parameter. Here's an example:

lparameters toApplication as SQApplication, toDatabase as Database, ;
  tcSelect, tcCursor
if not empty(toDatabase.ExecuteSQLStatement('select CustomerID, CustomerName from Customers', ;
  .NULL., 'MyVirtualTable'))
  &tcSelect into cursor (tcCursor)
  return .T.
else
  return .F.
endif

A Select script can detect if it's being called from a query versus the Values button by checking for the existence of a variable called loQuery using code like:

if type('loQuery') = 'O'
* we're called from the Values button
endif

You can then conditionally code different behavior if necessary.

Parameters
A reference to the Stonefield Query Application object, a reference to the Database object the table belongs to, the SQL statement Stonefield Query created for the table, and the name of the cursor to create (only useful for Visual FoxPro scripts).

Return Value
For VBScript and JavaScript, an XML string containing the data for the table. For Visual FoxPro, True if the script created a cursor with the name specified in the fourth parameter.

Example

Visual FoxPro
This example calls a stored procedure named SalesByCategory to get the data for the table.

lparameters toApplication as SQApplication, toDatabase as Database, ;
  tcSelect, tcCursor
local loValues, loValue, lcCategory, lcYear, lcSelect, ;
  llReturn
loValues = toApplication.DataEngine.GetValuesForField('Categories.CategoryName', ;
  'equals')
if loValues.Count > 0
  loValue = loValues.Item(1)
  lcCategory = ['] + loValue.Value + [']
  loValues = toApplication.DataEngine.GetValuesForParameter('Year', ;
    'C', 4, 0, 'equals')
  if loValues.Count > 0
    loValue = loValues.Item(1)
    lcYear = ['] + loValue.Value + [']
    lcSelect = 'exec SalesByCategory ' + lcCategory + ', ' + lcYear
    llReturn = not empty(toDatabase.ExecuteSQLStatement(lcSelect, ;
      .NULL., tcCursor))
  endif
endif
return llReturn

The code starts by calling the GetValuesForField method of the DataEngine object of the passed Application object. This method displays a dialog from which the user can enter a filter value for a field; this value is passed to the stored procedure this code calls. GetValuesForField returns a values collection, so if the user entered any values, the first item in the collection is retrieved. Next, the GetValuesForParameter method is called. This method is similar to GetValuesForField, except it prompts the user for a parameter value that isn't associated with a field. If the user chose a value, the first item in the values collection is retrieved, and the two parameter values are passed to the SalesByCategory stored procedure by calling the ExecuteSQLStatement method of the passed Database object. Passing the name of the cursor to create to ExecuteSQLStatement creates a Visual FoxPro cursor, so the script returns True if ExecuteSQLStatement succeeded or False if not.

VBScript
This example calls a stored procedure named Sales By Year to get the data for the table.

function Main(Application, Database, SelectStatement, _
  CursorName)
FieldName = "SalesByYear.ShippedDate"
dim Values
set Values = Application.DataEngine.GetValuesForField(FieldName, _
  "between")
if Values.Count = 2 then
  Main = Database.ExecuteSQLStatement("exec [Sales By Year] " & _
    "?Start, ?End", Values)
else
  Main = ""
end if
end function

The code starts by calling the GetValuesForField method of the DataEngine object of the passed Application object. This method displays a dialog from which the user can enter a filter value for a field; in this case, since we're using the "between" operator, the user enters two values. GetValuesForField returns a values collection, which is then passed to the ExecuteSQLStatement method of the passed Database object. This method calls the Sales By Year stored procedure, passing the values in place of the ?Start and ?End parameters. ExecuteSQLStatement returns the result set as XML, which this script simply returns to Stonefield Query.

JavaScript
This example calls a stored procedure named Sales By Year to get the data for the table.

function Main(Application, Database, SelectStatement, 
  CursorName) {
var FieldName, Values, Results ;
FieldName = 'SalesByYear.ShippedDate' ;
Values = Application.DataEngine.GetValuesForField(FieldName, 
  'between') ;
Results = '' ;
if (Values.Count = 2) {
  Results = Database.ExecuteSQLStatement('exec [Sales By Year] ' +
    '?Start, ?End', Values) ;
}
return Results ;
}

The code starts by calling the GetValuesForField method of the DataEngine object of the passed Application object. This method displays a dialog from which the user can enter a filter value for a field; in this case, since we're using the "between" operator, the user enters two values. GetValuesForField returns a values collection, which is then passed to the ExecuteSQLStatement method of the passed Database object. This method calls the Sales By Year stored procedure, passing the values in place of the ?Start and ?End parameters. ExecuteSQLStatement returns the result set as XML, which this script simply returns to Stonefield Query.

Visual FoxPro
Here's a more complex example. It calls the same Sales by Year stored procedue but automatically handles any existing filter condition. Also, it doesn't add para to script but expects the values collection passed to ExecuteSQLStatement to take care of it:

lparameters toApplication as SQApplication, toDatabase as Database, ;
  tcSelect, tcCursor
local loFilters as FilterConditions, lnI, loFilter as FilterCondition, ;
  ldStartDate, ldEndDate, loGetValues, loGetValue, loValues, lcSelect, ;
  llReturn

* If a date range filter was specified for ShippedDate field, use it.

loFilters = toApplication.DataEngine.FilterConditions
for lnI = 1 to loFilters.Count
  loFilter = loFilters.Item(lnI)
  do case
    case lower(loFilter.FieldName) <> 'salesbyyear.shippeddate'
    case loFilter.Operator = 'is between' and loFilter.ValueType = 'Expression'
      ldStartDate = evaluate(loFilter.Values.Item(1))
      ldEndDate   = evaluate(loFilter.Values.Item(2))
    case loFilter.Operator = 'is between'
      ldStartDate = loFilter.Values.Item(1)
      ldEndDate   = loFilter.Values.Item(2)
    case loFilter.Operator = 'is greater than' and loFilter.ValueType = 'Expression'
      ldStartDate = evaluate(loFilter.Values.Item(1))
    case loFilter.Operator = 'is greater than'
      ldStartDate = loFilter.Values.Item(1)
    case loFilter.Operator = 'is less than' and loFilter.ValueType = 'Expression'
      ldEndDate = evaluate(loFilter.Values.Item(1))
    case loFilter.Operator = 'is less than'
      ldEndDate = loFilter.Values.Item(1)
  endcase
next

* If we don't have a complete (or any) date range, prompt the user for the missing
* value(s).

do case
  case empty(ldStartDate) and empty(ldEndDate)
    loGetValues = toApplication.DataEngine.GetValuesForParameter('Date Range', ;
      'D', 8, 0, 'between')
    if loGetValues.Count > 0
      loGetValue  = loGetValues.Item(1)
      ldStartDate = loGetValue.Value
      loGetValue  = loGetValues.Item(2)
      ldEndDate   = loGetValue.Value
    endif
  case empty(ldStartDate)
    loGetValues = toApplication.DataEngine.GetValuesForParameter('Starting Date', ;
      'D', 8, 0, 'equals')
    if loGetValues.Count > 0
      loGetValue  = loGetValues.Item(1)
      ldStartDate = loGetValue.Value
    endif
  case empty(ldEndDate)
    loGetValues = toApplication.DataEngine.GetValuesForParameter('Ending Date', ;
      'D', 8, 0, 'equals')
    if loGetValues.Count > 0
      loGetValue = loGetValues.Item(1)
      ldEndDate  = loGetValue.Value
    endif
endcase

* If we have both dates, create a values collection and add the parameters for the query to it.

if not empty(ldStartDate) and not empty(ldEndDate)
  loValues = toApplication.GetValuesCollection()
  loValues.AddItem('Beginning_Date', ldStartDate)
  loValues.AddItem('Ending_Date',    ldEndDate)

* Execute the desired stored procedure.

  lcSelect = 'exec [Sales by Year]'
  llReturn = not empty(toDatabase.ExecuteSQLStatement(lcSelect, ;
    loValues, tcCursor))
endif
return llReturn

The code starts by going through the conditions in the filter for the report (contained in the FilterConditions collection), looking for those involving SalesByYear.ShippedDate. It puts the values for those conditions into the ldStartDate and ldEndDate variables. If we don't have a complete date range (for example, "is greater than" or "is less than" is used, the code prompts the user for the missing value. It then calls GetValuesCollection to create a collection of value objects and add the starting and ending dates to the collection. Finally, it calls ExecuteSQLStatement to execute the stored procedure, passing it the values collection to use as the parameters for the stored procedure.

See Also
Table Properties