| Specification: | DB2 CLI 5.0 | ODBC 3.0 | ISO CLI |
SQLSetDescField() sets the value of a single field of a descriptor record.
Syntax
SQLRETURN SQLSetDescField (SQLHDESC DescriptorHandle,
SQLSMALLINT RecNumber,
SQLSMALLINT FieldIdentifier,
SQLPOINTER ValuePtr,
SQLINTEGER BufferLength);
Function Arguments
Table 156. SQLSetDescField Arguments
| Data Type | Argument | Use | Description |
|---|---|---|---|
| SQLHDESC | DescriptorHandle | input | Descriptor handle. |
| SQLSMALLINT | RecNumber | input | Indicates the descriptor record containing the field that the application seeks to set. Descriptor records are numbered from 0, with record number 0 being the bookmark record. The RecNumber argument is ignored for header fields. |
| SQLSMALLINT | FieldIdentifier | input | Indicates the field of the descriptor whose value is to be set. For more information, see FieldIdentifier Arguments. |
| SQLPOINTER | ValuePtr | input | Pointer to a buffer containing the descriptor information, or a four-byte value. The data type depends on the value of FieldIdentifier. If ValuePtr is a four-byte value, either all four of the bytes are used, or just two of the four are used, depending on the value of the FieldIdentifier argument. |
| SQLINTEGER | BufferLength | input | If FieldIdentifier is an ODBC-defined field and
ValuePtr points to a character string or a binary buffer, this
argument should be the length of *ValuePtr. If
FieldIdentifier is an ODBC-defined field and ValuePtr is an
integer, BufferLength is ignored.
If FieldIdentifier is a driver-defined field, the application indicates the nature of the field by setting the BufferLength argument. BufferLength can have the following values:
|
Usage
An application can call SQLSetDescField() to set any descriptor field one at a time. One call to SQLSetDescField() sets a single field in a single descriptor. This function can be called to set any field in any descriptor type, provided the field can be set (see the table later in this section).
| Note: | If a call to SQLSetDescField() fails, the contents of the descriptor record identified by the RecNumber argument are undefined. |
Other functions can be called to set multiple descriptor fields with a single call of the function. The SQLSetDescRec() function sets a variety of fields that affect the data type and buffer bound to a column or parameter (the TYPE, DATETIME_INTERVAL_CODE, OCTET_LENGTH, PRECISION, SCALE, DATA_PTR, OCTET_LENGTH_PTR, and INDICATOR_PTR fields) SQLBindCol() or SQLBindParameter() can be used to make a complete specification for the binding of a column or parameter. These functions set a specific group of descriptor fields with one function call.
SQLSetDescField() can be called to change the binding buffersby adding an offset to the binding pointers (SQL_DESC_DATA_PTR, SQL_DESC_INDICATOR_PTR, or SQL_DESC_OCTET_LENGTH_PTR). This changes the binding buffers without calling SQLBindCol()or SQLBindParameter(). This allows an application to change SQL_DESC_DATA_PTR without changing other fields, for instance SQL_DESC_DATA_TYPE.
Descriptor header fields are set by calling SQLSetDescField() with a RecNumber of 0, and the appropriate FieldIdentifier. Many header fields contain statement attributes, so may also be set by a call to SQLSetStmtAttr(). This allows applications to set a statement attribute without first obtaining a descriptor handle. A RecNumber of 0 is also used to set bookmark fields.
| Note: | The statement attribute SQL_ATTR_USE_BOOKMARKS should always be set before calling SQLSetDescField() to set bookmark fields. While this is not mandatory, it is strongly recommended. |
Sequence of Setting Descriptor Fields
When setting descriptor fields by calling SQLSetDescField(), the application must follow a specific sequence:
Initialization of Descriptor Fields
When a descriptor is allocated, the fields in the descriptor can be initialized to a default value, be initialized without a default value, or be undefined for the type of descriptor. The following tables indicate the initialization of each field for each type of descriptor, with "D" indicating that the field is initialized with a default, and "ND" indicating that the field is initialized without a default. If a number is shown, the default value of the field is that number. The tables also indicate whether a field is read/write (R/W) or read-only (R).
The fields of an IRD have a default value only after the statement has been prepared or executed and the IRD has been populated, not when the statement handle or descriptor has been allocated. Until the IRD has been populated, any attempt to gain access to a field of an IRD will return an error.
Some descriptor fields are defined for one or more, but not all, of the descriptor types (ARDs and IRDs, and APDs and IPDs). When a field is undefined for a type of descriptor, it is not needed by any of the functions that use that descriptor. Because a descriptor is a logical view of data, rather than an actual data structure, these extra fields have no effect on the defined fields (for more information, see Using Descriptors).
The fields that can be accessed by SQLGetDescField() cannot necessarily be set by SQLSetDescField(). Fields that can be set by SQLSetDescField() are listed in the following tables.
The initialization of header fields is as follows:
Table 157. Initialization of Header Fields
| SQL_DESC_ALLOC_TYPE (SQLSMALLINT) | |||
| R/W: |
| Default: |
|
| SQL_DESC_ARRAY_SIZE (SQLUINTEGER) | |||
| R/W: |
| Default: |
|
| SQL_DESC_ARRAY_STATUS_PTR (SQLUSMALLINT *) | |||
| R/W: |
| Default: |
|
| SQL_DESC_BIND_OFFSET_PTR (SQLINTEGER *) | |||
| R/W: |
| Default: |
|
| SQL_DESC_BIND_TYPE (SQLINTEGER) | |||
| R/W: |
| Default: |
|
| SQL_DESC_COUNT (SQLSMALLINT) | |||
| R/W: |
| Default: |
|
| SQL_DESC_ROWS_PROCESSED_PTR (SQLUINTEGER *) | |||
| R/W: |
| Default: |
|
The initialization of record fields is as follows:
Table 158. Initialization of Record Fields
| SQL_DESC_AUTO_UNIQUE_VALUE (SQLINTEGER) | |||
| R/W: |
| Default: |
|
| SQL_DESC_BASE_COLUMN_NAME (SQLCHAR *) | |||
| R/W: |
| Default: |
|
| SQL_DESC_BASE_TABLE_NAME (SQLCHAR *) | |||
| R/W: |
| Default: |
|
| SQL_DESC_CASE_SENSITIVE (SQLINTEGER) | |||
| R/W: |
| Default: |
|
| SQL_DESC_CATALOG_NAME (SQLCHAR *) | |||
| R/W: |
| Default: |
|
| SQL_DESC_CONCISE_TYPE (SQLSMALLINT) | |||
| R/W: |
| Default: |
|
| SQL_DESC_DATA_PTR (SQLPOINTER) | |||
| R/W: |
| Default: |
|
| SQL_DESC_DATETIME_INTERVAL_CODE (SQLSMALLINT) | |||
| R/W: |
| Default: |
|
| SQL_DESC_DATETIME_INTERVAL_PRECISION (SQLINTEGER) | |||
| R/W: |
| Default: |
|
| SQL_DESC_DISPLAY_SIZE (SQLINTEGER) | |||
| R/W: |
| Default: |
|
| SQL_DESC_FIXED_PREC_SCALE (SQLSMALLINT) | |||
| R/W: |
| Default: |
|
| SQL_DESC_INDICATOR_PTR (SQLINTEGER *) | |||
| R/W: |
| Default: |
|
| SQL_DESC_LABEL (SQLCHAR *) | |||
| R/W: |
| Default: |
|
| SQL_DESC_LENGTH (SQLUINTEGER) | |||
| R/W: |
| Default: |
|
| SQL_DESC_LITERAL_PREFIX (SQLCHAR *) | |||
| R/W: |
| Default: |
|
| SQL_DESC_LITERAL_SUFFIX (SQLCHAR *) | |||
| R/W: |
| Default: |
|
| SQL_DESC_LOCAL_TYPE_NAME (SQLCHAR *) | |||
| R/W: |
| Default: |
|
| SQL_DESC_NAME (SQLCHAR *) | |||
| R/W: |
| Default: |
|
| SQL_DESC_NULLABLE (SQLSMALLINT) | |||
| R/W: |
| Default: |
|
| SQL_DESC_NUM_PREC_RADIX (SQLINTEGER) | |||
| R/W: |
| Default: |
|
| SQL_DESC_OCTET_LENGTH (SQLINTEGER) | |||
| R/W: |
| Default: |
|
| SQL_DESC_OCTET_LENGTH_PTR (SQLINTEGER *) | |||
| R/W: |
| Default: |
|
| SQL_DESC_PARAMETER_TYPE (SQLSMALLINT) | |||
| R/W: |
| Default: |
|
| SQL_DESC_PRECISION (SQLSMALLINT) | |||
| R/W: |
| Default: |
|
| SQL_DESC_SCALE (SQLSMALLINT) | |||
| R/W: |
| Default: |
|
| SQL_DESC_SCHEMA_NAME (SQLCHAR *) | |||
| R/W: |
| Default: |
|
| SQL_DESC_SEARCHABLE (SQLSMALLINT) | |||
| R/W: |
| Default: |
|
| SQL_DESC_TABLE_NAME (SQLCHAR *) | |||
| R/W: |
| Default: |
|
| SQL_DESC_TYPE (SQLSMALLINT) | |||
| R/W: |
| Default: |
|
| SQL_DESC_TYPE_NAME (SQLCHAR *) | |||
| R/W: |
| Default: |
|
| SQL_DESC_UNNAMED (SQLSMALLINT) | |||
| R/W: |
| Default: |
|
| SQL_DESC_UNSIGNED (SQLSMALLINT) | |||
| R/W: |
| Default: |
|
| SQL_DESC_UPDATABLE (SQLSMALLINT) | |||
| R/W: |
| Default: |
|
The FieldIdentifier argument indicates the descriptor field to be set. A descriptor contains the descriptor header, consisting of the header fields described in the next section, and zero or more descriptor records, consisting of the record fields described in the following section.
Each descriptor has a header consisting of the following fields.
SQL_DESC_ALLOC_TYPE [All] This read-only SQLSMALLINT header field specifies whether the descriptor was allocated automatically by DB2 CLI or explicitly by the application. The application can obtain, but not modify, this field. The field is set to SQL_DESC_ALLOC_AUTO if the descriptor was automatically allocated. It is set to SQL_DESC_ALLOC_USER if the descriptor was explicitly allocated by the application.
SQL_DESC_ARRAY_SIZE [Application descriptors] In ARDs, this SQLUINTEGER header field specifies the number of rows in the rowset. This is the number of rows to be returned by a call to SQLFetch(), SQLFetchScroll(), or SQLSetPos(). The default value is 1. The field is also set through the SQL_ATTR_ROW_ARRAY_SIZE statement attribute.
In APDs, this SQLUINTEGER header field specifies the number of values for each parameter.
The default value of this field is 1. If SQL_DESC_ARRAY_SIZE is greater than 1, SQL_DESC_DATA_PTR, SQL_DESC_INDICATOR_PTR, and SQL_DESC_OCTET_LENGTH_PTR of the APD or ARD point to arrays. The cardinality of each array is equal to the value of this field.
This field in the ARD can also be set by calling SQLSetStmtAttr() with the SQL_ATTR_ROWSET_SIZE attribute. This field in the APD can also be set by calling SQLSetStmtAttr() with the SQL_ATTR_PARAMSET_SIZE attribute.
SQL_DESC_ARRAY_STATUS_PTR [All] For each descriptor type, this SQLUSMALLINT * header field points to an array of SQLUSMALLINT values. These arrays are named as follows:
In the IRD, this header field points to a row status array containing status values after a call to SQLFetch(), SQLFetchScroll(), or SQLSetPos(). The array has as many elements as there are rows in the rowset. The application must allocate an array of SQLUSMALLINTs and set this field to point to the array. The field is set to a null pointer by default. DB2 CLI will populate the array, unless the SQL_DESC_ARRAY_STATUS_PTR field is set to a null pointer, in which case no status values are generated and the array is not populated.
| Note: | Behavior is undefined if the application sets the elements of the row status array pointed to by the SQL_DESC_ARRAY_STATUS_PTR field of the IRD. The array is initially populated by a call to SQLFetch(), SQLFetchScroll(), or SQLSetPos(). If the call did not return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, the contents of the array pointed to by this field are undefined. |
The elements in the array can contain the following values:
This field in the ARD can also be set by calling SQLSetStmtAttr() with the SQL_ATTR_ROW_STATUS_PTR attribute.
In the IPD, this header field points to a parameter status array containing status information for each set of parameter values after a call to SQLExecute() or SQLExecDirect(). If the call to SQLExecute() or SQLExecDirect() did not return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, the contents of the array pointed to by this field are undefined. The application must allocate an array of SQLUSMALLINTs and set this field to point to the array. The driver will populate the array, unless the SQL_DESC_ARRAY_STATUS_PTR field is set to a null pointer, in which case no status values are generated and the array is not populated.
The elements in the array can contain the following values:
This field in the APD can also be set by calling SQLSetStmtAttr() with the SQL_ATTR_PARAM_STATUS_PTR attribute.
In the ARD, this header field points to a row operation array of values that can be set by the application to indicate whether this row is to be ignored for SQLSetPos() operations.
The elements in the array can contain the following values:
If no elements of the array are set, all rows are included in the bulk operation. If the value in the SQL_DESC_ARRAY_STATUS_PTR field of the ARD is a null pointer, all rows are included in the bulk operation; the interpretation is the same as if the pointer pointed to a valid array and all elements of the array were SQL_ROW_PROCEED. If an element in the array is set to SQL_ROW_IGNORE, the value in the row status array for the ignored row is not changed.
This field in the ARD can also be set by calling SQLSetStmtAttr() with the SQL_ATTR_ROW_OPERATION_PTR attribute.
In the APD, this header field points to a parameter operation array of values that can be set by the application to indicate whether this set of parameters is to be ignored when SQLExecute() or SQLExecDirect() is called. The elements in the array can contain the following values:
If no elements of the array are set, all sets of parameters in the array are used in the SQLExecute() or SQLExecDirect() calls. If the value in the SQL_DESC_ARRAY_STATUS_PTR field of the APD is a null pointer, all sets of parameters are used; the interpretation is the same as if the pointer pointed to a valid array and all elements of the array were SQL_PARAM_PROCEED.
This field in the APD can also be set by calling SQLSetStmtAttr() with the SQL_ATTR_PARAM_OPERATION_PTR attribute.
SQL_DESC_BIND_OFFSET_PTR [Application descriptors] This SQLINTEGER * header field points to the bind offset. It is set to a null pointer by default. If this field is not a null pointer, DB2 CLI dereferences the pointer and adds the dereferenced value to each of the deferred fields that has a non-null value in the descriptor record (SQL_DESC_DATA_PTR, SQL_DESC_INDICATOR_PTR, and SQL_DESC_OCTET_LENGTH_PTR) at fetch time, and uses the new pointer values when binding.
The bind offset is always added directly to the values in the SQL_DESC_DATA_PTR, SQL_DESC_INDICATOR_PTR, and SQL_DESC_OCTET_LENGTH_PTR fields. If the offset is changed to a different value, the new value is still added directly to the value in each descriptor field. The new offset is not added to the field value plus any earlier offset.
This field is a deferred field: it is not used at the time it is set, but is used at a later time by DB2 CLI to retrieve data.
This field in the ARD can also be set by calling SQLSetStmtAttr() with the SQL_ATTR_ROW_BIND_OFFSET_PTR attribute. This field in the ARD can also be set by calling SQLSetStmtAttr() with the SQL_ATTR_PARAM_BIND_OFFSET_PTR attribute.
See the description of row-wise binding in the "SQLFetchScroll" and "SQLBindParameter" sections.
SQL_DESC_BIND_TYPE [Application descriptors] This SQLINTEGER header field sets the binding orientation to be used for either binding columns or parameters.
In ARDs, this field specifies the binding orientation when SQLFetchScroll() is called on the associated statement handle.
To select column-wise binding for columns, this field is set to SQL_BIND_BY_COLUMN (the default).
This field in the ARD can also be set by calling SQLSetStmtAttr() with SQL_ATTR_ROW_BIND_TYPE Attribute.
In APDs, this field specifies the binding orientation to be used for dynamic parameters.
To select column-wise binding for parameters, this field is set to SQL_BIND_BY_COLUMN (the default).
This field in the APD can also be set by calling SQLSetStmtAttr() with SQL_ATTR_PARAM_BIND_TYPE Attribute.
SQL_DESC_COUNT [All] This SQLSMALLINT header field specifies the one-based index of the highest-numbered record that contains data. When DB2 CLI sets the data structure for the descriptor, it must also set the COUNT field to show how many records are significant. When an application allocates an instance of this data structure, it does not have to specify how many records to reserve room for. As the application specifies the contents of the records, DB2 CLI takes any required action to ensure that the descriptor handle refers to a data structure of the adequate size.
SQL_DESC_COUNT is not a count of all data columns that are bound (if the field is in an ARD), or all parameters that are bound (in an APD), but the number of the highest-numbered record. If a column or a parameter with a number that is less than the number of the highest-numbered column is unbound (by calling SQLBindCol() with the Target ValuePtr argument set to a null pointer, or SQLBindParameter() with the Parameter ValuePtr argument set to a null pointer), SQL_DESC_COUNT is not changed. If additional columns or parameters are bound with numbers greater than the highest-numbered record that contains data, DB2 CLI automatically increases the value in the SQL_DSEC_COUNT field. If all columns or parameters are unbound by calling SQLFreeStmt() with the SQL_UNBIND option, SQL_DESC_COUNT is set to 0.
The value in SQL_DESC_COUNT can be set explicitly by an application by calling SQLSetDescField(). If the value in SQL_DESC_COUNT is explicitly decreased, all records with numbers greater than the new value in SQL_DESC_COUNT are removed, unbinding the columns. If the value in SQL_DESC_COUNT is explicitly set to 0, and the field is in an APD, all parameter columns are unbound. If the value in SQL_DESC_COUNT is explicitly set to 0, and the field is in an ARD, all data buffers except a bound bookmark column are released.
The record count in this field of an ARD does not include a bound bookmark column.
SQL_DESC_ROWS_PROCESSED_PTR [Implementation descriptors] In an IRD, this SQLUINTEGER * header field points to a buffer containing the number of rows fetched after a call to SQLFetch() or SQLFetchScroll(), or the number of rows affected in a bulk operation performed by a call to SQLSetPos().
In an IPD, this SQLUINTEGER * header field points to a buffer containing the number of the row as each row of parameters is processed. No row number will be returned if this is a null pointer.
SQL_DESC_ROWS_PROCESSED_PTR is valid only after SQL_SUCCESS or SQL_SUCCESS_WITH_INFO has been returned after a call to SQLFetch() or SQLFetchScroll()(for an IRD field) or SQLExecute() or SQLExecDirect() (for an IPD field). If the return code is not one of the above, the location pointed to by SQL_DESC_ROWS_PROCESSED_PTR is undefined. If the call that fills in the buffer pointed to by this field did not return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, the contents of the buffer are undefined, unless it returns SQL_NO_DATA, in which case the value in the buffer is set to 0.
This field in the ARD can also be set by calling SQLSetStmtAttr() with the SQL_ATTR_ROWS_FETCHED_PTR attribute. This field in the ARD can also be set by calling SQLSetStmtAttr() with the SQL_ATTR_PARAMS_PROCESSED_PTR attribute.
The buffer pointed to by this field is allocated by the application. It is a deferred output buffer that is set by DB2 CLI. It is set to a null pointer by default.
Each descriptor contains one or more records consisting of fields that define either column data or dynamic parameters, depending on the type of descriptor. Each record is a complete definition of a single column or parameter.
SQL_DESC_AUTO_UNIQUE_VALUE [IRDs] This read-only SQLINTEGER record field contains SQL_TRUE if the column is an auto-incrementing column, or SQL_FALSE if the column is not an auto-incrementing column. This field is read-only, but the underlying auto-incrementing column is not necessarily read-only.
SQL_DESC_BASE_COLUMN_NAME [IRDs] This read-only SQLCHAR record field contains the base column name for the result set column. If a base column name does not exist (as in the case of columns that are expressions), then this variable contains an empty string.
SQL_DESC_BASE_TABLE_NAME [IRDs] This read-only SQLCHAR record field contains the base table name for the result set column. If a base table name cannot be defined or is not applicable, then this variable contains an empty string.
SQL_DESC_CASE_SENSITIVE [Implementation descriptors] This read-only SQLINTEGER record field contains SQL_TRUE if the column or parameter is treated as case-sensitive for collations and comparisons, or SQL_FALSE if the column is not treated as case-sensitive for collations and comparisons, or if it is a non-character column.
SQL_DESC_CATALOG_NAME [IRDs] This read-only SQLCHAR record field contains the catalog or qualifier name for the base table that contains the column. The return value is driver-dependent if the column is an expression or if the column is part of a view. If the data source does not support catalogs (or qualifiers) or the catalog or qualifier name cannot be determined, this variable contains an empty string.
SQL_DESC_CONCISE_TYPE [All] This SQLSMALLINT header field specifies the concise data type for all data types, including the datetime and interval data types.
The values in the SQL_DESC_CONCISE_TYPE and SQL_DESC_TYPE fields are interdependent. Each time one of the fields is set, the other must also be set. SQL_DESC_CONCISE_TYPE can be set by a call to SQLBindCol() or SQLBindParameter(), or SQLSetDescField(). SQL_DESC_TYPE can be set by a call to SQLSetDescField() or SQLSetDescRec().
If SQL_DESC_CONCISE_TYPE is set to a concise data type other than an interval or datetime data type, the SQL_DESC_TYPE field is set to the same value, and the SQL_DESC_DATETIME_INTERVAL_CODE field is set to 0.
If SQL_DESC_CONCISE_TYPE is set to the concise datetime or interval data type, the SQL_DESC_TYPE field is set to the corresponding verbose type (SQL_DATETIME or SQL_INTERVAL), and the SQL_DESC_DATETIME_INTERVAL_CODE field is set to the appropriate subcode.
SQL_DESC_DATA_PTR [Application descriptors and IPDs] This SQLPOINTER record field points to a variable that will contain the parameter value (for APDs) or the column value (for ARDs). The descriptor record (and either the column or parameter that it represents) is unbound if TargetValuePtr in a call to either SQLBindCol() or SQLBindParameter() is a null pointer, or the SQL_DESC_DATA_PTR field in a call to SQLSetDescField() or SQLSetDescRec() is set to a null pointer. Other fields are not affected if the SQL_DESC_DATA_PTR field is set to a null pointer. If the call to SQLFetch() or SQLFetchScroll()that fills in the buffer pointed to by this field did not return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, the contents of the buffer are undefined.
This field is a deferred field: it is not used at the time it is set, but is used at a later time by DB2 CLI to retrieve data.
Whenever the SQL_DESC_DATA_PTR field is set, DB2 CLI checks that the value in the SQL_DESC_TYPE field contains a the valid DB2 CLI or ODBC data types, and that all other fields affecting the data types are consistent. See Consistency Checks.
SQL_DESC_DATETIME_INTERVAL_CODE [All] This SQLSMALLINT record field contains the subcode for the specific datetime data type when the SQL_DESC_TYPE field is SQL_DATETIME. This is true for both SQL and C data types.
This field can be set to the following for datetime data types:
| Datetime types | DATETIME_INTERVAL_CODE |
|---|---|
| SQL_TYPE_DATE/SQL_C_TYPE_DATE | SQL_CODE_DATE |
| SQL_TYPE_TIME/SQL_C_TYPE_TIME | SQL_CODE_TIME |
|
SQL_TYPE_TIMESTAMP/ SQL_C_TYPE_TIMESTAMP | SQL_CODE_TIMESTAMP |
This field can also be set to other values (not listed here) for interval data types, which DB2 CLI does not support.
SQL_DESC_DATETIME_INTERVAL_PRECISION [All] This SQLINTEGER record field contains the interval leading precision if the TYPE field is SQL_INTERVAL (which DB2 CLI does not support).
SQL_DESC_DISPLAY_SIZE [IRDs] This read-only SQLINTEGER record field contains the maximum number of characters required to display the data from the column. The value in this field is not the same as the descriptor field LENGTH because the LENGTH field is undefined for all numeric types.
SQL_DESC_FIXED_PREC_SCALE [Implementation descriptors] This read-only SQLSMALLINT record field is set to SQL_TRUE if the column is an exact numeric column and has a fixed precision and non-zero scale (such as the MONEY data type), or SQL_FALSE if the column is not an exact numeric column with a fixed precision and scale.
SQL_DESC_INDICATOR_PTR [Application descriptors] In ARDs, this SQLINTEGER * record field points to the indicator variable. This variable contains SQL_NULL_DATA if the column value is a NULL. For APDs, the indicator variable is set to SQL_NULL_DATA to specify NULL dynamic arguments. Otherwise, the variable is zero (unless the values in SQL_DESC_INDICATOR_PTR and SQL_DESC_OCTET_LENGTH_PTR are the same pointer).
If the SQL_DESC_INDICATOR_PTR field in an ARD is a null pointer, DB2 CLI is prevented from returning information about whether the column is NULL or not. If the column is NULL and INDICATOR_PTR is a null pointer, SQLSTATE 22002, "Indicator variable required but not supplied," is returned when DB2 CLI attempts to populate the buffer after a call to SQLFetch() or SQLFetchScroll(). If the call to SQLFetch() or SQLFetchScroll() did not return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, the contents of the buffer are undefined.
The SQL_DESC_INDICATOR_PTR field determines whether the field pointed to by SQL_DESC_OCTET_LENGTH_PTR is set. If the data value for a column is NULL, DB2 CLI sets the indicator variable to SQL_NULL_DATA. The field pointed to by SQL_DESC_OCTET_LENGTH_PTR is then not set. If a NULL value is not encountered during the fetch, the buffer pointed to by SQL_DESC_INDICATOR_PTR is set to zero, and the buffer pointed to by SQL_DESC_OCTET_LENGTH_PTR is set to the length of the data.
If the INDICATOR_PTR field in an APD is a null pointer, the application cannot use this descriptor record to specify NULL arguments.
This field is a deferred field: it is not used at the time it is set, but is used at a later time by DB2 CLI to store data.
SQL_DESC_LABEL [IRDs] This read-only SQLCHAR record field contains the column label or title. If the column does not have a label, this variable contains the column name. If the column is unnamed and unlabeled, this variable contains an empty string.
SQL_DESC_LENGTH [All] This SQLUINTEGER record field is either the maximum or actual character length of a character string or a binary data type. It is the maximum character length for a fixed-length data type, or the actual character length for a variable-length data type. Its value always excludes the null termination character that ends the character string. Note that this field is a count of characters, not a count of bytes.
The value in this field may be different from the value for length defined in DB2 CLI Version 2.
SQL_DESC_LITERAL_PREFIX [IRDs] This read-only SQLCHAR record field contains the character or characters that DB2 CLI recognizes as a prefix for a literal of this data type. This variable contains an empty string for a data type for which a literal prefix is not applicable.
SQL_DESC_LITERAL_SUFFIX [IRDs] This read-only SQLCHAR record field contains the character or characters that DB2 CLI recognizes as a suffix for a literal of this data type. This variable contains an empty string for a data type for which a literal suffix is not applicable.
SQL_DESC_LOCAL_TYPE_NAME [Implementation descriptors] This read-only SQLCHAR record field contains any localized (native language) name for the data type that may be different from the regular name of the data type. If there is no localized name, then an empty string is returned. This field is for display purposes only.
SQL_DESC_NAME [Implementation descriptors] This SQLCHAR record field in a row descriptor contains the column alias, if it applies. If the column alias does not apply, the column name is returned. In either case, the UNNAMED field is set to SQL_NAMED. If there is no column name or a column alias, an empty string is returned in the NAME field and the UNNAMED field is set to SQL_UNNAMED.
An application can set the SQL_DESC_NAME field of an IPD to a parameter name or alias to specify stored procedure parameters by name. ( The SQL_DESC_NAME field of an IRD is a read-only field; SQLSTATE HY091 (Invalid descriptor field identifier) will be returned if an application attempts to set it.
In IPDs, this field is undefined if dynamic parameters are not supported. If named parameters are supported and the version of DB2 CLI is capable of describing parameters, then the parameter name is returned in this field.
The column name value can be affected by the environment attribute SQL_ATTR_USE_LIGHT_OUTPUT_SQLDA. See SQLSetEnvAttr - Set Environment Attribute for more information.
SQL_DESC_NULLABLE [Implementation descriptors] In IRDs, this read-only SQLSMALLINT record field is SQL_NULLABLE if the column can have NULL values; SQL_NO_NULLS if the column does not have NULL values; or SQL_NULLABLE_UNKNOWN if it is not known whether the column accepts NULL values. This field pertains to the result set column, not the base column.
In IPDs, this field is always set to SQL_NULLABLE, since dynamic parameters are always nullable, and cannot be set by an application.
SQL_DESC_NUM_PREC_RADIX [All] This SQLINTEGER field contains a value of 2 if the data type in the SQL_DESC_TYPE field is an approximate numeric data type, because the SQL_DESC_PRECISION field contains the number of bits. This field contains a value of 10 if the data type in the SQL_DESC_TYPE field is an exact numeric data type, because the SQL_DESC_PRECISION field contains the number of decimal digits. This field is set to 0 for all non-numeric data types.
SQL_DESC_OCTET_LENGTH [All] This SQLINTEGER record field contains the length, in bytes, of a character string or binary data type. For fixed-length character types, this is the actual length in bytes. For variable-length character or binary types, this is the maximum length in bytes. This value always excludes space for the null termination character for implementation descriptors and always includes space for the null termination character for application descriptors. For application data, this field contains the size of the buffer. For APDs, this field is defined only for output or input/output parameters.
SQL_DESC_OCTET_LENGTH_PTR [Application descriptors] This SQLINTEGER * record field points to a variable that will contain the total length in bytes of a dynamic argument (for parameter descriptors) or of a bound column value (for row descriptors).
For an APD, this value is ignored for all arguments except character string and binary; if this field points to SQL_NTS, the dynamic argument must be null-terminated. To indicate that a bound parameter will be a data-at-execute parameter, an application sets this field in the appropriate record of the APD to a variable that, at execute time, will contain the value SQL_DATA_AT_EXEC. If there is more than one such field, SQL_DESC_DATA_PTR can be set to a value uniquely identifying the parameter to help the application determine which parameter is being requested.
If the OCTET_LENGTH_PTR field of an ARD is a null pointer, DB2 CLI does not return length information for the column. If the SQL_DESC_OCTET_LENGTH_PTR field of an APD is a null pointer, DB2 CLI assumes that character strings and binary values are null terminated. (Binary values should not be null terminated, but should be given a length, in order to avoid truncation.)
If the call to SQLFetch() or SQLFetchScroll() that fills in the buffer pointed to by this field did not return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, the contents of the buffer are undefined.
This field is a deferred field: it is not used at the time it is set, but is used at a later time by DB2 CLI to buffer data.
By default this is a pointer to a 4-byte value. You can set the SQL_ATTR_USE_2BYTES_OCTET_LENGTH environment variable to change this. See SQLSetEnvAttr - Set Environment Attribute for more details.
SQL_DESC_PARAMETER_TYPE [IPDs]This SQLSMALLINT record field is set to SQL_PARAM_INPUT for an input parameter, SQL_PARAM_INPUT_OUTPUT for an input/output parameter, or SQL_PARAM_OUTPUT for an output parameter. Set to SQL_PARAM_INPUT by default.
For an IPD, the field is set to SQL_PARAM_INPUT by default if the IPD is not automatically populated by DB2 CLI (the SQL_ATTR_ENABLE_AUTO_IPD statement attribute is SQL_FALSE). An application should set this field in the IPD for parameters that are not input parameters.
SQL_DESC_PRECISION [All] This SQLSMALLINT record field contains the number of digits for an exact numeric type, the number of bits in the mantissa (binary precision) for an approximate numeric type, or the numbers of digits in the fractional seconds component for the SQL_TYPE_TIME, SQL_TYPE_TIMESTAMP, or SQL_INTERVAL_SECOND data type. This field is undefined for all other data types.
The value in this field may be different from the value for precision defined in DB2 CLI Version 2.
SQL_DESC_SCALE [All] This SQLSMALLINT record field contains the defined scale for DECIMAL and NUMERIC data types. The field is undefined for all other data types.
The value in this field may be different from the value for scale defined in DB2 CLI Version 2. For more information, see Appendix D, "Data Types."
SQL_DESC_SCHEMA_NAME [IRDs] This read-only SQLCHAR record field contains the schema name of the base table that contains the column. For many DBMS's, this is the owner name. If the data source does not support schemas (or owners) or the schema name cannot be determined, this variable contains an empty string.
SQL_DESC_SEARCHABLE [IRDs] This read-only SQLSMALLINT record field is set to one of the following values:
SQL_DESC_TABLE_NAME [IRDs] This read-only SQLCHAR record field contains the name of the base table that contains this column.
SQL_DESC_TYPE [All] This SQLSMALLINT record field specifies the concise SQL or C data type for all data types except datetime and interval data types. For the datetime and interval data types, this field specifies the verbose data type, i.e., SQL_DATETIME or SQL_INTERVAL.
Whenever this field contains SQL_DATETIME or SQL_INTERVAL, the SQL_DESC_DATETIME_INTERVAL_CODE field must contain the appropriate subcode for the concise type. For datetime data types, SQL_DESC_TYPE contains SQL_DATETIME, and the SQL_DESC_DATETIME_INTERVAL_CODE field contains a subcode for the specific datetime data type. For interval data types, SQL_DESC_TYPE contains SQL_INTERVAL, and the SQL_DESC_DATETIME_INTERVAL_CODE field contains a subcode for the specific interval data type.
The values in the SQL_DESC_TYPE and SQL_DESC_CONCISE_TYPE fields are interdependent. Each time one of the fields is set, the other must also be set. SQL_DESC_TYPE can be set by a call to SQLSetDescField() or SQLSetDescRec(). SQL_DESC_CONCISE_TYPE can be set by a call to SQLBindCol() or SQLBindParameter(), or SQLSetDescField().
If SQL_DESC_TYPE is set to a concise data type other than an interval or datetime data type, the SQL_DESC_CONCISE_TYPE field is set to the same value, and the SQL_DESC_DATETIME_INTERVAL_CODE field is set to 0.
If SQL_DESC_TYPE is set to the verbose datetime or interval data type (SQL_DATETIME or SQL_INTERVAL), and the SQL_DESC_DATETIME_INTERVAL_CODE field is set to the appropriate subcode, the SQL_DESC_CONCISE TYPE field is set to the corresponding concise type. Trying to set SQL_DESC_TYPE to one of the concise datetime or interval types will return SQLSTATE HY021 (Inconsistent descriptor information).
When the SQL_DESC_TYPE field is set by a call to
SQLSetDescField(), the following fields are set to the following
default values. The values of the remaining fields of the same record
are undefined:
| SQL_DESC_TYPE | Other fields Implicitly Set |
|---|---|
|
SQL_CHAR, SQL_VARCHAR | SQL_DESC_LENGTH is set to 1. SQL_DESC_PRECISION is set to 0. |
|
SQL_DECIMAL, SQL_NUMERIC | SQL_DESC_SCALE is set to 0. SQL_DESC_PRECISION is set to the precision for the respective data type. |
| SQL_FLOAT | SQL_DESC_PRECISION is set to the default precision for SQL_FLOAT. |
| SQL_DATETIME | This datatype is not supported by DB2 CLI. |
| SQL_INTERVAL | This datatype is not supported by DB2 CLI. |
When an application calls SQLSetDescField() to set fields of a descriptor, rather than calling SQLSetDescRec(), the application must first declare the data type. If the values implicitly set are unacceptable, the application can then call SQLSetDescField() to set the unacceptable value explicitly.
SQL_DESC_TYPE_NAME [Implementation descriptors] This read-only SQLCHAR record field contains the data-source-dependent type name (for example, "CHAR", "VARCHAR", and so on). If the data type name is unknown, this variable contains an empty string.
SQL_DESC_UNNAMED [Implementation descriptors] This SQLSMALLINT record field in a row descriptor is set to either SQL_NAMED or SQL_UNNAMED. If the NAME field contains a column alias, or if the column alias does not apply, the UNNAMED field is set to SQL_NAMED. If there is no column name or a column alias, the UNNAMED field is set to SQL_UNNAMED.
An application can set the SQL_DESC_UNNAMED field of an IPD to SQL_UNNAMED. SQLSTATE HY091 (Invalid descriptor field identifier) is returned if an application attempts to set the SQL_DESC_UNNAMED field of an IPD to SQL_NAMED. The SQL_DESC_UNNAMED field of an IRD is read-only; SQLSTATE HY091 (Invalid descriptor field identifier) will be returned if an application attempts to set it.
SQL_DESC_UNSIGNED [Implementation descriptors] This read-only SQLSMALLINT record field is set to SQL_TRUE if the column type is unsigned or non-numeric, or SQL_FALSE if the column type is signed.
SQL_DESC_UPDATABLE [IRDs] This read-only SQLSMALLINT record field is set to one of the following values:
SQL_DESC_UPDATABLE describes the updatability of the column in the result set, not the column in the base table. The updatability of the column in the base table on which this result set column is based may be different than the value in this field. Whether a column is updatable can be based on the data type, user privileges, and the definition of the result set itself. If it is unclear whether a column is updatable, SQL_UPDT_READWRITE_UNKNOWN should be returned.
A consistency check is performed by DB2 CLI automatically whenever an application passes in a value for the SQL_DESC_DATA_PTR field of the ARD, APD, or IPD. If any of the fields is inconsistent with other fields, SQLSetDescField() will return SQLSTATE HY021, "Inconsistent descriptor information." For more information see SQLSetDescRec(), Consistency Checks.
Return Codes
Diagnostics
Table 161. SQLSetDescField SQLSTATEs
| SQLSTATE | Description | Explanation |
|---|---|---|
| 01000 | General warning | Informational message. (Function returns SQL_SUCCESS_WITH_INFO.) |
| 01S02 | Option value changed. | DB2 CLI did not support the value specified in *ValuePtr (if ValuePtr was a pointer) or the value in ValuePtr (if ValuePtr was a four-byte value), or *ValuePtr was invalid because of SQL constraints or requirements, so DB2 CLI substituted a similar value. (Function returns SQL_SUCCESS_WITH_INFO.) |
| 07009 | Invalid descriptor index. | The FieldIdentifier argument was a header field, and the
RecNumber argument was not 0.
The RecNumber argument was 0 and the DescriptorHandle was an IPD. The RecNumber argument was less than 0. |
| 08S01 | Communication link failure. | The communication link between DB2 CLI and the data source to which it was connected failed before the function completed processing. |
| HY000 | General error. | An error occurred for which there was no specific SQLSTATE. The error message returned by SQLGetDiagRec() in the *MessageText buffer describes the error and its cause. |
| HY001 | Memory allocation failure. | DB2 CLI was unable to allocate memory required to support execution or completion of the function. |
| HY010 | Function sequence error. | The DescriptorHandle was associated with a StatementHandle for
which an asynchronously executing function (not this one) was called and was
still executing when this function was called.
SQLExecute() or SQLExecDirect() was called for the StatementHandle with which the DescriptorHandle was associated and returned SQL_NEED_DATA. This function was called before data was sent for all data-at-execution parameters or columns. |
| HY016 | Cannot modify an implementation row descriptor. | The DescriptorHandle argument was associated with an IRD, and the FieldIdentifier argument was not SQL_DESC_ARRAY_STATUS_PTR. |
| HY021 | Inconsistent descriptor information. | The TYPE field, or any other field associated with the TYPE field in the
descriptor, was not valid or consistent. The TYPE field was not a valid
DB2 CLI C type.
Descriptor information checked during a consistency check was not consistent. (see Consistency Checks) |
| HY091 | Descriptor type out of range. | The value specified for the FieldIdentifier argument was not a
DB2 CLI defined field and was not a defined value.
The value specified for the RecNumber argument was greater than the value in the SQL_DESC_COUNT field. The FieldIdentifier argument was SQL_DESC_ALLOC_TYPE. |
| HY092 | Option type out of range. | The value specified for the Attribute argument was not valid. |
| HY105 | Invalid parameter type. | The value specified for the SQL_DESC_PARAMETER_TYPE field was invalid. (For more information, see the InputOutputType Argument section in SQLBindParameter().) |
Restrictions
None.
Example
See the README file in the sqllib\samples\cli (or sqllib/samples/cli) subdirectory for a list of appropriate samples.
References