Scalable Database Server, HiRDB Version 8 SQL Reference

[Contents][Index][Back][Next]

Appendix C.2 Foreign DB: XDM/RD E2

Tables C-3 and C-4 show data type correspondence (foreign DB: XDM/RD E2)

Table C-3 Data type correspondence (Foreign DB: XDM/RD E2)(1/2)

Data type of XDM/RD E2 (Foreign DB) Data type of HiRDB
SMALLINT INTEGER DECIMAL(m,n)
1 [Figure] n [Figure] 29, 0 [Figure] n [Figure] m		 SMALLFLTor REAL4 FLOAT or DOUBLE PRECISION4 CHAR(n)
1 [Figure] n [Figure] 255		 VARCHAR(n)
1 [Figure] n [Figure] 255		 MCHAR(n)
1 [Figure] n [Figure] 255		 MVARCHAR(n)
1 [Figure] n [Figure] 255
SMALLINT R
INTEGER
R
DECIMAL(p,s)
1 [Figure] p [Figure] 15,
0 [Figure] s [Figure] 15
R1, 5, 6
LARGE DECIMAL(p,s) or NUMERIC(p,s)
1 [Figure] p [Figure] 29,
0 [Figure] s [Figure] p
R1, 6
SMALLFLT or REAL
R
FLOAT or DOUBLE PRECISION
R
CHAR(n)
1 [Figure] n [Figure] 255
R7
CHAR(n)
256 [Figure] n
VARCHAR(n)
1 [Figure] n [Figure] 255
R7
VARCHAR(n)
256 [Figure] n
LONG VARCHAR(n)
1 [Figure] n [Figure] 255
R2, 7
LONG VARCHAR(n)
256 [Figure] n
MCHAR(n)
1 [Figure] n [Figure] 255
R7
MCHAR(n)
256 [Figure] n
MVARCHAR(n)
1 [Figure] n [Figure] 255
R7
MVARCHAR(n)
256 [Figure] n
LONG MVARCHAR(n)
1 [Figure] n [Figure] 255
R2, 7
LONG MVARCHAR(n)
256 [Figure] n
NCHAR(n)
1 [Figure] n [Figure] 127
NCHAR(n)
128 [Figure] n
NVARCHAR(n)
1 [Figure] n [Figure] 127
NVARCHAR(n)
128 [Figure] n
LONG NVARCHAR(n)
1 [Figure] n [Figure] 127
LONG NVARCHAR(n)
128 [Figure] n
DATE
TIME
TIME(p)
p = 0, 2, 4, or 6
TIMESTAMP
R3, 7
TIMESTAMP(p)
p = 0, 2, 4, or 6
R3, 7
INTERVAL YEAR TO DAY
INTERVAL HOUR TO SECOND
INTERVAL HOUR TO SECOND(p)
p = 0, 2, 4, or 6
BLOB

Legend:
R: Recommended data type
Blank: Not mappable data type

Note 1
If a character string type of 255 bytes is specified for a column greater than or equal to 256 bytes on a foreign server, only a maximum of 255 bytes of data can be acquired.

Note 2
If the character code for columns in XDM/RD E2 is either EBCDIK or KEIS, an error may occur if the data in a character string containing double-byte characters, after code conversion, is greater than 255 bytes.

1 If p and s are omitted, different foreign servers have different default precision and scaling. Therefore, the precision (p) and scaling (s) on the foreign server should be made equal to the precision (m) and the scaling (n) in the foreign table definition accordingly. When specifying these operands, make DECIMAL(m,n) equal to DECIMAL(p,s).

2 Specifying a column of the LONG VARCHAR type or LONG MVARCHAR type in the search condition can cause an error on the foreign server. For details about the LONG VARCHAR and LONG MVARCHAR types, see the manual XDM E2 System XDM/RD E2 SQL Reference.

3 This should be mapped to CHAR(n) (where n = 19, 22, 24, or 26). In HiRDB, the format of the datetime character string is 'YYYY-MM-DD hh:mm:ss' if n = 19, and 'YYYY-MM-DD hh:mm:ss.xxxxxx' if n = 22, 24, or 26 (where x is 2 digits long if n = 22, 4 digits long if n = 24, and 6 digits long if n = 26). Comparison and arithmetic operations performed on a given column can cause an error on the foreign DB or produce unexpected results.

4 The precision of values that can be represented may be reduced. The scope and precision of values on HiRDB are hardware-dependent.

5 An error may occur if the precision is greater than 15 digits as a result of an operation.

6 An error may occur if the number of digits in the integer part is less than the data type of HiRDB.

If the number of digits in the fractional part is less than the data type of HiRDB, data is acquired by truncating the excess fractional part to match the number of digits in the fractional part of the data type of HiRDB.

If the number of digits in the integer or fractional part is larger than the data type of HiRDB, data is acquired by zero-filling any extended portion to match the number of digits in the data type of HiRDB.

7 If there is no specification, make the definition length of the data type of the foreign server equal to that of HiRDB.

If the definition length (length = n bytes, n [Figure] 255) of the data type of HiRDB is less than the actual length of the data type of the foreign server, only the first n bytes are acquired.

If the definition length of the data type of HiRDB is greater, the data is inserted left-justified and any excess length is blank-filled (double-byte spaces for national character strings, single-byte spaces for character strings or mixed character strings), provided that HiRDB is of a fixed-length data type.

If HiRDB is of a variable-length data type, the data is acquired "as is."

Table C-4 Data type correspondence (Foreign DB: XDM/RD E2)(2/2)

Data type of XDM/RD E2
(foreign DB)		 Data type of HiRDB
NCHAR(n)
1 [Figure] n [Figure] 127		 NVARCHAR(n)
1 [Figure] n [Figure] 127		 DATE TIME TIMESTAMP(p)
p = 0, 2, 4, or 6		 INTERVAL YEAR TO DAY INTERVAL HOUR TO SECOND BINARY(n)
1 [Figure] n [Figure] 255
SMALLINT
INTEGER
DECIMAL(p,s)
1 [Figure] p [Figure] 15,
0 [Figure] s [Figure] 15
LARGE DECIMAL(p,s) or NUMERIC(p,s)
1 [Figure] p [Figure] 29,
0 [Figure] s [Figure] p
SMALLFLT or REAL
FLOAT or DOUBLE PRECISION
CHAR(n)
1 [Figure] n [Figure] 255
CHAR(n)
256 [Figure] n
VARCHAR(n)
1 [Figure] n [Figure] 255
VARCHAR(n)
256 [Figure] n
LONG VARCHAR(n)
1 [Figure] n [Figure] 255
LONG VARCHAR(n)
256 [Figure] n
MCHAR(n)
1 [Figure] n [Figure] 255
MCHAR(n)
256 [Figure] n
MVARCHAR(n)
1 [Figure] n [Figure] 255
MVARCHAR(n)
256 [Figure] n
LONG MVARCHAR(n)
1 [Figure] n [Figure] 255
LONG MVARCHAR(n)
256 [Figure] n
NCHAR(n)
1 [Figure] n [Figure] 127		 R4
NCHAR(n)
128 [Figure] n
NVARCHAR(n)
1 [Figure] n [Figure] 127
R4
NVARCHAR(n)
128 [Figure] n
LONG NVARCHAR(n)
1 [Figure] n [Figure] 127
R1, 4
LONG NVARCHAR(n)
128 [Figure] n
DATE
R
TIME
R
TIME(p)
p = 0, 2, 4,or 6
R2
TIMESTAMP
TIMESTAMP(p)
p = 0, 2, 4, or 6
INTERVAL YEAR TO DAY
R
INTERVAL HOUR TO SECOND
R
INTERVAL HOUR TO SECOND(p)
p = 0, 2, 4, or 6
R3
BLOB

Legend:
R: Recommended data type.
Blank: Not mappable data type

Note 1
If a character string type of 255 bytes is specified for a column greater than or equal to 256 bytes on a foreign server, only a maximum of 255 bytes of data can be acquired.

Note 2
If the character code for columns in XDM/RD E2 is either EBCDIK or KEIS, an error may occur if the data in a character string containing double-byte characters, after code conversion, is greater than 255 bytes.

1 Specifying a column of the LONG VARCHAR type or LONG NVARCHAR type in the search condition can cause an error on the foreign server. For details about the LONG VARCHAR and LONG NVARCHAR types, see the manual XDM E2 System XDM/RD E2 SQL Reference.

2 Mapping can be made into the TIME type only if the precision p of XDM/RD E2 is 0.

3 Mapping can be made into the INTERVAL HOUR TO SECOND type only if the precision p of XDM/RD E2 is 0.

4 If there is no specification, make the definition length of the data type of the foreign server equal to that of HiRDB.

If the definition length (length = n bytes, n [Figure] 255) of the data type of HiRDB is less than the actual length of the data type of the foreign server, only the first n bytes are acquired.

If the definition length of the data type of HiRDB is greater, the data is inserted left-justified and any excess length is blank-filled (double-byte spaces for national character strings, single-byte spaces for character strings or mixed character strings), provided that HiRDB is of a fixed-length data type.

If HiRDB is of a variable-length data type, the data is acquired "as is."

[Contents][Back][Next]

[Trademarks]

All Rights Reserved. Copyright (C) 2007, Hitachi, Ltd.