SQLExecDirect() - Execute a statement directly

SQLExecDirect() prepares and executes an SQL statement in one step.

SQLExecDirect() uses the current values of the parameter marker variables, if any parameters exist in the statement. The statement can only be executed once.

ODBC specifications for SQLExecDirect()

Table 1. SQLExecDirect() specifications
ODBC specification level In X/Open CLI CAE specification? In ISO CLI specification?
1.0 Yes Yes

Syntax

SQLRETURN   SQLExecDirect    (SQLHSTMT          hstmt,
                              SQLCHAR     FAR   *szSqlStr,
                              SQLINTEGER        cbSqlStr);

Function arguments

The following table lists the data type, use, and description for each argument in this function.

Table 2. SQLExecDirect() arguments
Data type Argument Use Description
SQLHSTMT hstmt input Specifies the statement handle on which you execute the SQL statement. No open cursor can be associated with the statement handle you use for this argument. Refer to SQLFreeStmt() for more information about how to free or reset a statement handle.
SQLCHAR * szSqlStr input Specifies the string that contains the SQL statement. The connected database server must be able to prepare the statement.
SQLINTEGER cbSqlStr input Specifies the length, in bytes, of the contents of the szSqlStr argument. The length must be set to either the exact length of the statement, or if the statement is nul-terminated, set to SQL_NTS.

Usage

If you plan to execute an SQL statement more than once, or if you need to obtain information about columns in the result set before you execute a query, use SQLPrepare() and SQLExecute() instead of SQLExecDirect().

To use SQLExecDirect(), the connected database server must be able to dynamically prepare statement.

If the SQL statement text contains vendor escape clause sequences, Db2 ODBC first modifies the SQL statement text to the appropriate Db2-specific format before submitting it for preparation and execution. If your application does not generate SQL statements that contain vendor escape clause sequences, set the SQL_ATTR_NOSCAN statement attribute to SQL_NOSCAN_ON at the connection level. When you set this attribute to SQL_NOSCAN_ON, you avoid the performance impact that statement scanning causes.

The SQL statement cannot be COMMIT or ROLLBACK. Instead, You must call SQLEndTran() to issue COMMIT or ROLLBACK statements.

The SQL statement string can contain parameter markers. A parameter marker is represented by a question mark (?) character, and it is used to indicate a position in the statement where an application-supplied value is to be substituted when SQLExecDirect() is called. You can obtain values for parameter markers from the following sources:
  • An application variable.

    SQLBindParameter() is used to bind the application storage area to the parameter marker.

  • A LOB value residing at the server that is referenced by a LOB locator.

    SQLBindParameter() is used to bind a LOB locator to a parameter marker. The actual value of the LOB is kept at the server and does not need to be transferred to the application before being used as the input parameter value for another SQL statement.

You must bind all parameters before you call SQLExecDirect().

If the SQL statement is a query, SQLExecDirect() generates a cursor name and opens a cursor. If the application has used SQLSetCursorName() to associate a cursor name with the statement handle, Db2 ODBC associates the application-generated cursor name with the internally generated one.

If a result set is generated, SQLFetch() or SQLExtendedFetch() retrieves the next row or rows of data into bound variables. Data can also be retrieved by calling SQLGetData() for any column that was not bound.

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

No open cursor can exist on the statement handle before you execute an SQL statement on that handle.

If you call SQLSetStmtAttr() to specify that an array of input parameter values is bound to each parameter marker, you need to call SQLExecDirect() only once to process the entire array of input parameter values.

You cannot specify the FOR n ROWS clause in a MERGE statement that you execute with SQLExecDirect(). Use SQLSetStmtAttr() with the SQL_ATTR_PARAMSET_SIZE statement attribute to specify the number of rows to merge.

Return codes

After you call SQLExecDirect(), it returns one of the following values:
  • 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 requests data-at-execution parameter values. You call SQLParamData() and SQLPutData() to supply these values to SQLExecDirect().

SQL_SUCCESS is returned if the SQL statement is a searched UPDATE or searched DELETE and no rows satisfy the search condition. Use SQLRowCount() to determine the number of rows in a table that were affected by an UPDATE, INSERT, or DELETE statement that was executed on the table, or on a view of the table.

Diagnostics

The following table lists each SQLSTATE that this function generates, with a description and explanation for each value.

Table 3. SQLExecDirect() SQLSTATEs
SQLSTATE Description Explanation
01504 The UPDATE or DELETE statement does not include a WHERE clause. The szSqlStr argument contains an UPDATE or DELETE statement but no WHERE clause. (The function returns SQL_SUCCESS_WITH_INFO or SQL_NO_DATA_FOUND if no rows are in the table.)
07001 Wrong number of parameters. The number of parameters that are bound to application variables with SQLBindParameter() is less than the number of parameter markers in the SQL statement that the szSqlStr argument specifies.
07006 Invalid conversion. Transfer of data between Db2 ODBC and the application variables would result in incompatible data conversion.
08S01 Communication link failure. The communication link between the application and data source fails before the function completes.
21S01 Insert value list does not match column list. The szSqlStr argument contains an INSERT statement and the number of values that are to be inserted do not match the degree of the derived table.
21S02 Degrees of derived table does not match column list. The szSqlStr argument contains a CREATE VIEW statement, and the number of specified names is not the same degree as the derived table that is defined by the query specification.
22001 String data right truncation. A character string that is assigned to a character type column exceeds the maximum length of the column.
22008 Invalid datetime format or datetime field overflow. This SQLSTATE is returned for one or more of the following reasons:
  • The szSqlStr argument contains an SQL statement with an invalid datetime format. (That is, an invalid string representation or value is specified, or the value is an invalid date.)
  • Datetime field overflow occurred.

    Example: An arithmetic operation on a date or timestamp has a result that is not within the valid range of dates, or a datetime value cannot be assigned to a bound variable because it is too small.
22012 Division by zero is invalid. The szSqlStr argument contains an SQL statement with an arithmetic expression that caused division by zero.
22018 Error in assignment. This SQLSTATE is returned for one or more of the following reasons:
  • The szSqlStr argument contains an SQL statement with a parameter or literal, and the value or LOB locator was incompatible with the data type of the associated table column.
  • The length that is associated with a parameter value (the contents of the pcbValue buffer that is specified with the SQLBindParameter() function) is not valid.
  • The fSqlType argument that is used in SQLBindParameter() denoted an SQL graphic data type, but the deferred length argument (pcbValue) contains an odd length value. The length value must be even for graphic data types.
23000 Integrity constraint violation. The execution of the SQL statement is not permitted because the execution would cause an integrity constraint violation in the database management system.
24000 Invalid cursor state. A cursor is open on the statement handle.
24504 The cursor identified in the UPDATE, DELETE, SET, or GET statement is not positioned on a row. Results are pending on the statement handle from a previous query, or a cursor that is associated with the statement handle had not been closed.
34000 Invalid cursor name. The szSqlStr argument contains a positioned DELETE or a positioned UPDATE statement, and the cursor that the statement references is not open.
37xxx1 Invalid SQL syntax. The szSqlStr argument contains one or more of the following statement types:
  • A COMMIT
  • A ROLLBACK
  • An SQL statement that the connected database server could not prepare
  • A statement containing a syntax error
40001 Transaction rollback. The transaction to which the SQL statement belongs is rolled back due to a deadlock or timeout.
42xxx1 Syntax error or access rule violation These SQLSTATEs indicate one of the following errors:
  • For 425xx, the authorization ID does not have permission to execute the SQL statement that the szSqlStr argument contains.
  • For 42xxx, a variety of syntax or access problems with the statement occur.
42895 The value of a host variable in the EXECUTE or OPEN statement cannot be used because of its data type This SQLSTATE is returned for one or more of the following reasons:
  • The LOB locator type that is specified on the bind parameter function call does not match the LOB data type of the parameter marker.
  • The fSqlType argument, which is used on the bind parameter function, specifies a LOB locator type, but the corresponding parameter marker is not a LOB.
42S01 Database object already exists. The szSqlStr argument contains a CREATE TABLE or CREATE VIEW statement, and the specified table name or view name already exists.
42S02 Database object does not exist. The szSqlStr argument contains an SQL statement that references a table name or view name that does not exist.
42S11 Index already exists. The szSqlStr argument contains a CREATE INDEX statement, and the specified index name already exists.
42S12 Index not found. The szSqlStr argument contains a DROP INDEX statement, and the specified index name does not exist.
42S21 Column already exists. The szSqlStr argument contains an ALTER TABLE statement, and the column that is specified in the ADD clause is not unique or identifies an existing column in the base table.
42S22 Column not found. The szSqlStr argument contains an SQL statement that references a column name that does not exist.
44000 Integrity constraint violation. When the szSqlStr argument contains an SQL statement with a parameter or literal, one of the following violations occur:
  • The parameter value is NULL for a column that is defined as NOT NULL in the associated table column.
  • A duplicate value is supplied for a column that is constrained to contain only unique values.
  • An integrity constraint is violated.
58004 Unexpected system failure. Unrecoverable system error.
HY001 Memory allocation failure. Db2 ODBC is not able to allocate the required memory to support the execution or the completion of the function.
HY009 Invalid use of a null pointer. The szSqlStr argument specifies a null pointer.
HY013 Unexpected memory handling error. Db2 ODBC is not able to access the memory that is required to support execution or completion of the function.
HY014 No more handles. Db2 ODBC is not able to allocate a handle due to low internal resources.
HY019 Numeric value out of range. This SQLSTATE is returned for one or more of the following reasons:
  • A numeric value that is assigned to a numeric type column caused truncation of the whole part of the number, either at the time of assignment or in computing an intermediate result.
  • The szSqlStr argument contains an SQL statement with an arithmetic expression that causes division by zero.
HY090 Invalid string or buffer length. The argument cbSqlStr is less than 1 but not equal to SQL_NTS.
Note:
  1. xxx refers to any SQLSTATE with that class code. For example, 37xxx refers to any SQLSTATE with class code '37'.

Example

Refer to SQLFetch() for a related example.