IBM Books

SQL Reference

SQLTYPE and SQLLEN

Table 36 shows the values that may appear in the SQLTYPE and SQLLEN fields of the SQLDA. In DESCRIBE and PREPARE INTO, an even value of SQLTYPE means the column does not allow nulls, and an odd value means the column does allow nulls. In FETCH, OPEN, EXECUTE, and CALL, an even value of SQLTYPE means no indicator variable is provided, and an odd value means that SQLIND contains the address of an indicator variable.

Table 36. SQLTYPE and SQLLEN values for DESCRIBE, FETCH, OPEN, EXECUTE, and CALL

For DESCRIBE and PREPARE INTO For FETCH, OPEN, EXECUTE, and CALL
SQLTYPE Column Data Type SQLLEN Host Variable Data Type SQLLEN
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 26 fixed-length character string representation of a timestamp length attribute of the host variable
396/397 DATALINK length attribute of the column DATALINK length attribute of the host variable
400/401 N/A N/A NUL-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 N/A N/A NUL-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
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
Note:

  • The len.sqllonglen field in the secondary SQLVAR contains the length attribute of the column.

  • The SQLTYPE has changed from the previous version for portability in DB2. The values from the previous version (see previous version SQL Reference) will continue to be supported.

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 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 100

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:

Table 37. Values for Sign Indicator of a Packed Decimal Number
Sign Representation
Binary Decimal Hexadecimal
Positive (+) 1100 12 C
Negative (-) 1101 13 D

In summary:

  1. To store any value, allocate p/2+1 bytes, where p is precision.
  2. 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.
  3. The sign nybble will be the second nybble of the last byte.

There is an alternative way to perform packed decimal conversions, see CHAR.

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.

For example, in C: 

  ((char *)&(sqlda->sqlvar[i].sqllen))[0] = precision;
  ((char *)&(sqlda->sqlvar[i].sqllen))[1] = scale;
Footnotes:

100
ROWID is supported by DB2 Universal Database for OS/390 Version 6.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]