GET DESCRIPTOR
NAME
GET DESCRIPTOR: read fields of an SQL descriptor area (in a program)
SYNOPSIS
<get descriptor statement> ::=
GET DESCRIPTOR <descriptor name>
{ <parameter name> = { COUNT | TOP_LEVEL_COUNT | KEY_TYPE }
| VALUE <item number> <get item information> [ { , <get item information> }... ] }
<get item information> ::= <parameter name> = <descriptor item name>
<item number> ::= <simple value specification>
<descriptor item name> ::=
TYPE | LENGTH | OCTET_LENGTH | RETURNED_LENGTH | RETURNED_OCTET_LENGTH
| PRECISION | SCALE | DATETIME_INTERVAL_CODE | DATETIME_INTERVAL_PRECISION
| NULLABLE | INDICATOR | DATA | NAME | UNNAMED
| LEVEL | DEGREE | CARDINALITY | RETURNED_CARDINALITY | KEY_MEMBER
| COLLATION_CATALOG | COLLATION_SCHEMA | COLLATION_NAME
| CHARACTER_SET_CATALOG | CHARACTER_SET_SCHEMA | CHARACTER_SET_NAME
| SORT_DIRECTION | NULL_ORDERING
<descriptor name> ::= <simple value specification> | PTF <simple value specification>
The <descriptor name> is ALLOCATE DESCRIPTOR's,
or PTF and the name of an area of a polymorphic table function's
component procedure, which reads it into its SQL variables.
DESCRIPTION
Of PTF, the area is one a polymorphic table function's component procedure is passed the name of (CREATE FUNCTION): a table argument's row type, the columns a component reads of it, the result's row type, or a table argument's partitioning or ordering. Of an ordering, SORT_DIRECTION is 1 of ASC and -1 of DESC, NULL_ORDERING 1 of NULLS FIRST and -1 of NULLS LAST.
GET DESCRIPTOR copies COUNT, TOP_LEVEL_COUNT, KEY_TYPE, or fields of the item descriptor area of the number given, from 1, into parameters. An item beyond COUNT is no data, SQLSTATE 02000, its targets unchanged. A field never set reads as 0, or the empty string, and DATA as the null value.
The fields of an item, of a dynamic parameter or a column, as DESCRIBE sets them:
| Field | Type | Value |
|---|---|---|
| TYPE | exact | The data type's code, below |
| LENGTH | exact | A string's length, in characters or octets; a datetime's length in positions |
| OCTET_LENGTH | exact | A string's length in octets at most: 4 to a character |
| RETURNED_LENGTH, RETURNED_OCTET_LENGTH | exact | Of a varying string fetched, its length, and in octets |
| PRECISION | exact | A number's precision; a datetime's or interval's fractional seconds precision |
| SCALE | exact | An exact number's scale |
| DATETIME_INTERVAL_CODE | exact | A datetime's or interval's code, below |
| DATETIME_INTERVAL_PRECISION | exact | An interval's leading field precision |
| NULLABLE | exact | 1 if it may be null, else 0; 1 of a dynamic parameter |
| INDICATOR | exact | -1 of a null value fetched, else 0 |
| DATA | the item's type | The value |
| NAME | character | A column's name; empty of an unnamed column and of a dynamic parameter |
| UNNAMED | exact | 1 of an unnamed column and of a dynamic parameter, else 0 |
| LEVEL | exact | 0 of a column or parameter; of an item of a part of one, one more than the item it is a part of |
| DEGREE | exact | A row's number of fields |
| CARDINALITY | exact | An array's maximum cardinality |
| RETURNED_CARDINALITY | exact | Of an array fetched, its cardinality |
| KEY_MEMBER | exact | 1 of a column of the key KEY_TYPE says, else 0 (DESCRIBE) |
| CHARACTER_SET_CATALOG, _SCHEMA, _NAME | character | A string's character set: the catalog, INFORMATION_SCHEMA, SQL_TEXT |
| COLLATION_CATALOG, _SCHEMA, _NAME | character | A string's collation, named as its character set |
The codes of TYPE:
| Code | Type | Code | Type |
|---|---|---|---|
| 1 | CHARACTER | 9 | DATE, TIME or TIMESTAMP |
| 2 | NUMERIC | 10 | INTERVAL |
| 3 | DECIMAL | 12 | CHARACTER VARYING |
| 4 | INTEGER | 16 | BOOLEAN |
| 5 | SMALLINT | 60 | BINARY |
| 6 | FLOAT | 61 | BINARY VARYING |
| 7 | REAL | -1 | a type of a plug-in |
| 8 | DOUBLE PRECISION | 26 | DECFLOAT |
| 19 | ROW | 50 | ARRAY |
| 25 | BIGINT | 55 | MULTISET |
The codes of DATETIME_INTERVAL_CODE, of a datetime: 1 DATE, 2 TIME, 3 TIMESTAMP, 4 TIME WITH TIME ZONE, 5 TIMESTAMP WITH TIME ZONE; of an interval: 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.
GET DESCRIPTOR runs only in a program that embeds turborel, through
Session::execute: its targets are parameters, which direct SQL has
none of.
RULES
- An area of the name is allocated.
- The item number is exact numeric with scale 0, from 1 to the area's occurrences.
- COUNT's and TOP_LEVEL_COUNT's target and a numeric field's are exact numeric with scale 0; a name's field's a character string of 128 characters or more; DATA's of the item's type, a varying string's of its length or longer.
- DATA of a negative INDICATOR is got with INDICATOR.
- DATA is not got of an item of TYPE ROW, nor of an item that is part of an array's element.
PRIVILEGES
None.
ERRORS
| SQLSTATE | SQLCODE | When |
|---|---|---|
| 33000 | -921 | No area of the name is allocated (rule 1) |
| 2A000 | -103 | The item number or a target not of its type (rules 2, 3) |
| 07009 | -921 | The item number is not from 1 to the occurrences (rule 2) |
| 22005 | -301 | DATA's target is not of the item's type (rule 3) |
| 0700C | -921 | DATA of a row or of an array's element (rule 5) |
| 22002 | -301 | DATA of a negative INDICATOR, without INDICATOR (rule 4); with it, DATA's target is left as it is |
| 02000 | 100 | Not an error: no data, the item is beyond COUNT |
EXAMPLES
In a program, of the parameters PREPARE declares, and N,
exact, and WHAT, CHARACTER(128); MY_NAME holding Ada:
s.execute("ALLOCATE DESCRIPTOR 'ROW' WITH MAX 3", &mut p)?;
s.execute("SET DESCRIPTOR 'ROW' COUNT = 1", &mut p)?;
s.execute("SET DESCRIPTOR 'ROW' VALUE 1 TYPE = 12, LENGTH = 30, DATA = MY_NAME", &mut p)?;
s.execute("GET DESCRIPTOR 'ROW' N = COUNT", &mut p)?; // N = 1
s.execute("GET DESCRIPTOR 'ROW' VALUE 1 N = TYPE, WHAT = CHARACTER_SET_NAME", &mut p)?; // 12, SQL_TEXT
s.execute("GET DESCRIPTOR 'ROW' VALUE 1 TEXT = DATA", &mut p)?; // TEXT = Ada
s.execute("GET DESCRIPTOR 'ROW' VALUE 2 N = TYPE", &mut p)?; // no data
s.execute("SET DESCRIPTOR 'ROW' VALUE 1 INDICATOR = -1", &mut p)?;
Refused, then:
GET DESCRIPTOR 'ROW' VALUE 1 TEXT = DATA -301 (22002): INDICATOR is negative, and DATA is got without it [ISO/IEC 9075:1992 §17.4 GR5]
GET DESCRIPTOR 'ROW' VALUE 1 MY_ID = DATA -301 (22005): NUMERIC(9,0) does not match the type the item describes [ISO/IEC 9075:1992 §17.4 GR2, §17.1 SR2]
GET DESCRIPTOR 'ROW' VALUE 4 N = TYPE -921 (07009): item 4 is not of 1 to 3, the area's occurrences [ISO/IEC 9075:1992 §17.4 GR3]
GET DESCRIPTOR 'ROW' VALUE 1 WHAT = TYPE -103 (2A000): the target of TYPE is not of its type [ISO/IEC 9075:1992 §17.4 SR3, Table 17]
GET DESCRIPTOR 'NO_ROW' N = COUNT -921 (33000): no SQL descriptor area 'NO_ROW' is allocated [ISO/IEC 9075:1992 §17.4 GR1]
STANDARD
ISO/IEC 9075:1992 §17.4, of the descriptor areas of §17.1, Tables 17 to
20. SQL-92 makes it no <directly executable statement> [§20.1]. SQL-86
has no dynamic SQL. The codes 60 and 61 of BINARY and BINARY VARYING, and
their lengths in octets, are ISO/IEC 9075-2:2023's [§20.1 Table 30,
§20.10 GR7d ii], and so is the code 26 of DECFLOAT, its PRECISION its
precision [§20.10 GR7d v]. So are TOP_LEVEL_COUNT, LEVEL, DEGREE,
CARDINALITY and RETURNED_CARDINALITY, and the codes 19 and 50 of ROW and
ARRAY [§20.1 SR3 to SR6, §20.4 GR5], and KEY_TYPE and KEY_MEMBER
[§20.10 GR6d, GR7c], Feature T301.
SEE ALSO
SET DESCRIPTOR, DESCRIBE, ALLOCATE DESCRIPTOR, GET DIAGNOSTICS