|
|
All the data in a column must be of the same type. Specify
the data type for each column when you create a table or when you
add a column to an existing table. The ALLBASE/SQL data types and
the values you can specify for data of each type are shown in
Table 7-1 "ALLBASE/SQL Data Types".
Table 7-1 ALLBASE/SQL Data Types
Group |
Data Type |
Description |
Alpha-numeric | CHAR[ACTER][(n)] |
String of fixed length n, where n is an
integer from 1 to 3996 bytes. The default size is CHAR (1). The keyword
CHARACTER is a synonym for CHAR. |
| VARCHAR(n) |
String of variable length no greater than n, where
n must be an integer from 1 to 3996 bytes. |
Numeric | DEC[IMAL][(p[,s])]
NUMERIC[(p[,s])] |
Fixed-point packed decimal number with a precision (maximum number
of digits excluding sign and decimal point) no greater than
p, where p is 1 through 27, and a
scale (number of digits to the right of the decimal) of
s, where s is from 0 through
p. E (exponential) and L (Pascal longreal) notation are
not allowed in the specification of a decimal value. Operations on data
of type DECIMAL are often much more precise than operations on data of
type FLOAT.
The default for NUMERIC and DECIMAL types is DECIMAL (27,0). DEC and
NUMERIC are synonyms for DECIMAL. |
| FLOAT[(p)] or DOUBLE PRECISION |
Long (64-bit) floating point number. This is an approximate numeric value
consisting of an exponent and a mantissa. The precision,
p, is a positive integer that specifies the number of
significant binary digits in the mantissa. The value of p
can be from 25 to 53. The default is 53.
The range of negative numbers that can be represented is
-1.79769313486230E+308 to -2.22507385850721E-308. The range of positive
numbers that can be represented is 2.22507385850721E-308 to
1.79769313486230E+308. E (exponential) or L (Pascal longreal) notation
can be used to specify FLOAT values.
DOUBLE PRECISION is a synonym for FLOAT(53). |
| FLOAT(p) or REAL |
Short (32-bit) floating point number. This is an approximate numeric
value consisting of an exponent and a mantissa. The precision,
p, is a positive integer that specifies the number of
significant binary digits in the mantissa. The value of p
can be from 1 to 24. The default (using REAL) is 24. The range of
negative numbers that can be represented is -3.402823E+38 to
-1.175495E-38. The range of positive numbers that can be represented is
3.402823E+38 to 1.175495E-38.
REAL is a synonym for FLOAT (24). |
| INT[EGER] |
Integer in the range -2147483648 (-231) to 2147483647
(231-1).
INT is a synonym for INTEGER. |
| SMALLINT |
Integer in the range -32768 (-215) to 32767 (215-1). |
Date/Time | DATE |
String of form 'YYYY-MM-DD', where YYYY represents the calendar year, MM
is the month, and DD is the day of the month. DATE is in the range from
'0000-01-01' to '9999-12-31'. |
| TIME |
String of the form 'HH:MI:SS: where HH represents hours, MI is minutes,
and SS is seconds. TIME is in the range from '00:00:00' to
'23:59:59'. |
| DATETIME |
String of the form 'YYYY-MM-DD HH:MI:SS.FFF', where YYYY represents the
calendar year, MM is the month, DD is the day, HH the hour, MI the
minute, SS the second, and FFF thousandths of a second. The range is from
'000-01-01 00:00:00.000' to '9999-12-31 23:59:59.999'. |
| INTERVAL |
String of the form 'DDDDDDD HH:MI:SS.FFF', where DDDDDDD is a number of
days, HH a number of hours, MI a number of minutes, SS a number of
seconds, and FFF a number of thousandths of a second. The range is from
'0 00:00:00.000' to '3652436 23:59:59.999'. |
Binary | BINARY(n) |
Binary string of fixed length n, where n
is an integer from 1 to 3996 bytes. Each byte stores 2 hexadecimal
digits. |
| VARBINARY(n) |
Binary string of variable length no greater than n, where
n is an integer from 1 to 3996 bytes. Each byte stores 2
hexadecimal digits. |
| LONG BINARY(n) |
Binary string of fixed length n, where n
is an integer from 1 to (231-1) bytes. |
| LONG VARBINARY(n) |
Binary string of variable length no greater than n, where
n is an integer from 1 to (231-1)
bytes. |
Your choice of data types can affect the following:
How values are used in expressions. Some operations can be
performed only with data of a certain type. For example, arithmetic
operations are limited to numeric and date/time data types, such as
INTEGER, SMALLINT, FLOAT, DECIMAL, DATE, TIME, DATETIME, or INTERVAL.
Pattern matching with the LIKE predicate can be performed only with
string data, that is, data of types CHAR or VARCHAR.
The result of operations combining data of different types. When
comparisons and expressions combining data of different but
compatible types are evaluated, ALLBASE/SQL performs type conversion,
as described later in this chapter.
How values are transferred programmatically. When data is
transferred between ALLBASE/SQL and an application program in host
variables, ALLBASE/SQL uses the data type equivalencies described in
the ALLBASE/SQL application programming guides.
Table 7-2 "Data Type Storage Requirements"
contains the storage requirements of the various data types.
Table 7-2 Data Type Storage Requirements
Type |
Storage Required |
CHAR (n) | n bytes (where n must be an integer from 1 to 3996) |
VARCHAR (n) | n bytes (where n must be an integer from 1 to 3996) |
DECIMAL (p[,s]) | 4 bytes (where p <= 7) or 8 bytes (where 7
< p <= 15) or 12 bytes (where 15 < p <= 23) or 16 bytes (where
p > 23) |
FLOAT | 8 bytes |
REAL | 4 bytes |
INTEGER | 4 bytes. Integer values less than -2147483648 (-2**31)
or larger than 2147483647 (2**31 - 1) up to 15 digits long are stored as
decimals with a precision of 15 and a scale of 0, i.e., equivalent to
DECIMAL (15,0) |
SMALLINT | 2 bytes |
DATE | 16 bytes |
TIME | 16 bytes |
DATETIME | 16 bytes |
INTERVAL | 16 bytes |
BINARY (n) | n bytes (where n must be an integer from 1 to 3996) |
VARBINARY (n) | n bytes (where n must be an integer from 1 to 3996) |
LONG BINARY (n) | n bytes (where n must be an integer from 1 to
231 - 1) |
LONG VARBINARY (n) | n bytes (where n must be an integer from
1 to 231 - 1) |
|