SQLEXEC( ) Function

  • Article

Sends a SQL statement to the data source, where the statement is processed.

SQLEXEC(nConnectionHandle, [cSQLCommand, [cCursorName]])

Parameters

  • nConnectionHandle
    Specifies the connection handle to the data source returned by SQLCONNECT( ).

  • cSQLCommand
    Specifies the SQL statement passed to the data source.

    The SQL statement can contain a parameterized WHERE clause, which creates a parameterized view. You must define all parameters in the WHERE clause before issuing SQLEXEC( ). For example, if the parameters are variables, the variables must be created and initialized before SQLEXEC( ) is issued. For additional information about creating parameterized views, see Creating Views.

    You can include expressions in the SQL statement. Visual FoxPro evaluates all expressions in the SQL statement that you pass before sending them to the data source. Visual FoxPro can evaluate memory variable names, function calls, and expressions enclosed in parentheses as parameter values.

  • cCursorName
    Specifies the name of the Visual FoxPro cursor to which the result set is sent. If you don't include a cursor name, Visual FoxPro uses the default name SQLRESULT.

    For multiple result sets, new cursor names are derived by appending an incremented number to the name of the first cursor.

Return Values

Numeric data type. SQLEXEC( ) returns the number of result sets if there is more than one. SQLEXEC( ) returns 0 if it is still executing and returns 1 when it has finished executing. SQLEXEC( ) returns –1 if a connection level error occurs.

Remarks

If the SQL statement you want to pass is quite long, check to see if it exceeds the maximum length of a string literal in Visual FoxPro, which is 255 characters. Longer strings will cause a "Command contains unrecognized phrase/keyword" error. However, you can pass long SQL statements if you break them up into several concatenated literals. For example:

lnRetVal = SQLEXEC(lnHandle, "SELECT <long list of fields> " + ;
   "FROM <several tables> " + ;
   "WHERE <complex filter expression>")

If SQLEXEC( ) is used to execute a SQL statement prepared with SQLPREPARE( ), only the connection handle argument nConnectionHandle is required. The cSQLCommand and CursorName arguments should be omitted.

If the SQL statement generates one result set, SQLEXEC( ) stores the result set to the specified Visual FoxPro cursor. If the SQL statement generates two or more result sets and SQLSETPROP( ) is set to 1 (batch mode), you can name each result set by setting the SQLSETPROP( ) BatchMode option to 0 and changing the cursor name each time you call SQLMORERESULTS( ).

SQLEXEC( ) is one of the four functions that you can execute either synchronously or asynchronously. The Asynchronous setting of SQLSETPROP( ) determines whether these functions execute synchronously or asynchronously. In asynchronous mode, you must call SQLEXEC( ) repeatedly until it returns a value other than 0 (still executing).

Example

Example 1

The following example assumes SQLCONNECT( ) is successfully issued, and its return value is stored to a memory variable named gnConnHandle. SQLEXEC( ) is used to execute a query that returns all the information in the authors table into a cursor named MyCursor.

SQLSETPROP(gnConnHandle, 'asynchronous', .F.)
SQLEXEC(gnConnHandle, 'SELECT * FROM authors', 'MyCursor')

Example 2

The following example assumes SQLCONNECT( ) is successfully issued, and its return value is stored to a memory variable named gnConnHandle. SQLEXEC( ) evaluates variables and expressions in the SQL statements before sending them to the data source.

STORE "MOD(5,2)" to myVar
SQLEXEC(gnConnHandle, "SELECT * FROM testtable WHERE ikey = ?myVar")
SQLEXEC(handle, "select * from testtable where ikey = ?myVar")
SQLEXEC(handle, "select * from testtable where ikey = ?MOD(5,2)")
STORE 1 TO myVar1
STORE 2 TO myVar2
SQLEXEC(handle, "select * from testtable where ikey = ?(myVar1 + myVar2)")

See Also

AERROR( ) | SQLCANCEL( ) | SQLCONNECT( ) | SQLGETPROP( ) | SQLMORERESULTS( ) | SQLPREPARE( ) | SQLSETPROP( ) | SQLSTRINGCONNECT( )