SQLSetStmtAttr--Set options related to a statement

Purpose

SQLSetStmtAttr() sets options related to a statement.

Syntax

SQLRETURN   SQLSetStmtAttr   (SQLHSTMT          StatementHandle,   
                              SQLINTEGER        Attribute,         
                              SQLPOINTER        ValuePtr,          
                              SQLINTEGER        StringLength);     

Function arguments

Table 82. SQLSetStmtAttr arguments

Usage

Statement attributes for a statement remain in effect until they are changed by another call to SQLSetStmtAttr() or the statement is dropped by calling SQLFreeHandle(). Calling SQLFreeStmt() with the SQL_CLOSE, SQL_UNBIND, or SQL_RESET_PARAMS options does not reset statement attributes.

Some statement attributes support substitution of a similar value if the data source does not support the value specified in ValuePtr. In such cases, DB2 CLI returns SQL_SUCCESS_WITH_INFO and SQLSTATE 01S02 (Option value changed). For example, if Attribute is SQL_ATTR_CONCURRENCY, ValuePtr is SQL_CONCUR_ROWVER, and the data source does not support this, DB2 CLI substitutes SQL_CONCUR_VALUES and returns SQL_SUCCESS_WITH_INFO. To determine the substituted value, an application calls SQLGetStmtAttr(). The format of information set with ValuePtr depends on the specified Attribute.

SQLSetStmtAttr() accepts attribute information in one of two different formats: a null-terminated character string or a 32-bit integer value. The format of each is noted in the attribute's description. This format applies to the information returned for each attribute in SQLGetStmtAttr(). Character strings pointed to by the ValuePtr argument of SQLSetStmtAttr() have a length of StringLength.

The dirty bit

DB2 Everyplace uses the dirty bit to track changes made to a record. The behavior of the dirty bit is affected by the SQL_ATTR_DELETE_MODE, SQL_ATTR_READ_MODE, and SQL_ATTR_DIRTYBIT_SET_MODE statement attributes. The following table shows the states of the dirty bit after certain database operations are performed on a record. The table assumes that the SQL_ATTR_DIRTYBIT_SET_MODE parameter is set to SQL_DIRTYBIT_SET_BY_SYSTEM with the dirty bit maintained by the system.

Table 83. DB2 Everyplace dirty bit states

The value of the dirty bit can be obtained by querying the $dirty column of a table. For example, the following statement returns the dirty bit and the NAME column of the PHONEBOOK table:

SELECT $dirty, NAME from PHONEBOOK

The dirty bit can have the following values.

Table 84. DB2 Everyplace dirty bit values

Statement attributes

The currently defined attributes are shown below.

SQL_ATTR_CURSOR_SCROLLABLE (DB2 CLI)

A 32-bit integer that specifies the level of support that the application requires. Setting this attribute affects subsequent calls to SQLExecDirect() and SQLExecute(). The supported values are:

• SQL_NONSCROLLABLE

  Scrollable cursors are not required on the statement handle. If the application calls SQLFetchScroll() on this handle, the only valid value of FetchOrientation() is SQL_FETCH_NEXT. This is the default.

• SQL_SCROLLABLE

  Scrollable cursors are required on the statement handle. When calling SQLFetchScroll(), the application might specify any valid value of FetchOrientation so that the cursor can be positioned in modes other than the sequential mode.

SQL_ATTR_CURSOR_SENSITIVITY (DB2 CLI)

A 32-bit integer value that specifies whether a cursor is sensitive to the write activity of another cursor. The supported values are:

• SQL_UNSPECIFIED

  The write activity of other cursors has an undefined impact on the current cursor. This is the default.

• SQL_INSENSITIVE

  The write activity of other cursors has no impact on the current cursor.

  Note:

  Use this attribute value sparingly because it can affect performance.

SQL_ATTR_CURSOR_TYPE (DB2 CLI)

A 32-bit integer value that specifies the cursor type. The supported values are:

• SQL_CURSOR_FORWARD_ONLY = The cursor scrolls forward only. This is the default.
• SQL_CURSOR_STATIC = The data in the result set is static.

This option cannot be specified for an open cursor.

SQL_ATTR_ROW_ARRAY_SIZE (DB2 CLI)

A 32-bit integer value that specifies the number of rows in the row set. This is the number of rows returned by each call to SQLFetch() or SQLFetchScroll(). The default value is 1. If the specified row set size exceeds the maximum row set size supported by the data source, DB2 CLI substitutes that value and returns SQLSTATE 01S02 (Option value changed). This option can be specified for an open cursor.

SQL_ATTR_ROW_BIND_TYPE (DB2 CLI)

A 32-bit integer value that sets the binding orientation to be used when SQLFetch() or SQLFetchScroll() is called on the associated statement. Column-wise binding is selected by supplying the defined constant SQL_BIND_BY_COLUMN in ValuePtr. The length specified in ValuePtr must include space for all of the bound columns and any padding of the structure or buffer to ensure that, when the address of a bound column is incremented with the specified length, the result points to the beginning of the same column in the next row. When using the sizeof operator with structures or unions in ANSI C, this behavior is guaranteed. Column-wise binding is the default binding orientation for SQLFetchScroll().

SQL_ATTR_ROW_NUMBER (DB2 CLI)

A 32-bit integer value that is the number of the current row in the entire result set. If the number of the current row cannot be determined or there is no current row, DB2 CLI returns 0. This attribute can be retrieved by a call to SQLGetStmtAttr(), but not set by a call to SQLSetStmtAttr().

SQL_ATTR_ROW_STATUS_PTR (DB2 CLI)

A 16-bit unsigned integer value that points to an array of UWORD values containing row status values after a call to SQLFetch() or SQLFetchScroll(). The array has as many elements as there are rows in the row set. This statement attribute can be set to a null pointer, in which case DB2 CLI does not return row status values. This attribute can be set at any time, but the new value is not used until the next time SQLFetch() or SQLFetchScroll() is called.

SQL_ATTR_ROWS_FETCHED_PTR (DB2 CLI)

A 32-bit unsigned integer value that points to a buffer in which to return the number of rows fetched after a call to SQLFetch() or SQLFetchScroll().

SQL_ATTR_DELETE_MODE (DB2 Everyplace)

The supported values are:

• SQL_DELETE_MARK_ONLY

  This is the system default. When a delete SQL statement is executed, records are only marked as "delete". The record contents can still be read if the SQL_READ_INCLUDE_MARKED_DELETE is set.

• SQL_DELETE_PHYSICAL_REMOVE

  A delete SQL statement physically removes the records meeting the WHERE clause condition, regardless of its dirty bit.

For example, use the following syntax to physically remove some records ignoring the status of the dirty bits:

SQLSetStmtAttr (stmt, SQL_ATTR_DELETE_MODE, SQL_DELETE_PHYSICAL_REMOVE, 0)
 

Next execute the following SQL statement to delete all records from table T where X is not equal to 0:

DELETE T WHERE X<>0

SQL_ATTR_DIRTYBIT_SET_MODE (DB2 Everyplace)

A 32-bit integer value that specifies the cursor type. The supported values are:

• SQL_DIRTYBIT_SET_BY_SYSTEM

  This is the system default. A record that is inserted, updated, or deleted has a dirty bit that is set to INSERT, UPDATE, or DELETE, respectively. No UPDATE of the $dirty column is allowed when the SQL_DIRTYBIT_SET_BY_SYSTEM is set.

• SQL_DIRTYBIT_SET_BY_APPLICATION

  The application is responsible for setting the dirty bit when inserting, updating, or deleting records. The semantics for each operation are:

  UPDATE

  The system sets the dirty bit exactly as specified by the application. For example, if an application executes the following statement then all records in the table are reset to 0 (CLEAN):

  UPDATE T SET $dirty=0 WHERE $dirty>0
  

  INSERT

  The dirty bit of the newly inserted record is set to CLEAN.

  DELETE

  If SQL_DELETE_PHYSICAL_REMOVE is set, DELETE physically removes records from the database. Otherwise, the values of the $dirty column are set to DELETE and the records remain in the database.

For example, to clean the dirty bit of a record use the following statement:

SQLSetStmtAttr (stmt, SQL_ATTR_DIRTYBIT_SET_MODE, 
                SQL_DIRTYBIT_SET_BY_APPLICATION, 0)
 

Then execute the following SQL statement:

UPDATE T SET $DIRTY=0 WHERE $DIRTY>0

In general, applications can set SQL_DIRTYBIT_SET_BY_APPLICATION when the dirty bits are not needed for tracking database updates by end-users.

SQL_ATTR_READ_MODE (DB2 Everyplace)

A 32-bit integer value that specifies the cursor type. The supported values are:

• SQL_READ_EXCLUDE_MARKED_DELETE

  This is the system default. All records with the dirty bit set to "delete" are hidden from SQL.

• SQL_READ_INCLUDE_MARKED_DELETE

  Once set, the records with the dirty bit set to DELETE are visible from SQL SELECT statement. Applications can distinguish those deleted records from other records by examining the dirty bit for a record.

For example, use the following statement to read all records with the dirty bit set, including those with dirty bits marked as DELETE:

SQLSetStmtAttr (stmt, SQL_ATTR_READ_MODE, SQL_READ_INCLUDE_MARKED_DELETE, 0)
 

then execute the following SQL statement to retrieve all records:

SELECT * FROM T WHERE $dirty<>0

SQL_ATTR_REORG_MODE (DB2 Everyplace)

A 32-bit integer value that specifies whether automatic database reorganization is performed on user created tables and whether explicit REORG SQL statements are allowed. The supported values are:

• SQL_REORG_ENABLED - This is the system default. Database reorganization can be performed by DB2 Everyplace or explicitly by the user with a REORG SQL statement.
• SQL_REORG_DISABLED - REORG SQL statements are restricted and automatic database reorganization of user-created tables is disabled.

This option cannot be specified for an open cursor.

Return codes

• SQL_SUCCESS
• SQL_SUCCESS_WITH_INFO
• SQL_ERROR
• SQL_INVALID_HANDLE

Diagnostics

Table 85. SQLSetStmtAttr SQLSTATEs

Restrictions

None.

Related reference

• Key to DB2 CLI function descriptions
• DB2 CLI function summary