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, 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). Then populate the parameters collection passed to ExecuteSQLStatement with parameters using the same names. For example:

Parameters = SQApplication.GetValuesCollection()
Parameters.AddItem('CustIDValue', 'some value')
Parameters.AddItem('DateOrdered', date(2008, 02, 29))
Stmt = "select * from Orders where CustomerID=?CustIDValue and " + ;
    "OrderDate=?DateOrdered"
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 attribute-based XML string with the following structure:

<DataSet>
    <_resultset Field1="value" Field2="value" .../>
    <_resultset Field1="value" Field2="value" .../>
    ...
</DataSet>

Example
See Select for example code.