6.2.1 List of data types

The following table lists the data types supported by HADB.

Table 6‒5: List of data types supported by HADB
No.	Class	Data type	Data type code^#1		Length of data storage (units: bytes)	Data format
No.	Class	Data type	Decimal	Hex	Length of data storage (units: bytes)	Data format
1	Numeric data	`INTEGER`	`241`	`F1`	8	Integer (8-byte)
2		`SMALLINT`	`245`	`F5`	4	Integer (4-byte)
3		`DECIMAL(m,n)`	`229`	`E5`	If 1 ≤ `m` ≤ 4: 2 If 5 ≤ `m` ≤ 8: 4 If 9 ≤ `m` ≤ 16: 8 If 17 ≤ `m` ≤ 38: 16	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	Character string data	`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	Binary data	`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
(2) Character string data
(3) Datetime data
(4) Binary data

(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 × 10³⁰⁸ to -2.3 × 10^-308, 0, and approximately 2.3 × 10^-308 to 1.7 × 10³⁰⁸.

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.

To Page Top

(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

To Page Top

(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.

To Page Top

(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.

To Page Top