602SQL Documentation Index  

SQL Data Types

Data type specifies the set of possible values for a table column, variable, routine parameter or a function value. These types are available in 602SQL:

type ::= character_string [language_and_charset] | binary_object | bit_string | accurate_number_type 
    | approximate_number_type | BOOLEAN | DATE | TIME [WITH TIME ZONE] | TIMESTAMP [WITH TIME ZONE] 
    | domain_identifier
character_string ::= CHAR [( length )] | CHAR VARYING ( length ) | VARCHAR ( length ) | NCHAR [( length )] 
    | NCHAR VARYING ( length ) | CLOB [( length )] | NCLOB [( length )]
binary_object ::= BLOB [( length )]
bit_string ::= BINARY ( length ) | VARBINARY ( length ) | BIT [( length )] | BIT VARYING ( length )
accurate_number_type ::= INTEGER | SMALLINT | TINYINT | BIGINT | DECIMAL [( precision [, decim ] )] 
    | NUMERIC [( precision [, decim ] )]
approximate_number_type ::= REAL | DOUBLE PRECISION | FLOAT [( precision )]

Without changing a meaning you can use INT instead of INTEGER, DEC instead of DECIMAL, CHARACTER instead of CHAR, NATIONAL CHAR or NATIONAL CHARACTER instead of NCHAR, CHAR LARGE OBJECT or CHARACTER LARGE OBJECT instead of CLOB and BINARY LARGE OBJECT instead of BLOB.

You can also use LONG VARCHAR instead of CLOB, LONG VARBINARY instead of BLOB and VARBINARY instead of BIT VARYING in order to maintain compatibility with ODBC.

Internal ("physical") data types used by a client program correspond with the SQL types, when the client sends or reads data. The following table describes the correspondence between the two type sets. The third column contains the constants used for physical types description.

SQL Type Description Internal Representation Designation Number
CHAR one character ATT_CHAR 2
CHAR(n), CHAR VARYING(n), VARCHAR(n), for n<=4090 string character up to length n ATT_STRING 7
NCHAR(n), NCHAR VARYING(n), for n<=2045 Unicode character string up to length n ATT_STRING 7
CLOB, CLOB(n) character string of unlimited length ATT_TEXT 20
NCLOB, NCLOB(n) Unicode character string of unlimited length ATT_TEXT 20
BLOB, BLOB(n) order of bytes ATT_NOSPEC 21
BINARY(n) for n<=4090 order of bytes up to length n ATT_BINARY 10
BIT(n), BIT VARYING(n) for 1<n<=8*4090 order of (n-1) / 8 + 1 bytes ATT_BINARY 10
INTEGER 4-byte integer number ATT_INT32 4
SMALLINT 2-byte integer number ATT_INT16 3
TINYINT 1-byte integer number ATT_INT8 45
BIGINT 8-byte integer number ATT_INT64 46
DECIMAL(n, d), NUMERIC(n, d) for n <= 2 1-byte number with a scale d ATT_INT8 45
DECIMAL(n, d), NUMERIC(n, d) for 2 < n <= 4 2-byte number with a scale d ATT_INT16 3
DECIMAL(n, d), NUMERIC(n, d) for 4 < n <= 9 4-byte number with a scale d ATT_INT32 4
DECIMAL(n, d), NUMERIC(n, d) for n > 9 8-byte number with a scale d ATT_INT64 46
DATE date as a 4-byte number ATT_DATE 11
TIME, TIME WITH TIME ZONE time as a 4-byte number ATT_TIME 12
TIMESTAMP, TIMESTAMP WITH TIME ZONE date and time as a 4-byte number ATT_TIMESTAMP 13

The NULL value belongs to each type. It's equivalent to the UNKNOWN value for BOOLEAN type, equivalent to the empty string for BLOB and string character types and equivalent to the string of zeros for binary strings. It's different from any other possible values for the other types.

Variances from Intermediate Level to the Entry Level

Variances from Intermediate to the Full Level

SQL Properties in 602SQL Defined by Implementation

Operating System Requirements

Full support for Unicode and 8-bit strings in a non-Windows coding is available only in Windows 2000 and XP. The charset support depends on the system configuration for Linux systems - ISO charsets are usually supported, Windows code pages are usually not supported.

List of topics: