GET DESCRIPTOR

Gets values from an SQL descriptor area.

where item-information is:

Usage

Embedded, Module.

Description

Values are retrieved from the specified SQL descriptor area. The GET DESCRIPTOR statement can be used in two forms:

•To determine the number of active item descriptor areas for the specified SQL descriptor, the form …host-variable = COUNT is used. TOP_LEVEL_COUNT is used to determine the top level columns (or parameters).

•The VALUE form is used to retrieve SQL descriptor field values from the item descriptor area specified by item-number.

When using GET DESCRIPTOR with DESCRIBE OUTPUT, if COUNT > 0, the output is a result set and a cursor should be used to retrieve data.

The descriptor-name is identified by a host variable or a literal.

Item Descriptor Area

An item descriptor area contains the following fields:

Field name	Description
CHARACTER_SET_CATALOG	catalog name for the character set which the described item is using.
CHARACTER_SET_SCHEMA	A character string containing the schema name for the character set which the described item is using.
CHARACTER_SET_NAME	A character string containing the name of the character set which the described item is using.
COLLATION_CATALOG	A character string containing the catalog name for the collation which the described item is using.
COLLATION_SCHEMA	A character string containing the schema name for the collation which the described item is using.
COLLATION_NAME	A character string containing the name of the collation which the described item is using.
DATA	If the INDICATOR field does not indicate a null value, this field contains an input (OPEN or EXECUTE) or output (FETCH or EXECUTE) value with the data type specified by the TYPE field, and with the attributes specified by the applicable fields in the item descriptor area.
DATETIME_INTERVAL_CODE	If the TYPE field contains 9 or 10 (i.e. for DATETIME and INTERVAL data types), this column will contain an exact numeric value with scale 0 which specifies the DATETIME or INTERVAL subtype. See below for descriptions of the codes that apply to these two data types.
DATETIME_INTERVAL_PRECISION	An exact numeric value with scale 0, which specifies the leading field precision for the INTERVAL data type (i.e. when the TYPE value is 10).
INDICATOR	An exact numeric value with scale 0, used as a null indicator for input (OPEN or EXECUTE) or output (FETCH or EXECUTE with an INTO clause) values. INDICATOR=-1 indicates a null value, and INDICATOR=0 indicates a non-null value. If INDICATOR is > 0 after a FETCH operation or an EXECUTE operation with INTO clause, it indicates that a truncation occurred and the value of INDICATOR is the required length.
LENGTH	An exact numeric value with scale 0, containing the string length of a character or binary string data type. Terminating null bytes are excluded.
LEVEL	An exact numeric value with scale 0, which identifies the item descriptor area’s level. Level 0 is the top level.
NAME	A character string containing the column name, returned by DESCRIBE OUTPUT. After DESCRIBE INPUT this field contains a question mark (?).
NULLABLE	An exact numeric value with scale 0, indicating whether a resulting column can contain null or not. NULLABLE=1 indicates that null is allowed. NULLABLE=0 indicates that null is not allowed.
OCTET_LENGTH	An exact numeric value with scale 0, containing the number of octets of a character or binary string data type. Terminating null bytes are excluded.
PARAMETER_MODE	An exact numeric value with scale 0, which specifies the MODE of a routine parameter. See below for a description of the code values for this field.
PARAMETER_ORDINAL_POSITION	An exact numeric value with scale 0, indicating the ordinal position of the parameter in the parameter list of the routine definition.
PARAMETER_SPECIFIC_CATALOG	A character string representing the catalog name for the invoked procedure, if the prepared statement is a call statement.
PARAMETER_SPECIFIC_SCHEMA	A character string representing the schema name for the invoked procedure, if the prepared statement is a call statement.
PARAMETER_SPECIFIC_NAME	A character string representing the name of the invoked procedure, if the prepared statement is a call statement.
PRECISION	An exact numeric value with scale 0, specifying the precision for a numeric data type value. For the data types: INTERVAL DAY TO SECOND INTERVAL HOUR TO SECOND INTERVAL MINUTE TO SECOND INTERVAL SECOND TIME and TIMESTAMP, the value in this field describes the precision of the fractional SECONDS component.
RETURNED_LENGTH	An exact numeric value with scale 0, set by FETCH or EXECUTE with an INTO clause, which returns the actual length of a VARCHAR or VARBINARY output value.
RETURNED_OCTET_LENGTH	An exact numeric value with scale 0, set by FETCH or EXECUTE with an INTO clause, which returns the actual number of octets of a VARCHAR or VARBINARY output value.
SCALE	An exact numeric value with scale 0, specifying the scale for a numeric data type value.
TYPE	An exact numeric value with scale 0, containing a coded representation of the data type. See below for a description of the codes.
UNNAMED	An exact numeric value with scale 0, indicating whether NAME contains an actual column or parameter name, or not. UNNAMED=0 indicates that NAME contains an actual name. UNNAMED=1 means that NAME does not contain an actual name.

TYPE Fields in the Item Descriptor Area

The TYPE field in the item descriptor area can contain one of the following values:

Code	SQL data type
1	CHARACTER
2	DECIMAL
3	DECIMAL
4	INTEGER
5	SMALLINT
6	FLOAT
7	REAL
8	DOUBLE PRECISION
9	DATETIME
10	INTERVAL
12	VARCHAR
16	BOOLEAN
25	BIGINT
30	BLOB
40	CLOB
60	BINARY ^a
61	BINARY VARYING
-8	NCHAR
-9	NCHAR VARYING
-11	INTEGER(p)^b
-40	NCLOB

^aNull padding is applied to the fixed size BINARY data type.

^bINTEGER(p) is a Mimer SQL specific data type used for integer data with a specified precision.

DATETIME Data Types in the Item Descriptor Area

For DATETIME data types, the DATETIME_INTERVAL_CODE field in the item descriptor area can contain one of the following values:

Code	DATETIME subtype
1	DATE
2	TIME
3	TIMESTAMP

INTERVAL Data Types in the Item Descriptor Area

For INTERVAL data types, the DATETIME_INTERVAL_CODE field in the item descriptor area can contain one of the following values:

Code	INTERVAL subtype
1	YEAR
2	MONTH
3	DAY
4	HOUR
5	MINUTE
6	SECOND
7	YEAR TO MONTH
8	DAY TO HOUR
9	DAY TO MINUTE
10	DAY TO SECOND
11	HOUR TO MINUTE
12	HOUR TO SECOND
13	MINUTE TO SECOND

Parameters in the Item Descriptor Area

For routine parameters, the PARAMETER_MODE field in the item descriptor area can contain one of the following values:

Code	PARAMETER_MODE
1	PARAMETER_MODE_IN
2	PARAMETER_MODE_INOUT
4	PARAMETER_MODE_OUT

Notes

The value of the DATA field is undefined if the INDICATOR field indicates the null value.

The data type of the host variables must be compatible with the data type of the associated field name.

Examples

exec sql GET DESCRIPTOR 'SQLA' :cnt = COUNT;

exec sql GET DESCRIPTOR 'SQLA' VALUE 1 :hostvar1 = DATA;

For more information, see the Mimer SQL Programmer's Manual, SQL Descriptor Area.

Standard Compliance

Standard	Compliance	Comments
SQL-2016	Features outside core	Feature B031, “Basic dynamic SQL”. Feature B032, “Extended dynamic SQL” support of dynamic descriptor names.

Standard

Compliance

Comments

SQL-2016

Features outside core

Feature B031, “Basic dynamic SQL”.

Feature B032, “Extended dynamic SQL” support of dynamic descriptor names.