GET DESCRIPTOR

http://developer.mimer.com

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.

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.

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

VARCHAR

30

BLOB

40

CLOB

-2

BINARY¹

-3

BINARY VARYING

-8

NCHAR

-9

NCHAR VARYING

-11

INTEGER(p)²

¹
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.

Example
 EXEC SQL GET DESCRIPTOR 'SQLA' VALUE 1 :hostvar1 = DATA;
 
For more information, see the Mimer SQL Programmer's Manual, chapter 11, SQL Descriptor Area.

Standard Compliance

Standard

Compliance

Comments

X/Open-95
SQL-92

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) 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	VARCHAR
30	BLOB
40	CLOB
-2	BINARY¹
-3	BINARY VARYING
-8	NCHAR
-9	NCHAR VARYING
-11	INTEGER(p)²

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 SQL-92	YES	Fully compliant.

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