SQLDA (SQL descriptor area)
An SQLDA is a collection of variables that is required for execution of the SQL DESCRIBE statement.
The SQLDA variables are options that can be used by the PREPARE, OPEN, FETCH, and EXECUTE statements. An SQLDA communicates with dynamic SQL; it can be used in a DESCRIBE statement, modified with the addresses of host variables, and then reused in a FETCH or EXECUTE statement.
SQLDAs are supported for all languages, but predefined declarations are provided only for C, REXX, FORTRAN, and COBOL.
The meaning of the information in an SQLDA depends on its use. In PREPARE and DESCRIBE, an SQLDA provides information to an application program about a prepared statement. In OPEN, EXECUTE, and FETCH, an SQLDA describes host variables.
- When describing a table with 3 VARCHAR columns and 1 INTEGER column, there will be 4 SQLVAR entries
- When describing a table with 2 VARCHAR columns, 1 CLOB column, and 1 integer column, there will be 8 SQLVAR entries
In EXECUTE, FETCH, and OPEN, if any one of the variables being described is a LOB type (LOB locators and file reference variables do not require doubled SQLDAs) or a structured type, the number of SQLVAR entries for the entire SQLDA must be doubled. (Distinct types and reference types are not relevant in these cases, because the additional information in the double entries is not required by the database. Array, cursor and row types are not supported as SQLDA variables in EXECUTE, FETCH and OPEN statements.)
SQLDA field descriptions
- Base SQLVARs: These entries are always present. They contain the base information about the column, parameter marker, or host variable such as data type code, length attribute, column name, host variable address, and indicator variable address.
- Secondary SQLVARs: These entries are only present if the number of SQLVAR entries is doubled as per the rules outlined previously. For user-defined types (excluding reference types), they contain the user-defined type name. For reference types, they contain the target type of the reference. For LOBs, they contain the length attribute of the host variable and a pointer to the buffer that contains the actual length. (The distinct type and LOB information does not overlap, so distinct types can be based on LOBs without forcing the number of SQLVAR entries on a DESCRIBE to be tripled.) If locators or file reference variables are used to represent LOBs, these entries are not necessary.
The circumstances under which the SQLVAR entries are set by DESCRIBE is detailed in Effect of DESCRIBE on the SQLDA.
Fields in the SQLDA header
C Name | SQL Data Type | Usage in DESCRIBE and PREPARE (set by the database manager except for SQLN) | Usage in FETCH, OPEN, and EXECUTE (set by the application before executing the statement) |
---|---|---|---|
sqldaid | CHAR(8) | The seventh byte of this field is a flag byte named SQLDOUBLED. The database manager sets
SQLDOUBLED to the character 2if two SQLVAR entries have been created for each column; otherwise it is set to a blank (X'20' in ASCII, X'40' in EBCDIC). See Effect of DESCRIBE on the SQLDA for details on when SQLDOUBLED is set. |
The seventh byte of this field is used when the number of SQLVARs is doubled. It is named
SQLDOUBLED. If any of the host variables being described is a structured type, BLOB, CLOB, or
DBCLOB, the seventh byte must be set to the character 2; otherwise it can be set to any character but the use of a blank is recommended. |
sqldabc | INTEGER | For 32 bit, the length of the SQLDA, equal to SQLN*44+16. For 64 bit, the length of the SQLDA, equal to SQLN*56+16 | For 32 bit, the length of the SQLDA, >= to SQLN*44+16. For 64 bit, the length of the SQLDA, >= to SQLN*56+16. |
sqln | SMALLINT | Unchanged by the database manager. Must be set to a value greater than or equal to zero before the DESCRIBE statement is executed. Indicates the total number of occurrences of SQLVAR. | Total number of occurrences of SQLVAR provided in the SQLDA. SQLN must be set to a value greater than or equal to zero. |
sqld | SMALLINT | Set by the database manager to the number of columns in the result table or to the number of parameter markers. | The number of host variables described by occurrences of SQLVAR. |
Fields in an occurrence of a base SQLVAR
Name | Data Type | Usage in DESCRIBE and PREPARE | Usage in FETCH, OPEN, and EXECUTE |
---|---|---|---|
sqltype | SMALLINT | Indicates the data type of the column or parameter marker,
and whether it can contain nulls. (Parameter markers are always considered
nullable.) Table 4 lists the allowable
values and their meanings. Note that for a distinct, array, cursor, row, or reference type, the data type of the base type is placed into this field. For a structured type, the data type of the result of the FROM SQL transform function of the transform group (based on the CURRENT DEFAULT TRANSFORM GROUP special register) for the type is placed into this field. There is no indication in the base SQLVAR that it is part of the description of a user-defined type or reference type. |
Same for host variable. Host variables for datetime values must be character string variables. For FETCH, a datetime type code means a fixed-length character string. If sqltype is an even number value, the sqlind field is ignored. |
sqllen | SMALLINT | The length attribute of the column or parameter marker. For
datetime columns and parameter markers, the length of the string representation
of the values. See Table 4. Note that the value is set to 0 for large object strings (even for those whose length attribute is small enough to fit into a two byte integer). |
The length attribute of the host variable. See Table 4. Note that the value is ignored by the database manager for CLOB, DBCLOB, and BLOB columns. The len.sqllonglen field in the Secondary SQLVAR is used instead. |
sqldata | pointer | For string SQLVARS, sqldata contains the code page. For character-string
SQLVARs where the column is defined with the FOR BIT DATA attribute,
sqldata contains 0. For other character-string SQLVARS, sqldata contains
either the SBCS code page for SBCS data, or the SBCS code page associated
with the composite MBCS code page for MBCS data. For Japanese EUC,
Traditional Chinese EUC, and Unicode UTF-8 character-string SQLVARS,
sqldata contains 954, 964, and 1208 respectively. For all other column types, sqldata is undefined. |
Contains the address of the host variable (where the fetched data will be stored). |
sqlind | pointer | For character-string SQLVARS, sqlind contains 0, except for
MBCS data, when sqlind contains the DBCS code page associated with
the composite MBCS code page. For all other types, sqlind is undefined. |
Contains the address of an associated indicator variable, if there is one; otherwise, not used. If sqltype is an even number value, the sqlind field is ignored. |
sqlname | VARCHAR (30) | Contains the unqualified name of the column or parameter marker.
For columns and parameter markers that have a system-generated name, the thirtieth byte is set to X'FF'. For column names specified by the AS clause, this byte is X'00'. |
When connecting to a host database, sqlname can be set to indicate
a FOR BIT DATA string as follows:
When working with XML data, sqlname can be set to indicate
an XML subtype as follows:
|
Fields in an occurrence of a secondary SQLVAR
Name | Data Type | Usage in DESCRIBE and PREPARE | Usage in FETCH, OPEN, and EXECUTE |
---|---|---|---|
len.sqllonglen | INTEGER | The length attribute of a BLOB, CLOB, or DBCLOB column or parameter marker. | The length attribute of a BLOB, CLOB, or DBCLOB host variable. The database manager ignores the SQLLEN field in the Base SQLVAR for the data types. The length attribute stores the number of bytes for a BLOB or CLOB, and the number of double-byte characters for a DBCLOB. |
reserve2 | CHAR(3) for 32 bit, and CHAR(11) for 64 bit. | Not used. | Not used. |
sqlflag4 | CHAR(1) | The value is X'01' if the SQLVAR represents a reference type with a target type named in sqldatatype_name. The value is X'12' if the SQLVAR represents a structured type, with the user-defined type name in sqldatatype_name. Otherwise, the value is X'00'. | Set to X'01' if the SQLVAR represents a reference type with a target type named in sqldatatype_name. Set to X'12' if the SQLVAR represents a structured type, with the user-defined type name in sqldatatype_name. Otherwise, the value is X'00'. |
sqldatalen | pointer | Not used. | Used for BLOB, CLOB, and DBCLOB host variables only. If this field is the null value, then the actual length (in double-byte characters) should be stored in the 4 bytes immediately before the start of the data and SQLDATA should point to the first byte of the field length. If this field is not the null value, it contains a pointer to a 4 byte long buffer that contains the actual length in bytes (even for DBCLOB) of the data in the buffer pointed to from the SQLDATA field in the matching base SQLVAR. Note that, whether or not this field is used, the len.sqllonglen field must be set. |
sqldatatype_name | VARCHAR(27) | For a user-defined type, the database manager sets this to the fully qualified user-defined type name.1 For a reference type, the database manager sets this to the fully qualified type name of the target type of the reference. | For structured types, set to the fully qualified user-defined type name in the format indicated in the table note.1 |
reserved | CHAR(3) | Not used. | Not used. |
1 The first 8 bytes
contain the schema name of the type (extended to the right with spaces,
if necessary). Byte 9 contains a dot (.). Bytes 10 to 27 contain the
low order portion of the type name, which is not extended to
the right with spaces. Note that, although the prime purpose of
this field is for the name of user-defined types, the field is also
set for IBM® predefined data
types. In this case, the schema name is SYSIBM, and the low order
portion of the name is the name stored in the TYPENAME column of the
DATATYPES catalog view. For example:
|
Effect of DESCRIBE on the SQLDA
For a DESCRIBE OUTPUT or PREPARE OUTPUT INTO statement, the database manager always sets SQLD to the number of columns in the result set, or the number of output parameter markers. For a DESCRIBE INPUT or PREPARE INPUT INTO statement, the database manager always sets SQLD to the number of input parameter markers in the statement. Note that a parameter marker that corresponds to an INOUT parameter in a CALL statement is described in both the input and output descriptors.
- SQLN >= SQLD and no entry is either a LOB, user-defined type
or reference type
The first SQLD SQLVAR entries are set and SQLDOUBLED is set to blank.
- SQLN >= 2*SQLD and at least one entry is a LOB, user-defined type or reference type
Two times SQLD SQLVAR entries are set, and SQLDOUBLED is set to
2
. - SQLD <= SQLN < 2*SQLD and at least one entry is a distinct, array, cursor, row, or reference type, but
there are no LOB entries or structured type entries
The first SQLD SQLVAR entries are set and SQLDOUBLED is set to blank. If the SQLWARN bind option is YES, a warning SQLCODE +237 (SQLSTATE 01594) is issued.
- SQLN < SQLD and no entry is either a LOB, user-defined type
or reference type
No SQLVAR entries are set and SQLDOUBLED is set to blank. If the SQLWARN bind option is YES, a warning SQLCODE +236 (SQLSTATE 01005) is issued.
Allocate SQLD SQLVARs for a successful DESCRIBE.
- SQLN < SQLD and at least one entry is a distinct, array, cursor, row, or reference type, but
there are no LOB entries or structured type entries
No SQLVAR entries are set and SQLDOUBLED is set to blank. If the SQLWARN bind option is YES, a warning SQLCODE +239 (SQLSTATE 01005) is issued.
Allocate 2*SQLD SQLVARs for a successful DESCRIBE including the names of the distinct, array, cursor, and row types and target types of reference types.
- SQLN < 2*SQLD and at least one entry is a LOB or a structured
type
No SQLVAR entries are set and SQLDOUBLED is set to blank. A warning SQLCODE +238 (SQLSTATE 01005) is issued (regardless of the setting of the SQLWARN bind option).
Allocate 2*SQLD SQLVARs for a successful DESCRIBE.
References in the previous lists to LOB entries include distinct type entries whose source type is a LOB type.
The SQLWARN option of the BIND or PREP command is used to control whether the DESCRIBE (or PREPARE INTO) will return the warning SQLCODEs +236, +237, +239. It is recommended that your application code always consider that these SQLCODEs could be returned. The warning SQLCODE +238 is always returned when there are LOB or structured type entries in the select list and there are insufficient SQLVARs in the SQLDA. This is the only way the application can know that the number of SQLVARs must be doubled because of a LOB or structured type entry in the result set.
If a structured type entry is being described, but no FROM SQL transform is defined (either because no TRANSFORM GROUP was specified using the CURRENT DEFAULT TRANSFORM GROUP special register (SQLSTATE 42741) or because the name group does not have a FROM SQL transform function defined (SQLSTATE 42744)), the DESCRIBE will return an error. This error is the same error returned for a DESCRIBE of a table with a structured type entry.
If the database manager
returns identifiers that are longer than those that can be stored
in the SQLDA, the identifier is truncated and a warning is returned
(SQLSTATE 01665); however, when the name of a structured type is truncated,
an error is returned (SQLSTATE 42622). For details on identifier length
limitations, see SQL and XQuery limits
.
SQLTYPE and SQLLEN
SQLTYPE | Column data type for DESCRIBE and PREPARE INTO | SQLLEN for DESCRIBE and PREPARE INTO | Host variable data type for FETCH, OPEN, and EXECUTE | SQLLEN for FETCH, OPEN, and EXECUTE |
---|---|---|---|---|
384/385 | date | 10 | fixed-length character string representation of a date | length attribute of the host variable |
388/389 | time | 8 | fixed-length character string representation of a time | length attribute of the host variable |
392/393 | timestamp | 19 for TIMESTAMP(0) otherwise 20+p for TIMESTAMP(p) | fixed-length character string representation of a timestamp | length attribute of the host variable |
400/401 | N/A | N/A | NULL-terminated graphic string | length attribute of the host variable |
404/405 | BLOB | 0 * | BLOB | Not used. * |
408/409 | CLOB | 0 * | CLOB | Not used. * |
412/413 | DBCLOB | 0 * | DBCLOB | Not used. * |
448/449 | varying-length character string | length attribute of the column | varying-length character string | length attribute of the host variable |
452/453 | fixed-length character string | length attribute of the column | fixed-length character string | length attribute of the host variable |
456/457 | long varying-length character string | length attribute of the column | long varying-length character string | length attribute of the host variable |
460/461 | not applicable | not applicable | NULL-terminated character string | length attribute of the host variable |
464/465 | varying-length graphic string | length attribute of the column | varying-length graphic string | length attribute of the host variable |
468/469 | fixed-length graphic string | length attribute of the column | fixed-length graphic string | length attribute of the host variable |
472/473 | long varying-length graphic string | length attribute of the column | long graphic string | length attribute of the host variable |
480/481 | floating-point | 8 for double precision, 4 for single precision | floating-point | 8 for double precision, 4 for single precision |
484/485 | packed decimal | precision in byte 1; scale in byte 2 | packed decimal | precision in byte 1; scale in byte 2 |
492/493 | big integer | 8 | big integer | 8 |
496/497 | large integer | 4 | large integer | 4 |
500/501 | small integer | 2 | small integer | 2 |
908/909 | varying-length binary string | length attribute of the column | varying-length binary string | length attribute of the host variable |
912/913 | fixed-length binary string | length attribute of the column | fixed-length binary string | length attribute of the host variable |
916/917 | not applicable | not applicable | BLOB file reference variable | 267 |
920/921 | not applicable | not applicable | CLOB file reference variable | 267 |
924/925 | not applicable | not applicable | DBCLOB file reference variable. | 267 |
960/961 | not applicable | not applicable | BLOB locator | 4 |
964/965 | not applicable | not applicable | CLOB locator | 4 |
968/969 | not applicable | not applicable | DBCLOB locator | 4 |
988/989 | XML | 0 | not applicable; use an XML AS <string or binary LOB type> host variable instead | not used |
996 | decimal floating-point | 8 for DECFLOAT(16), 16 for DECFLOAT(34) | decimal floating-point | 8 for DECFLOAT(16), 16 for DECFLOAT(34) |
2440/2441 | row | not applicable | row | not used |
2440/2441 | cursor | not applicable | row | not used |
Note:
|
Unrecognized and unsupported SQLTYPEs
The values that appear in the SQLTYPE field of the SQLDA are dependent on the level of data type support available at the sender as well as at the receiver of the data. This is particularly important as new data types are added to the product.
New data types may or may not be supported by the sender or receiver of the data and may or may not even be recognized by the sender or receiver of the data. Depending on the situation, the new data type may be returned, or a compatible data type agreed upon by both the sender and receiver of the data may be returned or an error may result.
When the sender and receiver agree to use a compatible data type, the following table indicates the mapping that will take place. This mapping will take place when at least one of the sender or the receiver does not support the data type provided. The unsupported data type can be provided by either the application or the database manager.
Data Type | Compatible Data Type |
---|---|
BIGINT | DECIMAL(19, 0) |
ROWID | VARCHAR(40) FOR BIT DATA |
Note that no indication is given in the SQLDA that the data type is substituted.
Packed decimal numbers
Packed decimal numbers are stored in a variation of Binary Coded Decimal (BCD) notation. In BCD, each nybble (four bits) represents one decimal digit. For example, 0001 0111 1001 represents 179. Therefore, read a packed decimal value nybble by nybble. Store the value in bytes and then read those bytes in hexadecimal representation to return to decimal. For example, 0001 0111 1001 becomes 00000001 01111001 in binary representation. By reading this number as hexadecimal, it becomes 0179.
The decimal point is determined by the scale. In the case of a DEC(12,5) column, for example, the rightmost 5 digits are to the right of the decimal point.
Sign is indicated by a nybble to the right of the nybbles representing the digits. A positive or negative sign is indicated as follows:
Sign | Binary representation | Decimal representation | Hexadecimal representation |
---|---|---|---|
Positive (+) | 1100 | 12 | C |
Negative (-) | 1101 | 13 | D |
- To store any value, allocate p/2+1 bytes, where p is precision.
- Assign the nybbles from left to right to represent the value. If a number has an even precision, a leading zero nybble is added. This assignment includes leading (insignificant) and trailing (significant) zero digits.
- The sign nybble will be the second nybble of the last byte.
For example:
Column | Value | Nybbles in Hexadecimal Grouped by Bytes |
---|---|---|
DEC(8,3) | 6574.23 | 00 65 74 23 0C |
DEC(6,2) | -334.02 | 00 33 40 2D |
DEC(7,5) | 5.2323 | 05 23 23 0C |
DEC(5,2) | -23.5 | 02 35 0D |
SQLLEN field for decimal
The SQLLEN field contains the precision (first byte) and scale (second byte) of the decimal column. If writing a portable application, the precision and scale bytes should be set individually, versus setting them together as a short integer. This will avoid integer byte reversal problems.
((char *)&(sqlda->sqlvar[i].sqllen))[0] = precision;
((char *)&(sqlda->sqlvar[i].sqllen))[1] = scale;