Extended SQLPREPARE.
Prepares a SQL statement for remote execution by SQLExecEx( ).

SQLPrepareEx(nConn, cSQL [, cCursors | cVariables [, cArrayName [, nFlags [, cCursorSchema [, cParamSchema [, cCallback [, nCallbackinterval]]]]]]])

Parameters

nConn

A valid ODBC connection handle.

cSQL

The SQL statement to execute.

Parameters can be embedded into the SQL statement by enclosing them in curly braces prepended by a "?",
everything in the statement that is enclosed by " or ' is passed on as is.

cCursors | cVariables (optional)

Specifies one or more cursornames seperated by a comma e.g. "yourCursor1, yourCursor2"

If you omit this parameter or pass an empty string, the names are automatically created like in SQLEXEC,
the first cursor is named "sqlresult" and for each additional cursor the resultset number is appended (sqlresult2, sqlresult3 ....).

If you pass SQLEXECEX_DEST_VARIABLE in the nFlags parameter, the value is interpreted as a comma seperated list
of variable or field names into which the data of the first row of the resultset are stored in sequential order,
that is column no. 1 is stored into variable/field no. 1, 2 in 2 and so on.

cArray (optional)

The name of the array into which the number of returned/deleted/updated/inserted rows is stored for each statement

ColumnContent
1The cursorname or an empty string if no resultset is generated.
2The number of rows returned, deleted, updated or inserted.


This is exaclty the same behaviour as introduced in VFP9.
If you specify SQLEXECEX_STORE_INFO in the nFlags parameter, additional info is stored into the first column of the array.

nFlags (optional, additive)

default = SQLEXECEX_DEST_CURSOR | SQLEXECEX_CALLBACK_PROGRESS | SQLEXECEX_CALLBACK_INFO

If you omit the nFlags parameter or pass the value 0, the default is used.

FlagDescription
SQLEXECEX_DEST_CURSORStore the resultset into one or more VFP cursors.
SQLEXECEX_DEST_VARIABLE Store the resultset into VFP variables/fields.
If you pass this flag only the values of the first row of the resultset are saved into the variables/fields.
SQLEXECEX_REUSE_CURSORStore the resultset into an existing cursor (which is ZAP'ed first).
SQLEXECEX_NATIVE_SQLDon't try to parse the passed SQL statement for parameters, just pass it on to the datasource as it is.
Of course you cannot embedd any parameters if you set this flag.
SQLEXECEX_CALLBACK_PROGRESSCallback into the function passed in the cCallback parameter while fetching rows, if you omit the cCallback parameter this flag is ignored.
SQLEXECEX_CALLBACK_INFOCallback into the function passed in the cCallback parameter if additional information from the backend is receveived (e.g. from PRINT or RAISERROR statements in a stored procedure).
SQLEXECEX_STORE_INFOStore additional info from the backend into the array passed in the cArray parameter.
cCursorSchema :

The syntax for the schema is like in CREATE TABLE/CURSOR, a comma seperated list of column name and type description e.g.:
"myIntCol I, myCharCol C(254) NULL, myBinaryCol C(254) NULL NOCPTRANS, myText M, myBlob W"

Note

Long typenames like 'Numeric' or 'Character' are NOT supported!
If you query the database for Unicode character data the default is to convert to Ansi. If you want to get the unicode data unmodified you can specify NOCPTRANS for the column in the schema. e.g.
"yourUnicodeSmallText C(254) NOCPTRANS, yourUnicodeLongtext M NOCPTRANS"

If you convert a unicode column to types 'W' or 'Q' the data will also be in Unicode format.

cParameterSchema (optional)

Comma seperated list of parameter number, name and SQL type in wich the data should be send e.g.
"1 '@parmone' SQL_WCHAR, 2 '@parmtwo' SQL_CHAR"

&&  the parameter name must be enclosed with either " (double quotes) or ' (single quotes)
 lcDocument = STRTOFILE('someFileWithUnicodeContent.txt')
 ?SQLEXECEX(yourConnection,'UPDATE yourTable SET someField = ?{someVar}, someUnicodeTextField = ?{lcDocument} WHERE yourKey = ?{lnID}','','',0,'','2 SQL_WCHAR')

&& you can also pass a backend specific datatype by specifying the sql type in numerical form
 ?SQLEXECEX(yourConnection,'UPDATE yourTable SET someField = ?{someVar} WHERE  yourKey = ?{lnID}','','laResult',0,'','1 43')

Note

Named parameters can only be used if you call stored procedures and the backend & ODBC driver supports it - Microsoft SQL Server for example.
If you use named parameters all parameters must be named.

cCallback (optional)

Function to call back while fetching the resultset or when a PRINT or RAISERROR statement or other additional backend information is retrieved.

Function prototype:

FUNCTION SQLCallback(lnSet, lnRow, lnRowCount)
&& lnSet: number of the resultset which is currently fetched or -1
&& if a PRINT or RAISERROR statement with low severity is catched

&& lnRow: current row that is fetched, or if lnSet is -1
&& lnRow contains the message/warning of the PRINT or 
&& RAISERROR statement

&& lnRowCount: either the number of rows to fetch overall
&& or 0/-1 if the ODBC driver used doesn't support returning the 
&& matching rows of a query
&& if lnSet equals -1 this parameter has no meaning and is always .F.
ENDFUNC
nCallbackinterval

default = 100

The interval to use for calling back into the callback procedure while fetching rows.
Your callback procedure will be called once before the first row is fetched, then on every n'th record and once after the last row was fetched.

Return Value

A prepared statement handle, -1 if the function failed.

See Also

Reference

ASQLDataSources
ASQLDrivers
ChangeSQLDataSource
CreateSQLDataSource
DeleteSQLDataSource
SQLExecEx
SQLGetPropEx
SQLSetPropEx
SQLCancelEx

Used WinApi functions

SQLAllocHandle