Select - Stonefield Query SDK

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.

A GetSQL script is preferred to a Select script if possible. Select scripts cause a query to be split into individual tables when a report is run, which can make a report run slower, especially with tables with a lot of records. A GetSQL script can potentially be significantly faster since the query isn't split in that case but instead the returned SQL statement is used as a subquery for the main SQL statement for the report.

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

You can't have both GetSQL and Select scripts for a table. Creating one disables the "create" link for the other.

Note that Stonefield Query passes a SQL statement to Select scripts. The field names are unaliased in this statement. Your code must respect the field names passed in the statement. For example, if the user selects a field called CustomerID from the Customers table and another field also called 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
local lcSelect
lcSelect = 'select CustomerID, CustomerName from Customers'
if not empty(toDatabase.ExecuteSQLStatement(lcSelect, ;
  .NULL., 'MyVirtualTable'))
  &tcSelect into cursor (tcCursor)
  return .T.
else
  return .F.
endif

Your code should also respect the report's filter conditions. There are two ways to do that. One is to parse the WHERE clause from the SQL statement passed as a parameter and use it when constructing the result set. For example:

lparameters toApplication as SQApplication, toDatabase as Database, ;
  tcSelect, tcCursor
lcSelect = 'select * from Orders'
lnPos = atc(' where ', tcSelect)
if lnPos > 0
  lcWhere = substr(tcSelect, lnPos + 7)
  lcSelect = toApplication.DataEngine.AddToWhere(lcSelect, lcWhere)
endif
toDatabase.ExecuteSQLStatement(lcSelect, , tcCursor)

The other is to construct the result set without regard to the report's filter, then use a final SQL statement to return the result set with the desired fields and filter conditions. However, if you query an ODBC database engine, such as SQL Server, the filter conditions may use a different syntax than Stonefield Query does. This is particularly the case for filter conditions involving date, datetime, and logical values. The ODBCFilterToLocalFilter method of the DataEngine object converts the WHERE clause in a SQL statement from ODBC syntax to local Stonefield Query syntax. For example:

lparameters toApplication as SQApplication, toDatabase as Database, ;
  tcSelect, tcCursor
lcSelect = 'select * from Orders'
lcCursor = sys(2015)
toDatabase.ExecuteSQLStatement(lcSelect, , lcCursor)
lcSelect = toApplication.DataEngine.ODBCFilterToLocalFilter(tcSelect)
lcSelect = strtran(lcSelect, 'Orders', lcCursor, -1, -1, 1)
&lcSelect into cursor (tcCursor)

A Select script may be called from either a query or when the user clicks the Values button. The script can tell the difference 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 Visual FoxPro, True if the script created a cursor with the name specified in the fourth parameter. For other languages, an XML string containing the data for the table. Stonefield Query is somewhat flexible in the format of the XML, but the elements do have to be named the same as the fields in the report. For example, you can return the XML created by the WriteXML method of an ADO.Net DataTable.

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

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

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

Please note that the method in this script must be named table_Select where table is the name of the table item in your data dictionary. The sample below is a Select script for the SalesByYear table.

public static string Salesbyyear_Select(SFQApplication sfqApplication, 
  Database database, string selectStatement, string cursorName)
{
  string fieldName, results;
  Values enteredValues;

  fieldName = "SalesByYear.ShippedDate";
  enteredValues = sfqApplication.DataEngine.GetValuesForField(fieldName, "between");
  results = String.Empty;

  if (enteredValues.Count == 2) 
  {
    results = database.ExecuteSQLStatement("exec [Sales By Year] ?Start, ?End", 
      enteredValues) ;
  }

  return results;
}

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

public shared function Salesbyyear_Select(sfqApplication as SFQApplication,
  database as Database, selectStatement as string, cursorName as string) as string

  dim fieldName, results as String
  dim enteredValues as Values

  fieldName = "SalesByYear.ShippedDate"
  enteredValues = sfqApplication.DataEngine.GetValuesForField(fieldName, "between")
  results = String.Empty

  if enteredValues.Count = 2 then
    results = database.ExecuteSQLStatement("exec [Sales By Year] ?Start, ?End",
      enteredValues)
  end if

  return results
End Function

Visual FoxPro
Here's a more complex example. It calls the same Sales by Year stored procedure but automatically handles any existing filter condition. Also, it doesn't add parameters to the statement 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