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 fieldsField |
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.