Extended SQLEXEC, sends a SQL statement to the data source, where the statement is processed.
SQLExecEx(nConn | nStatement, cSQL [, cCursors | cVariables [, cArrayName [, nFlags [, cCursorSchema [, cParamSchema [, cCallback [, nCallbackinterval]]]]]]])
Parameters
- nConn | nStatement
A valid ODBC connection handle or a prepared statement handle returned from SQLPrepareEx.
- 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
Column Content 1 The cursorname or an empty string if no resultset is generated. 2 The 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.
Flag Description SQLEXECEX_DEST_CURSOR Store 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_CURSOR Store the resultset into an existing cursor (which is ZAP'ed first). SQLEXECEX_NATIVE_SQL Don'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_PROGRESS Callback into the function passed in the cCallback parameter while fetching rows, if you omit the cCallback parameter this flag is ignored. SQLEXECEX_CALLBACK_INFO Callback 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_INFO Store 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
The number of resultsets or 1 for SQL statements which don't produce a resultset.
-1 if the function failed, -2 if the operation was aborted by the callback function.
Example
Embedding of parameters.
LOCAL laInfo[1], laError[1] lcSQL = "SELECT * FROM someTable WHERE someCol = ?{someVar}" IF SQLEXECEX(yourConnection, lcSQL, 'yourResult', 'laInfo') ? "Cursor " + laInfo[1,1] + " contains " + ALLTRIM(STR(laInfo[1,2])) + " rows" ELSE AERROREX('laError') DISPLAY MEMORY LIKE laError ENDIF lcSQL = "UPDATE someTable SET someCol = ?{myCursor.someField} WHERE pk = ?{myCursor.pk}" IF SQLEXECEX(yourConnection, lcSQL, '', 'laInfo') > 0 ? laInfo[1,2], " rows updated" ELSE AERROREX('laError') DISPLAY MEMORY LIKE laError ENDIF
Storing the result into variables instead of a cursor.
LOCAL lnSum, lnAvg ?SQLEXECEX(yourConnection, 'SELECT SUM(someCol), AVG(someCol2) FROM yourTable', 'lnSum, lnAvg', '', SQLEXECEX_DEST_VARIABLE)
Using named parameters.
MSSQL T-SQL: ------------------------ CREATE PROCEDURE teststoredproc (@lnPK int = 1, @description varchar(8000) = 'Hello' OUTPUT, @thirdParam int = 2, @fourthParam varchar(4000)) ) AS BEGIN PRINT 'Procedure START' SET @description = REPLICATE('HelloWorld',50) PRINT 'Procedure finished!' END ------------------------ LOCAL lcMessage, lcMessage2, lcSQL lcMessage = '' lcMessage2 = 'Hi SQL-Server' lcSQL = "{ CALL teststoredproc(?@{lcMessage},?{lcMessage2}) }" ?SQLEXECEX(yourConnection,lcSQL,'','aResult',0,'','1 "@description", 2 "@fourthParam" ')
See Also
Reference
ASQLDataSources
ASQLDrivers
ChangeSQLDataSource
CreateSQLDataSource
DeleteSQLDataSource
SQLCancelEx
SQLGetPropEx
SQLPrepareEx
SQLSetPropEx
Used WinApi functions
SQLAllocHandle
SQLBindParameter
SQLGetStmtAttr
SQLSetDescRec
SQLSetDescField
SQLExecDirect
SQLParamData
SQLPutData
SQLGetDiagRec
SQLNumResultCols
SQLRowCount
SQLDescribeCol
SQLColAttribute
SQLGetInfo
SQLBindCol
SQLFetch
SQLFreeStmt
SQLMoreResults
SQLFreeHandle