Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

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:

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 (in a structured user-defined type).
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.
USER_DEFINED_TYPE_CATALOG
A character string representing the catalog name for the user-defined type, if the item descriptor area's type is user-defined.
USER_DEFINED_TYPE_SCHEMA
A character string representing the schema name for the user-defined type, if the item descriptor area's type is user-defined.
USER_DEFINED_TYPE_NAME
A character string representing the name for the user-defined type, if the item descriptor area's type is user-defined.
USER_DEFINED_TYPE_CODE
An exact numeric value with scale 0, indicating whether a user-defined type is distinct or structured, if the item descriptor area's type is user-defined.
USER_DEFINED_TYPE_CODE=0 means distinct.
USER_DEFINED_TYPE_CODE=1 means structured.

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
16
BOOLEAN
17
User-defined type1
25
BIGINT
30
BLOB
40
CLOB
60
BINARY2
61
BINARY VARYING
-8
NCHAR
-9
NCHAR VARYING
-11
INTEGER(p)3
-40
NCLOB

1The user-defined type is specified using the item descriptor areas USER_DEFINED_TYPE_CATALOG, USER_DEFINED_TYPE_SCHEMA, and USER_DEFINED_TYPE_NAME.

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

3INTEGER(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-2011
Features outside core
Feature B031, "Basic dynamic SQL".
Feature B032, "Extended dynamic SQL" support of dynamic descriptor names.

Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX