ExecuteSQLStatement

As its name implies, the ExecuteSQLStatement method sends a SQL statement to the current data source for the Database object. This method can be called from the Select script of a table to call a stored procedure in the database, from a script called from the Output Expression property of a calculated field to obtain some intermediary results, or from any other script where you need to retrieve some data from the database. You can pass any valid SQL statement to ExecuteSQLStatement.

If you want to use a parameterized statement, you have one of two choices:

You must specify parameters as ?ParameterName. Each parameter name must be unique and a valid name (it must start with a letter and only contain letters, digits, and underscores). Also, the parameter variable must be in scope, which means you need to use Visual FoxPro code rather than VBScript or JavaScript code and the parameter variable must not be declared LOCAL. Here's an example:
```
private custIDValue
CustIDValue = 'some value'
DateOrdered = date(2008, 02, 29)
Stmt = "select * from Orders where CustomerID=?CustIDValue and OrderDate=?DateOrdered"
Database.ExecuteSQLStatement(Stmt)
```

Alternatively, you can populate the parameters collection passed to ExecuteSQLStatement, in which case the restrictions about scope and script language are irrelevant. In that case, specify each parameter as simply a "?". For example:

Parameters = SQApplication.GetValuesCollection()
Parameters.AddItem('CustIDValue', 'some value')
Parameters.AddItem('DateOrdered', date(2008, 02, 29))
Stmt = "select * from Orders where CustomerID=? and OrderDate=?"
Database.ExecuteSQLStatement(Stmt, Parameters)

For stored procedures that expect input parameters, specify the stored procedure name and pass the parameters collection for the parameters to pass to that stored procedure. For example:

Stmt = "exec MyProcedure"
Parameters = SQApplication.GetValuesCollection()
Parameters.AddItem('ParameterName1', 'Value of parameter 1')
Parameters.AddItem('ParameterName2', 'Value of parameter 2')
Database.ExecuteSQLStatement(Stmt, Parameters)

Syntax

ExecuteSQLStatement(SQLStatement as String
  [, ParametersCollection as Object]
  [, CursorName as String]) as String

Parameters
SQLStatement
The SQL statement to execute.

ParametersCollection
If you use a parameterized SQL statement or call a stored procedure that receives parameters, ExecuteSQLStatement needs a source for the values of the parameters. The ParametersCollection parameter is that source. It's a collection of value objects. A value object has a single property, Value, which contains the value for a parameter. The GetValuesCollection method of the Application object and the GetValuesForField and GetValuesForParameter methods of the DataEngine object return such a collection.

CursorName
The name of a cursor to create. This parameter should only be passed by Visual FoxPro scripts, since VBScript and JavaScript scripts cannot work with an in-memory cursor. It must be a valid cursor name. When this method is called from a Select script for a table, you can pass the last parameter received in that script as this parameter if desired (see the examples below).

Return Value
If the SQL statement failed or some error occurred, the return value is blank. If the CursorName parameter is passed, ExecuteSQLStatement creates an in-memory cursor with the specified name and returns that name. If the SQL statement doesn't create a result set (for example, a SQL UPDATE or INSERT statement), "1" is returned. Otherwise, the return value is the result set as an XML string.

Example
This example, called from the Select script for a table, uses ExecuteSQLStatement to call a stored procedure named SalesByCategory to get the data for the table.

Visual FoxPro

lparameters toApplication as SQApplication, toDatabase as Database, ;
  tcSelect, tcCursor
local lcField, loValues, loValue, lcCategory, lcYear, ;
  lcSelect, llReturn
lcField = 'Categories.CategoryName'
loValues = toApplication.DataEngine.GetValuesForField(lcField, ;
  '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

VBScript

function Main(Application, Database, SelectStatement, CursorName)
dim Values, Value
Main = ""
Field = "Categories.CategoryName"
set Values = Application.DataEngine.GetValuesForField(Field, _
  "equals")
if Values.Count > 0 then
  set Value = Values.Item(1)
  Category = "'" & Value.Value & "'"
  set Values = Application.DataEngine.GetValuesForParameter("Year", _
    "C", 4, 0, "equals")
  if Values.Count > 0 then
    set Value = Values.Item(1)
    Year = "'" + Value.Value + "'"
    SelectStmt = "exec SalesByCategory " & Category & ", " & Year
    Main = Database.ExecuteSQLStatement(SelectStmt)
  end if
end if
end function

JavaScript

function Main(Application, Database, SelectStatement, CursorName) {
var XMLResult, Field, Values, Value, Category, SelectStmt, Year ;
XMLResult = "" ;
Field = 'Categories.CategoryName' ;
Values = Application.DataEngine.GetValuesForField(Field, 
  'equals') ;
if (Values.Count > 0) {
  Value = Values.Item(1) ;
  Category = "'" + Value.Value + "'" ;
  Values = Application.DataEngine.GetValuesForParameter('Year', 
    'C', 4, 0, 'equals') ;
  if (Values.Count > 0) {
    Value = Values.Item(1) ;
    Year = "'" + Value.Value + "'" ;
    SelectSmt = 'exec SalesByCategory ' + Category + ', ' + Year ;
    XMLResult = Database.ExecuteSQLStatement(SelectStmt +
      Category + ', ' + Year) ;
  }
}
return XMLResult ;
}

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. The VBScript and JavaScript code returns the result set as XML and the Visual FoxPro code creates a cursor.

Example
This example, called from the Select script for a table, uses ExecuteSQLStatement to call a stored procedure named Sales By Year to get the data for the table.

Visual FoxPro

lparameters toApplication as SQApplication, toDatabase as Database, ;
  tcSelect, tcCursor
local lcField, loValues, lcSelect, llReturn
lcField = 'SalesByYear.ShippedDate'
loValues = toApplication.DataEngine.GetValuesForField(lcField, ;
  'between')
if loValues.Count = 2
  lcSelect = 'exec [Sales By Year] ?Start, ?End'
  llReturn = not empty(toDatabase.ExecuteSQLStatement(lcSelect, ;
    loValues, tcCursor))
endif
return llReturn

VBScript

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

JavaScript

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

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 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. The VBScript and JavaScript code returns the result set as XML and the Visual FoxPro code creates a cursor.