Descriptors in CLI applications

In CLI, you can store information, such as data types, sizes, and pointers, about columns in a result set and the associated parameters in an SQL statement. Descriptors are a logical view of this information and provide a way for applications to query and update this information.

The bindings of application buffers to columns and parameters must also be stored. Descriptors

Many CLI functions make use of descriptors, but the application itself does not need to manipulate them directly.

For instance:
  • When an application binds column data using SQLBindCol(), descriptor fields are set that completely describe the binding.
  • A number of statement attributes correspond to the header fields of a descriptor. In this case you can achieve the same effect calling SQLSetStmtAttr() as calling the corresponding function SQLSetDescField() that sets the values in the descriptor directly.

Although no database operations require direct access to descriptors, there are situations where working directly with the descriptors will be more efficient or result in simpler code. For instance, a descriptor that describes a row fetched from a table can then be used to describe a row inserted back into the table.

There are four types of descriptors:
Application Parameter Descriptor (APD)
Describes the application buffers (pointers, data types, scale, precision, length, maximum buffer length, and so on) that are bound to parameters in an SQL statement. If the parameters are part of a CALL statement they may be input, output, or both. This information is described using the application's C data types.
Application Row Descriptor (ARD)
Describes the application buffers bound to the columns. The application may specify different data types from those in the implementation row descriptor (IRD) to achieve data conversion of column data. This descriptor reflects any data conversion that the application may specify.
Implementation Parameter Descriptor (IPD)
Describes the parameters in the SQL statement (SQL type, size, precision, and so on).
  • If the parameter is used as input, this describes the SQL data that the database server will receive after CLI has performed any required conversion.
  • If the parameter is used as output, this describes the SQL data before CLI performs any required conversion to the application's C data types.
Implementation Row Descriptor (IRD)
Describes the row of data from the result set before CLI performs any required data conversion to the application's C data types.

The only difference between four types of descriptors is how they are used. One of the benefits of descriptors is that a single descriptor can be used to serve multiple purposes. For instance, a row descriptor in one statement can be used as a parameter descriptor in another statement.

As soon as a descriptor exists, it is either an application descriptor or an implementation descriptor. This is the case even if the descriptor has not yet been used in a database operation. If the descriptor is allocated by the application using SQLAllocHandle() then it is an application descriptor.

Values stored in a descriptor

Each descriptor contains both header fields and record fields. These fields together completely describe the column or parameter.

Header fields

Each header field occurs once in each descriptor. Changing one of these fields affects all columns or parameters.

Many of the following header fields correspond to a statement attribute. Setting the header field of the descriptor using SQLSetDescField() is the same as setting the corresponding statement attribute using SQLSetStmtAttr(). The same holds true for retrieving the information using SQLGetDescField() or SQLGetStmtAttr(). If your application does not already have a descriptor handle allocated then it is more efficient to use the statement attribute calls instead of allocating the descriptor handle, and then using the descriptor calls.

The list of the header fields are:
  • SQL_DESC_ALLOC_TYPE
  • SQL_DESC_BIND_TYPEa
  • SQL_DESC_ARRAY_SIZEa
  • SQL_DESC_COUNT
  • SQL_DESC_ARRAY_STATUS_PTRa
  • SQL_DESC_ROWS_PROCESSED_PTRa
  • SQL_DESC_BIND_OFFSET_PTRa
Note:
a
This header field corresponds to a statement attribute.

The descriptor header field SQL_DESC_COUNT is the one-based index of the highest-numbered descriptor record that contains information (and not a count of the number of columns or parameters). CLI automatically updates this field (and the physical size of the descriptor) as columns or parameters are bound and unbound. The initial value of SQL_DESC_COUNT is 0 when a descriptor is first allocated.

Descriptor records

A single descriptor can contain zero or more descriptor records. As new columns or parameters are bound, new descriptor records can be added to the descriptor. The descriptor record is removed when a column or parameter is unbound.

The fields in a descriptor record describe a column or parameter. A specific field occurs only once in each descriptor record. The fields in a descriptor record follow:
  • SQL_DESC_AUTO_UNIQUE_VALUE
  • SQL_DESC_BASE_COLUMN_NAME
  • SQL_DESC_BASE_TABLE_NAME
  • SQL_DESC_CARDINALITY
  • SQL_DESC_CARDINALITY_PTR
  • SQL_DESC_CASE_SENSITIVE
  • SQL_DESC_CATALOG_NAME
  • SQL_DESC_CONCISE_TYPE
  • SQL_DESC_DATA_PTR
  • SQL_DESC_DATETIME_INTERVAL_CODE
  • SQL_DESC_DATETIME_INTERVAL_PRECISION
  • SQL_DESC_DISPLAY_SIZE
  • SQL_DESC_FIXED_PREC_SCALE
  • SQL_DESC_IDENTITY_VALUE
  • SQL_DESC_INDICATOR_PTR
  • SQL_DESC_LABEL
  • SQL_DESC_LENGTH
  • SQL_DESC_LITERAL_PREFIX
  • SQL_DESC_LITERAL_SUFFIX
  • SQL_DESC_LOCAL_TYPE_NAME
  • SQL_DESC_NAME
  • SQL_DESC_NULLABLE
  • SQL_DESC_OCTET_LENGTH
  • SQL_DESC_OCTET_LENGTH_PTR
  • SQL_DESC_PARAMETER_TYPE
  • SQL_DESC_PRECISION
  • SQL_DESC_SCALE
  • SQL_DESC_SCHEMA_NAME
  • SQL_DESC_SEARCHABLE
  • SQL_DESC_TABLE_NAME
  • SQL_DESC_TYPE
  • SQL_DESC_TYPE_NAME
  • SQL_DESC_UNNAMED
  • SQL_DESC_UNSIGNED
  • SQL_DESC_UPDATABLE

Deferred fields

Deferred fields are created when the descriptor header or a descriptor record is created. The addresses of the defined variables are stored but not used until a later point in the application. The application must not deallocate or discard these variables between the time it associates them with the fields and the time CLI reads or writes them.

The following table lists the deferred fields and the meaning or a null pointer where applicable:
Table 1. Deferred fields
Field Meaning of Null value
SQL_DESC_DATA_PTR The record is unbound.
SQL_DESC_INDICATOR_PTR (none)
SQL_DESC_OCTET_LENGTH_PTR (ARD and APD only)
  • ARD: The length information for that column is not returned.
  • APD: If the parameter is a character string, the driver assumes that string is null-terminated. For output parameters, a null value in this field prevents the driver from returning length information. (If the SQL_DESC_TYPE field does not indicate a character-string parameter, the SQL_DESC_OCTET_LENGTH_PTR field is ignored.)
SQL_DESC_ARRAY_STATUS_PTR (multirow fetch only) A multirow fetch failed to return this component of the per-row diagnostic information.
SQL_DESC_ROWS_PROCESSED_PTR (multirow fetch only) (none)
SQL_DESC_CARDINALITY_PTR (none)

Bound descriptor records

The SQL_DESC_DATA_PTR field in each descriptor record points to a variable that contains the parameter value (for APDs) or the column value (for ARDs). This is a deferred field that defaults to null. When the column or parameter is bound, it points to the parameter or column value. At this point the descriptor record is said to be bound.
Application Parameter Descriptors (APD)
Each bound record constitutes a bound parameter. The application must bind a parameter for each input and output parameter marker in the SQL statement before the statement is executed.
Application Row Descriptors (ARD)
Each bound record relates to a bound column.