GET DESCRIPTOR

www.mimer.com/developer

GET DESCRIPTOR

Gets values from an SQL descriptor area.

where item-information is:

Usage

Embedded.

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. The VALUE form is used to retrieve SQL descriptor field values from the item descriptor area specified by item-number.

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 with an INTO clause) 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.

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 name or a situation dependent generated name (for example if the value is a result of an aggregate function).

UNNAMED=0 indicates that NAME contains an actual column name. UNNAMED=1 means that NAME does not contain an actual column 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

NUMERIC

3

DECIMAL

4

INTEGER

5

SMALLINT

6

FLOAT

7

REAL

8

DOUBLE PRECISION

9

DATETIME

10

INTERVAL

12

CHARACTER VARYING

-2

BINARY¹

-3

BINARY VARYING

-11

INTEGER(p)²

-13

CHARACTER VARYING

¹
Null padding is applied to the fixed size BINARY data type.

²
INTEGER(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

Restrictions

None.

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.

Standard Compliance

Standard

Compliance

Comments

X/Open-95
SQL92

YES

Fully compliant.

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 with an INTO clause) 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.
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 name or a situation dependent generated name (for example if the value is a result of an aggregate function). UNNAMED=0 indicates that NAME contains an actual column name. UNNAMED=1 means that NAME does not contain an actual column name.

Code	SQL data type
1	CHARACTER
2	NUMERIC
3	DECIMAL
4	INTEGER
5	SMALLINT
6	FLOAT
7	REAL
8	DOUBLE PRECISION
9	DATETIME
10	INTERVAL
12	CHARACTER VARYING
-2	BINARY¹
-3	BINARY VARYING
-11	INTEGER(p)²
-13	CHARACTER VARYING

Code	DATETIME subtype
1	DATE
2	TIME
3	TIMESTAMP

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

Code	PARAMETER_MODE
1	PARAMETER_MODE_IN
2	PARAMETER_MODE_INOUT
4	PARAMETER_MODE_OUT

Standard	Compliance	Comments
X/Open-95 SQL92	YES	Fully compliant.

Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se