You use SET DESCRIPTOR to update an SQL descriptor area. You can update the values for the descriptor area field COUNT or the contents of an item descriptor.
See section "Descriptor area" for information on the structure and use of the descriptor area.
The SQL descriptor area must be created beforehand.
SET DESCRIPTOR GLOBAL descriptor
{ COUNT= number |
VALUE item_number field_id = field_contents [, field_id = field_contents ]... }
number ::= { integer | host_variable }
item_number ::= { integer | host_variable }
field_id ::=
{
REPETITIONS |
TYPE |
DATETIME_INTERVAL_CODE |
PRECISION |
SCALE |
LENGTH |
INDICATOR |
DATA
}
field_contents ::= { host_variable | { number | host_variable }
descriptor
Name of the SQL descriptor area containing the items to be updated.
You cannot update the items in this descriptor area if there is an open cursor with block mode activated (see section "PREFETCH pragma") and a FETCH NEXT... statement whose INTO clause contains the name of the same SQL descriptor area has been executed for this cursor.
COUNT=number
The COUNT field is set to the value of number.
number
For number, specify an integer or a host variable of the SQL data type SMALLINT, where
0 <= number <= defined maximum number of item descriptors
The contents of item descriptors with an item number greater than number are undefined.
VALUE clause
The specified field of the item descriptor with the item number item_number are set to the specified field contents.
If you specify several fields, they are set in the following order regardless of the order in which you specify them in the SET DESCRIPTOR statement:
REPETITIONS
TYPE
DATETIME_INTERVAL_CODE
PRECISION
SCALE
LENGTH
INDICATOR
DATA
item_number
Number of the item descriptor to be updated.
The items in the descriptor area are numbered sequentially starting with 1.
For item_number you can specify an integer or a host variable of the type SMALLINT, where
1 <= item_number<= COUNT and <= defined maximum number of items
field_id
Field of item descriptor item_number to be updated. You can only specify the same field identifier once.
field_contents
New value for the field field_id
If field_id is DATA, you must specify a host variable for field_contents. Otherwise you can specify an integer or a host variable of the SQL data type SMALLINT for field_contents. You cannot specify an aggregate or vector for any field except DATA and INDICATOR.
REPETITIONS
The value specified for field_contents must be >= 1 and <= 255.
The fields TYPE, DATETIME_INTERVAL_CODE PRECISION, SCALE, and LENGTH are set to the same value for the item descriptors with the item numbers item_number, item_number+1, ..., item_number+REPETITIONS-1,
provided that the item numbers are <= COUNT and <= defined maximum number of item descriptors.
The REPETITIONS field is set to the value of field_contents for item_number. REPETITIONS is set to 1 for the item descriptors with the item numbers item_number+1, ..., item_number+REPETITIONS-1.
The other fields for the items involved are set to the value specified or are undefined.
REPETITIONS omitted:
REPETITIONS is set to 1 for item_number.
TYPE
Sets the TYPE field. The contents of the DATETIME_INTERVAL_CODE field of the same item descriptor are undefined. The fields PRECISION, SCALE and LENGTH of the same item descriptor are set to default values, depending on the value of the TYPE field:
SQL data type | TYPE | PRECISION | SCALE | LENGTH |
NVARCHAR | -42 | 1 | ||
NCHAR | -31 | 1 | ||
CHAR | 1 | 1 | ||
NUMERIC | 2 | 1 | 0 | |
DECIMAL | 3 | 1 | 0 | |
INTEGER | 4 | |||
SMALLINT | 5 | |||
FLOAT | 6 | 1 | ||
REAL | 7 | |||
DOUBLE PRECISION | 8 | |||
DATE, TIME, TIMESTAMP | 9 | 0 | ||
VARCHAR | 12 | 1 |
Table 51: Setting the TYPE field of an item descriptor
Values not specified are undefined.
Except for REPETITIONS, the values of all the other fields for this item descriptor are undefined.
DATETIME_INTERVAL_CODE
Depending on the value of DATETIME_INTERVAL_CODE, the value of the RECISION field is set as follows:
DATETIME_INTERVAL_CODE | PRECISION |
1 | 0 |
2 | 0 |
3 | 6 |
Table 52: Setting the DATETIME_INTERVAL_CODE field of an item descriptor
Except for REPETITIONS and TYPE, the values of all the other fields for this item descriptor are undefined.
PRECISION, SCALE, LENGTH
The fields are set in this order. If the TYPE field is already set and PRECISION, SCALE or LENGTH contain default values, these are overwritten.
The value of the DATA field for this item descriptor is undefined.
INDICATOR
If you specify a vector with several elements, a corresponding number of INDICATOR fields for the subsequent item descriptors are set, provided that the item numbers of these items are <= COUNT and <= defined maximum number of item descriptors.
DATA
The data type of the host variable must match the data type indicated by the TYPE, LENGTH, PRECISION, SCALE and DATETIME_INTERVAL_CODE fields of the same item descriptor (see section "Transferring values between host variables and a descriptor area"). If the specified host variable is a vector with several elements, the TYPE, LENGTH, PRECISION, SCALE and
DATETIME_INTERVAL_CODE fields of exactly the same number of subsequent item descriptors must specify the same data type, and the item number of these item descriptors must be <= COUNT and <= defined maximum number of item descriptors.
If DATA and INDICATOR are specified, both must be atomic values or vectors with the same number of elements.
The DATA field is set if the associated INDICATOR field is >= 0. Otherwise the contents of the DATA field are undefined.
Examples
The type and number of decimal digits and number of digits after the decimal point in the second item descriptor in the SQL descriptor area :DEMO_DESC are changed:
SET DESCRIPTOR GLOBAL :demo_desc
VALUE 2 TYPE = 2, PRECISIONS = 7, SCALE = 2
Set the number of item descriptors in the SQL descriptor area DEMO_DESC to zero:
SET DESCRIPTOR GLOBAL :demo_desc COUNT = 0
See also
ALLOCATE DESCRIPTOR, DEALLOCATE DESCRIPTOR, DESCRIBE, GET DESCRIPTOR