7.6.1 Mapping data types
This subsection explains mapping between HADB's data types and JDBC's SQL data types.
- Organization of this subsection
(1) Correspondence between HADB's data types and JDBC's SQL data types
There is not an exact one-to-one match between the HADB data types and the JDBC SQL data types. For this reason, the JDBC driver performs mapping (conversion) between HADB's data types and JDBC's SQL data types. If an unmappable data type is used for data access, the JDBC driver throws an SQLException.
The data types are mapped with getXXX and setXXX methods in the ResultSet or PreparedStatement class. For details about the mapping rules employed by the getXXX and setXXX methods, see the documentation for the JDBC 1.0 standard or the JDBC 2.0 basic standard.
The following table shows the correspondences between HADB's data types and JDBC's SQL data types.
|
HADB's data type |
JDBC's SQL data type |
|---|---|
|
INTEGER |
BIGINT |
|
SMALLINT |
INTEGER, SMALLINT#1 |
|
DECIMAL |
DECIMAL (, NUMERIC)#2 |
|
DOUBLE PRECISION |
DOUBLE |
|
CHAR |
CHAR |
|
VARCHAR |
VARCHAR (, LONGVARCHAR)#2 |
|
DATE |
DATE |
|
TIME |
TIME |
|
TIMESTAMP |
TIMESTAMP |
|
BINARY |
BINARY (, VARBINARY, LONGVARBINARY)#2 |
|
VARBINARY |
VARBINARY (, BINARY, LONGVARBINARY)#2 |
|
ROW |
BINARY (, VARBINARY, LONGVARBINARY)#2 |
|
BOOLEAN#3 |
BOOLEAN |
- #1
-
This column is found only in a ResultSet created from DatabaseMetaData. If the column's data type is defined as short, the JDBC driver returns the value corresponding to SMALLINT for the metadata that can be obtained by ResultSetMetaData. For data other than metadata, the JDBC driver returns the value corresponding to INTEGER.
- #2
-
Data types shown in parentheses are supported only when JDBC's SQL data types are specified in the arguments of the setNull or setObject method. Otherwise, they are not supported for mapping from HADB's data types to JDBC's SQL data types.
- #3
-
This refers to a BOOLEAN column in a ResultSet object that is generated by the getTypeInfo method of DatabaseMetaData.
(2) Mapping during retrieval data acquisition
The table below shows the mapping between the getXXX methods in the ResultSet class and JDBC's SQL data types. If a getXXX method is called for one of JDBC's unmappable SQL data types, the JDBC driver throws an SQLException.
|
Method name |
JDBC's SQL data type |
|||||||||
|---|---|---|---|---|---|---|---|---|---|---|
|
BIG |
INT |
DCML |
DBL |
CHAR |
VCHR |
DATE |
TIME |
TSTMP |
BIN, VARBIN |
|
|
getByte |
Y |
Y |
Y |
Y |
Y# |
Y# |
N |
N |
N |
N |
|
getShort |
Y |
Y |
Y |
Y |
Y# |
Y# |
N |
N |
N |
N |
|
getInt |
Y |
Rec |
Y |
Y |
Y# |
Y# |
N |
N |
N |
N |
|
getLong |
Rec |
Y |
Y |
Y |
Y# |
Y# |
N |
N |
N |
N |
|
getFloat |
Y |
Y |
Y |
Y |
Y# |
Y# |
N |
N |
N |
N |
|
getDouble |
Y |
Y |
Y |
Rec |
Y# |
Y# |
N |
N |
N |
N |
|
getBigDecimal |
Y |
Y |
Rec |
Y |
Y# |
Y# |
N |
N |
N |
N |
|
getBoolean |
Y |
Y |
Y |
Y |
Y |
Y |
N |
N |
N |
N |
|
getString |
Y |
Y |
Y |
Y |
Rec |
Rec |
Y |
Y |
Y |
Y |
|
getBytes |
N |
N |
N |
N |
N |
N |
N |
N |
N |
Rec |
|
getDate |
N |
N |
N |
N |
Y# |
Y# |
Rec |
Y |
Y |
N |
|
getTime |
N |
N |
N |
N |
Y# |
Y# |
N |
Rec |
Y |
N |
|
getTimestamp |
N |
N |
N |
N |
Y# |
Y# |
Y |
Y |
Rec |
N |
|
getAsciiStream |
N |
N |
N |
N |
Y |
Y |
N |
N |
N |
Y |
|
getObject |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
|
getCharacterStream |
N |
N |
N |
N |
Y |
Y |
N |
N |
N |
Y |
|
getBinaryStream |
N |
N |
N |
N |
N |
N |
N |
N |
N |
Y |
- Legend:
-
BIG: BIGINT
INT: INTEGER
DBL: DOUBLE
DCML: DECIMAL
VCHR: VARCHAR
TSTMP: TIMESTAMP
BIN: BINARY
VARBIN: VARBINARY
Rec: Mapping is recommended.
Y: Can be mapped. Note, however, that data loss or a conversion error might occur, depending on the format of the conversion-source data.
N: Cannot be mapped.
- #
-
During conversion for this method, the JDBC driver removes any spaces preceding or following the character string data retrieved from the database. After removing the spaces, the JDBC driver converts the data to the Java data type returned by the getXXX method.
The following notes apply to converting data to a Java data type:
-
If character string data contains a fractional part and the getByte, getInt, getShort, or getLong method is executed, the JDBC driver discards the fractional part, and then converts and returns only the integer.
-
If the character string data contains double-byte characters, the JDBC driver throws an SQLException without converting the data.
-
If overflow occurs after character string data is converted to a Java data type, the JDBC driver throws an SQLException.
-
(3) Mapping when a dynamic parameter is specified
The following table lists the setXXX methods of the PreparedStatement class and JDBC's SQL data types that can be mapped to the methods. For a JDBC's SQL data type that cannot be used, an SQLException is thrown.
If a setXXX method is called for one of JDBC's unmappable SQL data types, the JDBC driver throws an SQLException.
|
Method name |
JDBC's SQL data type |
|||||||||
|---|---|---|---|---|---|---|---|---|---|---|
|
BIGINT |
INTEGER |
DECIMAL#1 |
DOUBLE |
CHAR |
VARCHAR |
DATE |
TIME |
TIMESTAMP |
BINARY, VARBINARY |
|
|
setByte |
Y |
Y |
Y |
Y |
Y |
Y |
N |
N |
N |
N |
|
setShort |
Y |
Y |
Y |
Y |
Y |
Y |
N |
N |
N |
N |
|
setInt |
Y |
Rec |
Y |
Y |
Y |
Y |
N |
N |
N |
N |
|
setLong |
Rec |
Y |
Y |
Y |
Y |
Y |
N |
N |
N |
N |
|
setFloat |
Y |
Y |
Y |
Y |
Y |
Y |
N |
N |
N |
N |
|
setDouble |
Y |
Y |
Y |
Rec |
Y |
Y |
N |
N |
N |
N |
|
setBigDecimal |
Y |
Y |
Rec |
Y |
Y |
Y |
N |
N |
N |
N |
|
setBoolean |
Y |
Y |
Y |
Y |
Y |
Y |
N |
N |
N |
N |
|
setString |
Y |
Y |
Y |
Y |
Rec |
Rec |
Y |
Y |
Y |
Y |
|
setBytes |
N |
N |
N |
N |
N |
N |
N |
N |
N |
Rec |
|
setDate |
N |
N |
N |
N |
Y |
Y |
Rec |
Y |
Y |
N |
|
setTime |
N |
N |
N |
N |
Y |
Y |
N |
Rec |
Y |
N |
|
setTimestamp#2 |
N |
N |
N |
N |
Y |
Y |
Y |
Y |
Rec |
N |
|
setAsciiStream |
N |
N |
N |
N |
Y |
Y |
N |
N |
N |
Y |
|
setObject#3 |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
|
setCharacterStream |
N |
N |
N |
N |
Y#4 |
Y#4 |
N |
N |
N |
Y |
|
setBinaryStream |
N |
N |
N |
N |
N |
N |
N |
N |
N |
Y |
- Legend:
-
Rec: Mapping is recommended.
Y: Can be mapped. Note, however, that data loss or a conversion error might occur, depending on the format of the conversion-source data.
N: Cannot be mapped.
- #1
-
If a setXXX method specifies a value for a dynamic parameter of HADB's DECIMAL data type, and the dynamic parameter and the value have different precisions or scalings, the JDBC driver performs one of the following operations, as applicable:
-
When the value has a larger precision than the dynamic parameter: Throws an SQLException.
-
When the value has a smaller precision than the dynamic parameter: Increases the precision.
-
When the value has a larger scaling than the dynamic parameter: Truncates the value according to the actual scaling.
-
When the value has a smaller scaling than the dynamic parameter: Adds zeros to increase the scaling.
-
- #2
-
If a setXXX method specifies a value for a dynamic parameter of HADB's TIME or TIMESTAMP data type, and the dynamic parameter and the value have different precisions for the fractional seconds part, the JDBC driver performs one of the following operations:
-
If the value has a larger fractional seconds precision than the dynamic parameter: Truncates the fractional seconds part of the value.
-
If the value has a smaller fractional seconds precision than the dynamic parameter: Expands the fractional seconds part of the value.
-
- #3
-
Objects of the InputStream class and the Reader class (including subclasses) cannot be specified in the setObject method.
- #4
-
If the length of the data that can be retrieved from a java.io.Reader object is shorter than the length specified in the arguments, the JDBC driver adds zeros as shown below until the length specified in the arguments is reached:
![[Figure]](GRAPHICS/F0000093.GIF)