| Specification: | DB2 CLI 1.1 | ODBC 1.0 | ISO CLI |
SQLExecDirect() directly executes the specified SQL statement. The statement can only be executed once. Also, the connected database server must be able to dynamically prepare statement. (For more information about supported SQL statements refer to Table 215.)
Syntax
SQLRETURN SQLExecDirect (SQLHSTMT StatementHandle,
SQLCHAR *FAR StatementText,
SQLINTEGER TextLength);
Function Arguments
Table 57. SQLExecDirect Arguments
| Data Type | Argument | Use | Description |
|---|---|---|---|
| SQLHSTMT | StatementHandle | input | Statement handle. There must not be an open cursor associated with StatementHandle, see SQLFreeStmt - Free (or Reset) a Statement Handle for more information. |
| SQLCHAR * | StatementText | input | SQL statement string. The connected database server must be able to prepare the statement, see Table 215 for more information. |
| SQLINTEGER | TextLength | input | Length of contents of StatementText argument. The length must be set to either the exact length of the statement, or if the statement is null-terminated, set to SQL_NTS. |
Usage
If the SQL statement text contains vendor escape clause sequences, DB2 CLI will first modify the SQL statement text to the appropriate DB2 specific format before submitting it for preparation and execution. If the application does not generate SQL statements that contain vendor escape clause sequences (Using Vendor Escape Clauses), then it should set the SQL_ATTR_NOSCAN statement attribute to SQL_NOSCAN_ON at the connection level so that DB2 CLI does not perform a scan for vendor escape clauses.
The SQL statement cannot be a COMMIT or ROLLBACK. Instead, SQLTransact() must be called to issue COMMIT or ROLLBACK. For more information about supported SQL statements refer to Table 215.
The SQL statement string may contain parameter markers. A parameter marker is represented by a "?" character, and is used to indicate a position in the statement where an application supplied value is to be substituted when SQLExecDirect() is called. This value can be obtained from:
SQLSetParam() or SQLBindParameter() is used to bind the application storage area to the parameter marker.
SQLBindFileToParam() is used to bind a file to a LOB parameter marker. When SQLExecDirect() is executed, DB2 CLI will transfer the contents of the file directly to the database server.
All parameters must be bound before calling SQLExecDirect().
Refer to the PREPARE section of the SQL Reference for information on rules related to parameter markers.
If the SQL statement is a query, SQLExecDirect() will generate a cursor name, and open the cursor. If the application has used SQLSetCursorName() to associate a cursor name with the statement handle, DB2 CLI associates the application generated cursor name with the internally generated one.
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 (using SQLBindCol() or SQLBindFileToCol()). 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.
There must not already be an open cursor on the statement handle.
If SQLParamOptions() has been called to specify that an array of input parameter values has been bound to each parameter marker, then the application needs to call SQLExecDirect() only once to process the entire array of input parameter values.
Return Codes
SQL_NEED_DATA is returned when the application has requested to input data-at-execution parameter values by calling SQLParamData() and SQLPutData().
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
Table 58. SQLExecDirect SQLSTATEs
| SQLSTATE | Description | Explanation | ||
|---|---|---|---|---|
| 01504 | The UPDATE or DELETE statement does not include a WHERE clause. | StatementText contained an UPDATE or DELETE statement which did not contain a WHERE clause. (Function returns SQL_SUCCESS_WITH_INFO or SQL_NO_DATA_FOUND if there were no rows in the table). | ||
| 01508 | Statement disqualified for blocking. | The statement was disqualified for blocking for reasons other than storage. | ||
| 07001 | Wrong number of parameters. | The number of parameters bound to application variables using SQLBindParameter() was less than the number of parameter markers in the SQL statement contained in the argument StatementText. | ||
| 07006 | Invalid conversion. | Transfer of data between DB2 CLI and the application variables would result in incompatible data conversion. | ||
| 21S01 | Insert value list does not match column list. | StatementText contained an INSERT statement and the number of values to be inserted did not match the degree of the derived table. | ||
| 21S02 | Degrees of derived table does not match column list. | StatementText contained a CREATE VIEW statement and the number of names specified is not the same degree as the derived table defined by the query specification. | ||
| 22001 | String data right truncation. | A character string assigned to a character type column exceeded the maximum length of the column. | ||
| 22003 | Numeric value out of range. | A numeric value 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.
StatementText contained an SQL statement with an arithmetic expression which caused division by zero.
| ||
| 22005 | Error in assignment. | StatementText contained 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 associated with a parameter value (the contents of the pcbValue buffer specified on SQLBindParameter()) is not valid. The argument fSQLType used in SQLBindParameter() or SQLSetParam(), 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. | ||
| 22007 | Invalid datetime format. | StatementText contained an SQL statement with an invalid datetime format; that is, an invalid string representation or value was specified, or the value was an invalid date. | ||
| 22008 | Datetime field overflow. | Datetime field overflow occurred; for 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. | StatementText contained an SQL statement with an arithmetic expression that caused division by zero. | ||
| 23000 | Integrity constraint violation. | The execution of the SQL statement is not permitted because the execution would cause integrity constraint violation in the DBMS. | ||
| 24000 | Invalid cursor state. | A cursor was already opened on the statement handle. | ||
| 24504 | The cursor identified in the UPDATE, DELETE, SET, or GET statement is not positioned on a row. | Results were pending on the StatementHandle from a previous query or a cursor associated with the hsmt had not been closed. | ||
| 34000 | Invalid cursor name. | StatementText contained a Positioned DELETE or a Positioned UPDATE and the cursor referenced by the statement being executed was not open. | ||
| 37xxx a | Invalid SQL syntax. | StatementText contained one or more of the following:
| ||
| 40001 | Transaction rollback. | The transaction to which this SQL statement belonged was rolled back due to a deadlock or timeout. | ||
| 40003 08S01 | Communication link failure. | The communication link between the application and data source failed before the function completed. | ||
| 42xxx | Syntax Error or Access Rule Violation. | 425xx indicates the authorization ID does not have permission
to execute the SQL statement contained in StatementText.
Other 42xxx SQLSTATES indicate a variety of syntax or access problems with the statement. | ||
| 428A1 | Unable to access a file referenced by a host file variable. | This can be raised for any of the following scenarios. The
associated reason code in the text identifies the particular error:
| ||
| 42895 | The value of a host variable in the EXECUTE or OPEN statement cannot be used because of its data type. | The LOB locator type specified on the bind parameter function call does
not match the LOB data type of the parameter marker.
The argument fSQLType used on the bind parameter function specified a LOB locator type but the corresponding parameter marker is not a LOB. | ||
| 44000 | Integrity constraint violation. | StatementText contained an SQL statement which contained a parameter or literal. This parameter value was NULL for a column defined as NOT NULL in the associated table column, or a duplicate value was supplied for a column constrained to contain only unique values, or some other integrity constraint was violated. | ||
| 56084 | LOB data is not supported in DRDA. | LOB columns cannot either be selected or updated when connecting to DRDA servers (using DB2 Connect). | ||
| 58004 | Unexpected system failure. | Unrecoverable system error. | ||
| S0001 | Database object already exists. | StatementText contained a CREATE TABLE or CREATE VIEW statement and the table name or view name specified already existed. | ||
| S0002 | Database object does not exist. | StatementText contained an SQL statement that references a table name or view name which does not exist. | ||
| S0011 | Index already exists. | StatementText contained a CREATE INDEX statement and the specified index name already existed. | ||
| S0012 | Index not found. | StatementText contained a DROP INDEX statement and the specified index name did not exist. | ||
| S0021 | Column already exists. | StatementText contained an ALTER TABLE statement and the column specified in the ADD clause was not unique or identified an existing column in the base table. | ||
| S0022 | Column not found. | StatementText contained an SQL statement that references a column name which does not exist. | ||
| HY001 | Memory allocation failure. | DB2 CLI is unable to allocate memory required to support execution or completion of the function. | ||
| HY009 | Invalid argument value. | StatementText was a null pointer. | ||
| HY013 | Unexpected memory handling error. | DB2 CLI was unable to access memory required to support execution or completion of the function. | ||
| HY014 | No more handles. | DB2 CLI was unable to allocate a handle due to internal resources. | ||
| HY090 | Invalid string or buffer length. | The argument TextLength was less than 1 but not equal to SQL_NTS. | ||
| HY092 | Option type out of range. | The FileOptions argument of a previous SQLBindFileToParam() operation was not valid. | ||
| HY503 | Invalid file name length. | The fileNameLength argument value from SQLBindFileToParam() was less than 0, but not equal to SQL_NTS. | ||
| HYT00 | Timeout expired. | The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for SQLSetConnectAttr(). | ||
| ||||
Restrictions
None.
(The complete sample tbread.c is also available here .)
/* From the CLI sample TBREAD.C */
/* ... */
/* execute directly the statement */
printf("\n Execute directly the statement.\n");
printf(" %s\n", stmt);
sqlrc = SQLExecDirect( hstmt, stmt, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* ... */
/* execute directly the statement */
printf("\n Execute directly the statement.\n");
printf(" %s\n", stmt);
sqlrc = SQLExecDirect( hstmt, stmt, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* ... */
/* execute directly the statement */
printf("\n Execute directly the statement\n");
printf(" %s.\n", stmt);
sqlrc = SQLExecDirect( hstmt, stmt, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
References