Alpha

These pages are built from main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

GET DESCRIPTOR

Statements

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

  1. An area of the name is allocated.
  2. The item number is exact numeric with scale 0, from 1 to the area's occurrences.
  3. 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.
  4. DATA of a negative INDICATOR is got with INDICATOR.
  5. 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