11.4.2 Correspondence between distributed server data types and HiRDB data types

Execution of the DESCRIBE statement converts distributed server data types into the corresponding HiRDB data types. The results are set in the SQL Descriptor Area. If no corresponding HiRDB data type exists for a particular distributed server data type, data code 0 is set in SQLDA.

A UAP must be created so that the DESCRIBE statement is executed first, then the desired column in the table at the distributed server can be accessed using variables of the appropriate data types that are set in the SQL Descriptor Area. However, there are some exceptions to this rule (e.g., accessing a DATE-type column using a CHAR-type variable).

Organization of this subsection
(1) HiRDB distributed server
(2) XDM/RD distributed server
(3) ORACLE distributed server
(4) RDB1 E2 distributed server
(5) SQL/K distributed server

(1) HiRDB distributed server

Table 11-7 shows the relationships between the data types that are set in the SQL Descriptor Area of a HiRDB distributed client after the DESCRIBE statement has executed and the data types of HiRDB.

Table 11-7 Data types set in SQL Descriptor Area of HiRDB after execution of DESCRIBE statement in the case of a HiRDB distributed server

HiRDB data typeData type set in SQL Descriptor Area of HiRDBDescription
INTEGERINTEGERInteger (4-byte binary format)
SMALLINTSMALLINTInteger (2-byte binary format)
DECIMAL (p, s)DECIMAL (p, s)Fixed-point number
[Figure]Precision (total number of digits) = p
[Figure] Scale factor (number of digits following the decimal point) = s
1 [Figure] p[Figure] 29, 0 [Figure] s[Figure] p
FLOATFLOATDouble-precision floating-point number
SMALLFLTSMALLFLTSingle-precision floating-point number
CHAR (n)CHAR (n)Fixed-length character string
VARCHAR (n)VARCHAR (n)Variable-length character string
NCHAR (n)NCHAR (n)Fixed-length national character string
NVARCHAR (n)NVARCHAR (n)Variable-length national character string
MCHAR (n)MCHAR (n)Fixed-length mixed character string
MVARCHAR (n)MVARCHAR (n)Variable-length mixed character string
DATE1DATEDate
TIME2TIMETime
INTERVAL YEAR TO DAYINTERVAL YEAR TO DAYDate interval
INTERVAL HOUR TO SECONDINTERVAL HOUR TO SECONDTime interval
BLOBBLOBBinary
ROWROWROW type
1 DATE-type variables are not supported by the distributed client facility. However, access to DATE-type columns can be performed by using CHAR(10)-type input variables, as in local access to HiRDB.
2 TIME-type variables are not supported by the distributed client facility. However, access to TIME-type columns can be performed by using CHAR(8)-type input variables, as in local access to HiRDB.

(2) XDM/RD distributed server

Table 11-8 shows the relationships between the data types that are set in the SQL Descriptor Area of a HiRDB distributed client after the DESCRIBE statement has executed and the data types of XDM/RD.

Table 11-8 Data types set in SQL Descriptor Area of HiRDB after execution of DESCRIBE statement in the case of an XDM/RD distributed server

XDM/RD data typeData type set in SQL Descriptor Area of HiRDBDescription
INTEGERINTEGERInteger (4-byte binary format)
SMALLINTSMALLINTInteger (2-byte binary format)
DECIMAL (p, s)DECIMAL (p, s)Fixed-point number
[Figure]Precision (total number of digits) = p
[Figure] Scale factor (number of digits following the decimal point) = s
1 [Figure] p[Figure] 29, 0 [Figure] s[Figure] p
LARGE DECIMAL (p, s)
FLOATFLOATDouble-precision floating-point number
SMALLFLTSMALLFLTSingle-precision floating-point number
CHAR (n)CHAR (n)Fixed-length character string
VARCHAR (n)VARCHAR (n)Variable-length character string
LONG VARCHAR (n)
NCHAR (n)NCHAR (n)Fixed-length national character string
NVARCHAR (n)NVARCHAR (n)Variable-length national character string
LONG NVARCHAR (n)
MCHAR (n)MCHAR (n)Fixed-length mixed character string
MVARCHAR (n)MVARCHAR (n)Variable-length mixed character string
LONG MVARCHAR (n)
DATE1DATEDate
TIME2TIMETime
INTERVAL YEAR TO DAYINTERVAL YEAR TO DAYDate interval
INTERVAL HOUR TO SECONDINTERVAL HOUR TO SECONDTime interval
ROWROWROW type
1 DATE-type variables are not supported by the distributed client facility. However, access to DATE-type columns can be performed by using CHAR(10)-type input variables, as in local access to HiRDB.
2 TIME-type variables are not supported by the distributed client facility. However, access to TIME-type columns can be performed by using CHAR(8)-type input variables, as in local access to HiRDB.

(3) ORACLE distributed server

Table 11-9 shows the relationships between the data types that are set in the SQL Descriptor Area of a HiRDB distributed client after the DESCRIBE statement has executed and the data types of ORACLE.

Table 11-9 Data types set in SQL Descriptor Area of HiRDB after execution of DESCRIBE statement in the case of an ORACLE distributed server

ORACLE data typeData type set in SQL Descriptor Area of HiRDBDescription
NUMBER (p, s)DECIMAL (p, s)Fixed-point number
[Figure]Precision (total number of digits) = p
[Figure] Scale factor (number of digits following the decimal point) = s
1 [Figure] p[Figure] 29, 0 [Figure] s[Figure] p
NUMBER (p, s)1DECIMAL (p, 0)Fixed-point number
[Figure]Precision (total number of digits) = p
[Figure] Scale factor (number of digits following the decimal point) = s
1 [Figure] p[Figure] 29, s < 0
NUMBER (p, s)2DECIMAL (p, p)Fixed-point number
[Figure]Precision (total number of digits) = p
[Figure] Scale factor (number of digits following the decimal point) = s
1 [Figure] p[Figure] 29, s > p
NUMBER3FLOATDouble-precision floating-point number
NUMBER (p, s)4Fixed-point number
[Figure]Precision (total number of digits) = p
[Figure] Scale factor (number of digits following the decimal point) = s
30 [Figure] p[Figure] 38, 0 [Figure] s[Figure] p
CHAR (n)CHAR (n)Fixed-length character string
n[Figure] 255
VARCHAR2 (n)VARCHAR (n)Variable-length character string
n[Figure] 2000
LONGVARCHAR (32000)5Variable-length character string
DATE6DATEDate
ROWData code 0Data code 0 is set in SQLDA because no corresponding data type is found in the HiRDB database.
LONG ROW
ROWID
MLSLABEL
1 If s < 0 and data is entered by the UPDATE or INSERT statement into an appropriate column based on the results of executing the DESCRIBE statement, the data is subject to rounding depending on the actual value of the input data.
2 If s > p and data is entered by the UPDATE or INSERT statement into an appropriate column based on the results of executing the DESCRIBE statement, a precision error can occur depending on the actual value of the input data.
3 The permissible range of the absolute values of NUMBER-type data is 1.0E-129 through 9.99...E125 with a precision of 38 decimal digits. NUMBER-type columns can be accessed using a FLOAT-type output variable at the expense of reduced numeric value precision.
4 In the case of p > 29, NUMBER-type columns can be accessed using a FLOAT-type output variable at the expense of reduced numeric value precision.
5 A maximum of 2 GB of character data can be stored as LONG-type data. Therefore, a VARCHAR(32000)-type output variable can retrieve only part of the data in some cases.
6 DATE-type variables are not supported by the distributed client facility. However, access to DATE-type columns can be performed by using CHAR(10)-type input variables, as in local access to HiRDB. Although the DATE type of ORACLE contains time information as part of the data, only the date information can be accessed from a distributed client.

(4) RDB1 E2 distributed server

Table 11-10 shows the relationships between the data types that are set in the SQL Descriptor Area of a HiRDB distributed client after the DESCRIBE statement has been executed and the data types of RDB1 E2.

Table 11-10 Data types set in SQL Descriptor Area of HiRDB after execution of DESCRIBE statement in the case of an RDB1 E2 distributed server

RDB1 E2 data typeData type set in SQL Descriptor Area of HiRDBDescription
INTEGERINTEGERInteger (4-byte binary format)
SMALLINTSMALLINTInteger (2-byte binary format)
DECIMAL (p, s)DECIMAL (p, s)Fixed-point number
Precision (total number of digits) = p
Scale factor (number of digits following the decimal number) = s
1 [Figure] p[Figure] 29, 0 [Figure] s[Figure] p
FLOATFLOATDouble-precision floating-point number
SMALLFLTSMALLFLTSingle-precision floating-point number
CHAR (n)CHAR (n)Fixed-length character string
n[Figure] 254
VARCHAR (n)VARCHAR (n)Variable-length character string
n[Figure] 254
LONG VARCHAR (n)VARCHAR (n)Variable-length character string
255 [Figure] n[Figure] 4000
NCHAR (n)NCHAR (n)Fixed-length national character string
n[Figure] 127
NVARCHAR (n)NVARCHAR (n)Variable-length national character string
n[Figure] 127
ROWROWROW type

(5) SQL/K distributed server

Table 11-11 shows the relationships between the data types that are set in the SQL Descriptor Area of a HiRDB distributed client after the DESCRIBE statement has been executed and the data types of SQL/K.

Table 11-11 Data types set in SQL Descriptor Area of HiRDB after execution of DESCRIBE statement in the case of an SQL/K distributed server

SQL/K data typeData type set in SQL Descriptor Area of HiRDBDescription
INTEGERINTEGERInteger (4-byte binary format)
SMALLINTSMALLINTInteger (2-byte binary format)
DECIMAL (p,s)DECIMAL (p,s)Fixed-point number
Precision (total number of digits) = p
Scale factor (number of digits following the decimal number) = s
1 [Figure] p[Figure] 29, 0 [Figure] s[Figure] p
CHAR (n)CHAR (n)Fixed-length character string
n[Figure] 32000
CHAR (n)CHAR(32000)1Fixed-length character string
n > 32000
NCHAR (n)NCHAR (n)Fixed-length national character string
n[Figure] 16000
NCHAR (n)NCHAR (16000)2Fixed-length national character string
n > 16000
MCHAR (n)MCHAR (n)Fixed-length mixed character string
n[Figure] 32000
MCHAR (n)MCHAR(32000)1Fixed-length mixed character string
n > 32000
LARGE INTData code 0Data code 0 is set in SQLDA because no corresponding data type is found in the HiRDB database.
NUMERIC
TRAILING (P,S)
NUMERIC
UNSIGNED (P,S)
XCHAR (n)
BIT (n)
1 If n > 32000, using a VARCHAR(32000)-type output variable may result in partial data search only.
2 If n > 16000, using an NVARCHAR(16000)-type variable may result in partial data search only.