An SQL descriptor area is a more sophisticated method for
processing the result of a SELECT
or
FETCH
statement. An SQL descriptor area groups
the data of one row of data together with metadata items into one
data structure. The metadata is particularly useful when executing
dynamic SQL statements, where the nature of the result columns may
not be known ahead of time.
An SQL descriptor area consists of a header, which contains information concerning the entire descriptor, and one or more item descriptor areas, which basically each describe one column in the result row.
Before you can use an SQL descriptor area, you need to allocate one:
EXEC SQL ALLOCATE DESCRIPTOR identifier
;
The identifier serves as the “variable name” of the descriptor area. When you don't need the descriptor anymore, you should deallocate it:
EXEC SQL DEALLOCATE DESCRIPTOR identifier
;
To use a descriptor area, specify it as the storage target in an
INTO
clause, instead of listing host variables:
EXEC SQL FETCH NEXT FROM mycursor INTO DESCRIPTOR mydesc;
Now how do you get the data out of the descriptor area? You can think of the descriptor area as a structure with named fields. To retrieve the value of a field from the header and store it into a host variable, use the following command:
EXEC SQL GET DESCRIPTORname
:hostvar
=field
;
Currently, there is only one header field defined:
COUNT
, which tells how many item
descriptor areas exist (that is, how many columns are contained in
the result). The host variable needs to be of an integer type. To
get a field from the item descriptor area, use the following
command:
EXEC SQL GET DESCRIPTORname
VALUEnum
:hostvar
=field
;
num
can be a literal integer or a host
variable containing an integer. Possible fields are:
CARDINALITY
(integer)number of rows in the result set
DATA
actual data item (therefore, the data type of this field depends on the query)
DATETIME_INTERVAL_CODE
(integer)?
DATETIME_INTERVAL_PRECISION
(integer)not implemented
INDICATOR
(integer)the indicator (indicating a null value or a value truncation)
KEY_MEMBER
(integer)not implemented
LENGTH
(integer)length of the datum in characters
NAME
(string)name of the column
NULLABLE
(integer)not implemented
OCTET_LENGTH
(integer)length of the character representation of the datum in bytes
PRECISION
(integer) precision (for type numeric
)
RETURNED_LENGTH
(integer)length of the datum in characters
RETURNED_OCTET_LENGTH
(integer)length of the character representation of the datum in bytes
SCALE
(integer) scale (for type numeric
)
TYPE
(integer)numeric code of the data type of the column