IBM Books

Call Level Interface Guide and Reference

SQLExecDirect - Execute a Statement Directly

Purpose


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:

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.
Note:as a result the cursor state is undefined for DB2 Universal Database (the cursor will remain open for other RDBMSs).

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:
  • 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 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:
  • 01 - The file name length is invalid or the file name and/or the path has an invalid format.
  • 02 - The file option is invalid. It must have one of the following values: 
     
  SQL_FILE_READ      -read from an existing file
  SQL_FILE_CREATE    -create a new file for write
  SQL_FILE_OVERWRITE -overwrite an existing file.
                      If the file does not exist,
                      create the file.
  SQL_FILE_APPEND    -append to an existing file.
                      If the file does not exist,
                      create the file.
  • 03 - The file cannot be found.
  • 04 - The SQL_FILE_CREATE option was specified for a file with the same name as an existing file.
  • 05 - Access to the file was denied. The user does not have permission to open the file.
  • 06 - Access to the file was denied. The file is in use with incompatible modes. Files to be written to are opened in exclusive mode.
  • 07 - Disk full was encountered while writing to the file.
  • 08 - Unexpected end of file encountered while reading from the file.
  • 09 - A media error was encountered while accessing the file.
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().
Note:

a
xxx refers to any SQLSTATE with that class code. Example, 37xxx refers to any SQLSTATE in the 37 class.

Restrictions

None.

Example

See the CLI Sample fetch.c .

References

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]