6.2.1 List of data types
The following table lists the data types supported by HADB.
No. |
Class |
Data type |
Data type code#1 |
Length of data storage (units: bytes) |
Data format |
|
---|---|---|---|---|---|---|
Decimal |
Hex |
|||||
1 |
Numeric data |
INTEGER |
241 |
F1 |
8 |
Integer (8-byte) |
2 |
SMALLINT |
245 |
F5 |
4 |
Integer (4-byte) |
|
3 |
DECIMAL(m,n) |
229 |
E5 |
|
Fixed-point number |
|
4 |
DOUBLE PRECISION |
225 |
E1 |
8 |
Double-precision floating-point number |
|
5 |
Character string data |
CHARACTER(n) |
197 |
C5 |
n |
Fixed-length character string |
6 |
VARCHAR(n) |
193 |
C1 |
n + 2 |
Variable-length character string |
|
7 |
Datetime data |
DATE |
113 |
71 |
4 |
Data type for dates, with fields for the year, month, and day |
8 |
TIME(p) |
121 |
79 |
3 + ↑p ÷ 2↑ |
Data type for time, with fields for the hour, minute, and seconds |
|
9 |
TIMESTAMP(p) |
125 |
7D |
7 + ↑p ÷ 2↑ |
Data type for time stamps, with fields for the year, month, day, hour, minute, and seconds |
|
10 |
Binary data |
BINARY(n) |
149 |
95 |
n |
Fixed-length binary data |
11 |
VARBINARY(n) |
145 |
91 |
n + 2 |
Variable-length binary data |
|
12 |
Row data |
ROW |
69 |
45 |
Row length#2 |
Data type used for row interface |
- Legend:
-
Class: Classification
Hex: Hexadecimal
- #1
-
The code that represents the data type of the retrieval results column.
When using a CLI function, the data type code is stored in the structure a_rdb_SQLDataType_t.
- #2
-
The row length is the sum of the data storage size of each column.
- Organization of this subsection
(1) Numeric data
- ■ INTEGER
-
-
This data type handles integer values in the range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
-
The following shows the format to use when specifying this data type:
INT or INTEGER
-
The data is in 8-byte binary format.
-
Literals are written in the form 100, 200, and so on. For details about literals, see 6.3 Literals.
-
- ■ SMALLINT
-
-
This data type handles integer values in the range -2,147,483,648 to 2,147,483,647.
-
The following shows the format to use when specifying this data type:
SMALLINT
-
The data is in 4-byte binary format.
-
Using the SMALLINT can reduce the size of the database compared with using the INTEGER.
-
- ■ DECIMAL
-
-
This data type handles fixed-point numbers.
-
The following shows the format to use when specifying this data type:
{DEC | DECIMAL}[(m[,n])]
-
The precision (overall number of digits) is specified in m, and the scaling (number of digits in the fractional part) is specified in n.
-
m and n are positive integers such that 1 ≤ m ≤ 38, 0 ≤ n ≤ 38, n ≤ m.
-
If m is omitted, 38 is assumed, and if n is omitted, 0 is assumed.
-
The data is stored internally in binary format. The binary value that is stored depends on the scaling.
-
Negative values are represented in two's complement format.
-
The data is stored as integer data of 2 to 16 bytes, depending on the precision, as illustrated in the following figure:
Figure 6‒5: Data format of DECIMAL -
Literals are written in the form 123.4, 12.345, and so on. For details about literals, see 6.3 Literals.
-
- ■ DOUBLE PRECISION
-
-
This data type handles double-precision floating-point numbers. The ranges of values covered include approximately -1.7 × 10308 to -2.3 × 10-308, 0, and approximately 2.3 × 10-308 to 1.7 × 10308.
The exact range of values depends on the hardware representation.
-
The following shows the format to use when specifying this data type:
DOUBLE or DOUBLE PRECISION
-
The data is an 8-byte floating-point number.
-
In the case of literals such as 1.0e2 or -3.4E-1, the mantissa is represented by an integer or decimal literal, and the exponent is stored as an integer of no more than 3 digits. For details about literals, see 6.3 Literals.
-
This data type cannot handle NaN (not a number) and infinite values.
-
-0 is converted to +0.
-
Subnormal numbers are converted to +0.
-
When floating-point data is rounded, it is rounded to the nearest even number.
-
(2) Character string data
- ■ CHARACTER
-
-
This data type handles fixed-length character strings.
-
The following shows the format to use when specifying this data type:
CHAR, CHAR(n), CHARACTER, or CHARACTER(n)
-
The length of the character string (number of bytes) is specified in n, where n is an integer in the range 1 to 32,000. If n is omitted, 1 is assumed.
-
Literals are written in the form 'char'. For details about literals, see 6.3 Literals.
-
Both half-width and full-width characters can be handled.
-
When you perform comparisons on character string data, the ordering of the character encoding determines the ordering of the data being compared.
-
- ■ VARCHAR
-
-
This data type handles variable-length character strings.
-
The following shows the format to use when specifying this data type:
VARCHAR(n)
-
In the preceding format, n specifies (in bytes) the maximum length of each character string. The value of n must be an integer in the range from 1 to 64,000. n cannot be omitted.
-
The data format of the VARCHAR type is shown in the following figure.
Figure 6‒6: Data format of VARCHAR type The character string data length (L) is represented by four bytes.
-
Both half-width and full-width characters can be handled. The length of the character string can be 0 bytes.
-
When you perform comparisons on character string data, the ordering of the character encoding determines the ordering of the data being compared.
-
You cannot specify VARCHAR-type data whose length exceeds 32,000 bytes in the following locations:
-
Data type specified in the column definition in an ALTER TABLE statement
-
Data type specified in the column definition in a CREATE TABLE statement
-
Data type specified in a table function column list
-
Post-conversion data type specified in the scalar function CAST
-
Post-conversion data type specified in the scalar function CONVERT
-
-
(3) Datetime data
- ■ DATE
-
-
This is the data type for dates, with fields for the year, month, and day.
-
The following shows the format to use when specifying this data type:
DATE
-
It can handle dates with a range of values from January 1, 0001 to December 31, 9999.
-
The data length is 4 bytes. The data that is entered must be this length.
-
The data format of the DATE type is shown in the following figure.
Figure 6‒7: Data format of DATE type -
Literals are written in the form DATE'2012-03-30' or DATE'2012/03/30'. For details about literals, see 6.3 Literals.
-
- ■ TIME
-
-
This is the data type for time, with fields for the hour, minute, and seconds.
-
The following shows the format to use when specifying this data type:
TIME(p) or TIME
p specifies the fractional seconds precision (the number of digits to the right of the decimal point). You can specify a value of 0, 3, 6, 9, or 12 for p.
If TIME is specified, p is assumed to be 0.
-
This format can handle times with a range of values from 0 hours, 0 minutes, and 0.000000000000 seconds to 23 hours, 59 minutes, and 59.999999999999 seconds.
-
The data length is 3 + ↑p ÷ 2↑ bytes. The data that is entered must be this length.
-
The data format of the TIME type is shown in the following figure.
Figure 6‒8: Data format of TIME type One digit is represented in 4 bits. If the fractional seconds precision is an odd number, zeros are stored in the final 4 bits.
-
Literals are written in the form TIME'11:03:58.123456'. For details about literals, see 6.3 Literals.
-
- ■ TIMESTAMP
-
-
This is the data type for time stamps, with fields for the year, month, day, hour, minute, and seconds.
-
The following shows the format to use when specifying this data type:
TIMESTAMP(p) or TIMESTAMP
p specifies the fractional seconds precision (the number of digits to the right of the decimal point). You can specify a value of 0, 3, 6, 9, or 12 for p.
If TIMESTAMP is specified, p is assumed to be 0.
-
It can handle time stamps with a range of values from January 1, 0001 0:0:0.000000000000 to December 31, 9999 23:59:59.999999999999.
-
The data length is 7 + ↑p ÷ 2↑ bytes. The data that is entered must be this length.
-
The data format of the TIMESTAMP type is shown in the following figure.
Figure 6‒9: Data format of TIMESTAMP type One digit is represented in 4 bits. If the fractional seconds precision is an odd number, zeros are stored in the final 4 bits.
-
Literals are written in the form TIMESTAMP'2012-03-30 11:03:58.123456' or TIMESTAMP'2012/03/30 11:03:58.123456'. For details about literals, see 6.3 Literals.
-
(4) Binary data
- ■ BINARY
-
-
This is the data type for handling fixed-length binary data.
-
The following shows the format to use when specifying this data type:
BINARY(n) or BINARY
-
The length of the binary data (number of bytes) is specified in n, where n is an integer in the range 1 to 32,000. If n is omitted, 1 is assumed.
-
Literals are written in the form X'0A38ef92'. For details about literals, see 6.3 Literals.
-
The data format of the BINARY type is shown in the following figure.
Figure 6‒10: Data format of BINARY type
-
- ■ VARBINARY
-
-
This is the data type for handling variable-length binary data.
-
The following shows the format to use when specifying this data type:
VARBINARY(n)
-
The maximum length of the binary data (number of bytes) is specified in n, which must be an integer in the range 1 to 32,000, and cannot be omitted.
-
Literals are written in the form X'0A38ef92'. For details about literals, see 6.3 Literals.
-
The length of the binary data can be 0 bytes.
-
The data format of the VARBINARY type is shown in the following figure.
Figure 6‒11: Data format of VARBINARY type The length of the binary data (L) is represented in two bytes.
-