SqlExec Function | |
Sends a SQL statement to the data source, where the statement is processed.
Namespace:
XSharp.VFP
Assembly:
XSharp.VFP (in XSharp.VFP.dll) Version: 2.19
Syntax [NeedsAccessToLocalsAttribute(TRUE)]
FUNCTION SqlExec(
nStatementHandle AS LONG,
cSQLCommand AS STRING,
cCursorName AS STRING,
aCountInfo AS ARRAY
) AS LONG
[NeedsAccessToLocalsAttribute(true)]
public static int SqlExec(
int nStatementHandle,
[DefaultParameterValueAttribute("", 0)] string cSQLCommand,
[DefaultParameterValueAttribute("SQLRESULT", 0)] string cCursorName,
[DefaultParameterValueAttribute(null, 0)] Array aCountInfo
)
Request Example
View SourceParameters
- nStatementHandle
- Type: Long
Specifies the statement handle to the data source returned by the SqlConnect( ) function. For more information, see SqlConnect( ) Function.
- cSQLCommand
- Type: String
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 more information about parameterized views, see How to: Create Parameterized Views.
You can include expressions in the SQL statement. X# evaluates all expressions in the SQL statement that you pass before sending them to the data source.
X# can evaluate memory variable names, function calls, and expressions enclosed in parentheses as parameter values.
Tip |
---|
Parameterized queries are not yet supported in X# |
- cCursorName
- Type: String
Specifies the name of the X# cursor to which the result set is sent. If you don't include a cursor name, X# 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.
- aCountInfo
- Type: Array
Specifies an array to populate with row count information. The array is resized to hold all result sets.
The array has two columns: 1 – Alias, 2 –Count.
Return Value
Type:
Long
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 X#, 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:
1lnRetVal = SqlExec(lnHandle, "Select <long list of fields> " + ;
2"From <several tables> " + ;
3"Where <complex filter expression>")
If
SqlExec( ) is used to execute a SQL statement prepared with
SqlPrepare( ), only the connection handle argument nStatementHandle is required.
The cSQLCommand and CursorName arguments should be omitted. For more information, see SqlPrepare( ) Function.
If the SQL statement generates one result set,
SqlExec( ) stores the result set to the specified X# cursor.
If the SQL statement generates two or more result sets, you can name each result set by setting the connection's BatchMode property to False (.F.)
using the SqlSetProp( ) function and changing the cursor name each time you call the SqlMoreResults( ) function. Otherwise, SqlExec( ) names each result
set by appending sequential numbers to the name of the first one.
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).
Column | Array contents | Data type | Description |
---|
Alias | 0 | Character |
Indicates that SQL command did not return any results. Either no records were returned or the SQL command failed before results could be returned.
(final SqlMoreResults call) or execution failed before any result could be processed. Can be only on the first row.
Count column for the row contains value -1.
|
| Non-empty uppercase string | Character |
Alias of the cursor – target for the record fetch operation. The Count column for the row contains the number of fetched records or -1 if fetch failed.
If Count is -1, cursor may not have been created. During asynchronous execution, the fetch process for a cursor can be split between multiple
SqlMoreResults or SqlExec calls; each call returns its own fetch count for the cursor.
|
| Character | Empty String | Indicates that the SQL command (Insert, UPDATE, or DELETE) did not return a result set. |
Count | Number of affected or fetched records. | Integer | Indicates the number of affected records as returned by the ODBC SQLRowCount function. Returns -1 if the number of records is unavailable. |
Examples
The following example shows various ways to use
SqlExec( ) to execute ad-hoc queries and to call or create stored procedures:
1Clear
2LOCAL lnConn
3LOCAL lnPercent AS Int
4LOCAL lnOutput
5lnPercent = 50
6lnOutput = 0
7* Make connection, assuming a local trusted connection.
8lnConn = SqlConnect('local')
9IF m.lnConn > 0
10* Set the active database to PUBS.
11SqlExec(m.lnConn, 'use pubs')
12* Execute Select statement.
13SqlExec(m.lnConn, 'Select * From authors', 'PubAuthors')
14BROWSE
15
16* Execute Insert statement, get value of identity field.
17SqlExec(m.lnConn, "Insert Into JOBS (job_desc, min_lvl, max_lvl);
18VALUES ('Developer',75,150)")
19SqlExec(m.lnConn, "Select SCOPE_IDENTITY()", "job_id")
20? "ID for added Job is " + LTRIM(STR(job_id.exp))
21* Execute DELETE statement. Get number of records affected.
22SqlExec(m.lnConn, "DELETE From JOBS Where job_desc ='Developer'")
23SqlExec(m.lnConn, "Select @@ROWCOUNT", 'rowcount')
24? rowcount.exp, "record(s) deleted"
25* Call a stored procedure with no parameters.
26SqlExec(m.lnConn, 'sp_who', 'activeusers')
27BROWSE
28* Execute stored procedure with an INPUT parameter.
29SqlExec(m.lnConn, 'exec byroyalty ?lnPercent','HalfOffAuthors')
30
31* Create temp stored procedure with OUTPUT parameter and call it.
32SqlExec(m.lnConn, "Create PROCEDURE #MyProc @outparam int OUTPUT AS;
33Select @outparam=100")
34SqlExec(m.lnConn, "exec #myProc ?@lnOutput")
35? m.lnOutput
36
37* Create a temp stored procedure with INPUT and OUTPUT parameters
38* and call it.
39SqlExec(m.lnConn, "Create PROCEDURE #MyProc2 " + ;
40"@inputparam INT, " + ;
41"@outparam int OUTPUT " + ;
42"AS Set @outparam=@inputparam*10")
43SqlExec(m.lnConn, "exec #myProc2 ?lnPercent, ?@lnOutput")
44? m.lnOutput
45* Get version information.
46SqlExec(m.lnConn, 'Select @@VERSION','SQLVersion1')
47? STRTRAN(SQLVersion1.Exp,CHR(0))
48* Disconnect.
49SqlDisconnect(m.lnConn)
50ELSE
51? "Unable to connect to SQL Server"
52ENDIF
53RETURN
See Also