SQLExecute function (CLI) - Execute a statement

Executes a statement that was successfully prepared using SQLPrepare() on the same statement handle, once or multiple times. The statement is executed using the current values of any application variables that were bound to parameter markers by SQLBindParameter() or SQLBindFileToParam().

Specification:

  • CLI 1.1
  • ODBC 1.0
  • ISO CLI

Syntax

SQLRETURN   SQLExecute (SQLHSTMT       StatementHandle);  /* hstmt */

Function arguments

Table 1. SQLExecute arguments
Data type Argument Use Description
SQLHSTMT StatementHandle input Statement handle. There must not be an open cursor associated with StatementHandle.

Usage

The SQL statement string previously prepared on StatementHandle using SQLPrepare() may contain parameter markers. All parameters must be bound before calling SQLExecute().

Note: For XQuery expressions, you cannot specify parameter markers in the expression itself. You can, however, use the XMLQUERY function to bind parameter markers to XQuery variables. The values of the bound parameter markers will then be passed to the XQuery expression specified in XMLQUERY for execution.

Once the application has processed the results from the SQLExecute() call, it can execute the statement again with new (or the same) parameter values.

A statement executed by SQLExecDirect() cannot be re-executed by calling SQLExecute(). Only statements prepared with SQLPrepare() can be executed and re-executed with SQLExecute().

If the prepared SQL statement is a query or an XQuery expression, SQLExecute() will generate a cursor name, and open the cursor. If the application has used SQLSetCursorName() to associate a cursor name with the statement handle, CLI associates the application generated cursor name with the internally generated one.

To execute a query more than once on a given statement handle, the application must close the cursor by calling SQLCloseCursor() or SQLFreeStmt() with the SQL_CLOSE option. There must not be an open cursor on the statement handle when calling SQLExecute().

If a result set is generated, SQLFetch() or SQLFetchScroll() will retrieve the next row (or rows) of data into bound variables, LOB locators or LOB file references.

If the SQL statement is a positioned DELETE or a positioned UPDATE, the cursor referenced by the statement must be positioned on a row at the time SQLExecute() is called, and must be defined on a separate statement handle under the same connection handle.

If SQLSetStmtAttr() has been called with the SQL_ATTR_PARAMSET_SIZE attribute to specify that an array of input parameter values has been bound to each parameter marker, the application needs to call SQLExecute() only once to process the entire array of input parameter values. If the executed statement returns multiple result sets (one for each set of input parameters), then SQLMoreResults() should be used to advance to the next result set once processing on the current result set is complete.

Return codes

  • SQL_SUCCESS
  • SQL_SUCCESS_WITH_INFO
  • SQL_ERROR
  • SQL_INVALID_HANDLE
  • SQL_NEED_DATA
  • SQL_NO_DATA_FOUND

SQL_NEED_DATA is returned when the application has requested to input data-at-execute parameter values by setting the *StrLen_or_IndPtr value specified during SQLBindParameter() to SQL_DATA_AT_EXEC for one or more parameters.

SQL_NO_DATA_FOUND is returned if the SQL statement is a searched UPDATE or searched DELETE and no rows satisfy the search condition.

Diagnostics

The SQLSTATEs for SQLExecute() include all those for SQLExecDirect() except for HY009, HY090 and with the addition of the SQLSTATE in the following table. Any SQLSTATE that SQLPrepare() could return can also be returned on a call to SQLExecute() as a result of deferred prepare behavior.

Table 2. SQLExecute SQLSTATEs
SQLSTATE Description Explanation
HY010 Function sequence error. The specified StatementHandle was not in a prepared state. SQLExecute() was called without first calling SQLPrepare().

Authorization

None.

Example

  SQLHANDLE hstmt; /* statement handle */
  SQLCHAR *stmt = (SQLCHAR *)"DELETE FROM org WHERE deptnumb = ? ";
  SQLSMALLINT parameter1 = 0;

  /* allocate a statement handle */
  cliRC = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

  /* ... */

  /* prepare the statement */
  cliRC = SQLPrepare(hstmt, stmt, SQL_NTS);
  
  /* ... */

  /* bind parameter1 to the statement */
  cliRC = SQLBindParameter(hstmt,
                           1,
                           SQL_PARAM_INPUT,
                           SQL_C_SHORT,
                           SQL_SMALLINT,
                           0,
                           0,
                           &parameter1,
                           0,
                           NULL);
  /* ... */
  parameter1 = 15;

  /* execute the statement for parameter1 = 15  */
  cliRC = SQLExecute(hstmt);