SQL descriptor area

In the dynamic SQL statements described up to this point, the number and data types of the placeholders and derived columns for a dynamically compilable SQL statement or a dynamic cursor description were known at the time the ESQL program was compiled. So-called descriptor areas are available in SQL to allow you to use any number and type of input parameters and to process any results from queries in ESQL programs.

A descriptor area is an area in memory in which values and information on the number and data types of inputs (placeholders) and outputs (derived columns) in dynamically compiled SQL statements or cursor descriptions are stored.

Every descriptor area comprises a number of items. One item is created for every simple column or value or, in the case of multiple columns or aggregates, for every column element or every occurrence. Every item is subdivided into fields. The values in these fields can be set with DESCRIBE or SET DESCRIPTOR or read with GET DESCRIPTOR. Depending on the type of the item, only certain fields are assigned values. Every descriptor area has a field with the name COUNT, which contains the number of entries in the descriptor area.

The descriptor area contains the following fields for a given item:

Keyword	Meaning	Can be set in SQL program
COUNT	Number of items in the descriptor area	yes
TYPE	Data type of item: -42 NVARCHAR -31 NCHAR 1 CHAR 2 NUMERIC 3 DECIMAL 4 INTEGER 5 SMALLINT 6 FLOAT 7 REAL 8 DOUBLE PRECISION 9 Time and date data type 12 VARCHAR	yes
LENGTH	Length or maximum length in characters in the case of alphanumeric data types and time data types or length or maximum length in code units in the case of national data types	yes
OCTET_LENGTH	Maximum memory requirement in bytes in the case of alphanumeric data types, national data types, numeric data types and time data types	No, can be read only
PRECISION	Only for numeric data types and TIME and TIMESTAMP: Total number of decimal digits (for NUMERIC, DECIMAL, TIME, TIMESTAMP) or binary digits (in all other cases)	yes
SCALE	Only for integers and fixed-point numbers: number of digits after the decimal point	yes
DATETIME_ INTERVAL_CODE	Only for time and date data types (TYPE =9): 1 DATE2 TIME3 TIMESTAMP	yes
REPETITIONS	For multiple columns and aggregates: contains the number of components in the first item and 1 in the following items. For single columns: contains the value 1 only.	yes
NULLABLE	1 Output value can be NULL 0 in all other cases	no
INDICATOR	In a descriptor area for derived columns: 0 DATA contains the value read -1 NULL was read >0 Original length of an alphanumeric or a national value if information was lost during transfer In a descriptor area for placeholders: <0 Value is NULL 0 in all other cases	yes
DATA	Value of an item. The data type is defined in by the entries in TYPE, LENGTH, PRECISION, SCALE and DATETIME_INTERVAL_CODE	yes
NAME	Column name	no
UNNAMED	1 NAME contains column name 0 in all other cases	no

Tabelle 37: Fields of a descriptor area

The COUNT field occurs once in every descriptor area. The data type of NAME is CHAR( n) or VARCHAR(n) with n >= 128 and the data type of DATA is determined by the entries in TYPE, LENGTH, PRECISION and SCAL. The remaining fields have the data type SMALLINT.

Users do not need to concern themselves with the internal structure of the SQL descriptor area, but can address the fields of the descriptor area using SQL statements. Depending on the statement concerned, only certain fields in the descriptor area are addressed.

There are a number of SQL statements and variants of the EXECUTE, OPEN and FETCH statements available for working with an SQL descriptor area:

• Creating a descriptor area
  A descriptor area must first be created with ALLOCATE DESCRIPTOR. This statement specifies the maximum number of items in the descriptor area. The items in the descriptor area are undefined after the ALLOCATE DESCRIPTOR statement has been executed.

• Describe the number and data types of the placeholders
  DESCRIBE INPUT allows you to describe the number and data types in a dynamically compiled SQL statement or cursor description and to store these in a descriptor area.

• Pass values for placeholders
  The values for the placeholders in a dynamically compiled statement are taken from a descriptor area in the EXECUTE... USING SQL DESCRIPTOR statement. The values for the placeholders in a dynamically compiled cursor description are taken from a descriptor area in the OPEN... USING SQL DESCRIPTOR statement. In both cases, the required values must first be set using SET DESCRIPTOR.

• Describe the number and data types of the derived columns
  DESCRIBE OUTPUT allows you to describe the number and data types of the derived columns in a dynamically compiled SELECT statement or cursor description and to store these in a descriptor area.

• Store column values from a derived row
  The column values from a derived row in a dynamically compiled SELECT statement are stored in a descriptor area with EXECUTE... INTO SQL DESCRIPTOR. The column values of a row of the derived table in a dynamic cursor are stored in a descriptor area with FETCH... INTO SQL DESCRIPTOR.

• Modifying the descriptor area
  The number of items or the contents of an item of the descriptor area can be modified with SET DESCRIPTOR.

• Querying the descriptor area
  The number of items or the contents of fields in an item of a descriptor area can be queried with GET DESCRIPTOR.

• Releasing the descriptor area
  The memory occupied by a descriptor area can be released with DEALLOCATE DESCRIPTOR.