15.5.3 Notes about data type conversion

(1) Notes about the BINARY and VARBINARY types

■ Rules for comparing and storing or assigning

• The following rules apply to entering data to HADB's BINARY or VARBINARY type:

  • If the input data is SQL_C_WCHAR, after conversion to CHAR data, processing equivalent to conversion from SQL_C_CHAR to binary is performed.

  • If the input data is character string data, every 2 bytes of data is converted to 1 byte (8 bits) of binary data. If the input data is SQL_C_WCHAR, this processing is applied after the data is converted to SQL_C_CHAR. Two bytes of character string data represents one hexadecimal number. For example, 01 is converted to the binary 00000001, and FF is converted to the binary 11111111. Accordingly, an error occurs when half the byte length of the input data exceeds the definition length of the input destination.

• The following rules apply to entering SQL_C_BINARY type data to an HADB data type:

  • When the input destination is a data type that expects character string data (such as SQL_CHAR), an error occurs when the actual length of the input data exceeds the definition length of the target data.

■ Rules for retrieval

• The following rule applies when data is output from HADB's BINARY or VARBINARY type:

  • When receiving the data as character string data, each byte (8 bits) of the source data is represented by two ASCII characters. These characters are an ASCII representation of a hexadecimal number. For example, the binary 00000001 is converted to 01, and the binary 11111111 is converted to FF. If BuffeLength is less than twice the byte length of the source data (further doubled when the data is SQL_C_WCHAR), then the data is truncated and a warning output to that effect.

To Page Top

(2) Notes about the DATE, TIME, and TIMESTAMP types

• If date data of the DATE or CHAR type is converted to time stamp data of the TIMESTAMP type, zeros are set in the time part of the time stamp data.

• If time data of the TIME type or date data of the CHAR type is converted to time stamp data of the TIMESTAMP type, the current date is set in the date part of the time stamp data.

• If time data of the TIME or CHAR type is converted to time data of the TIME type or time stamp data of the TIMESTAMP type and the fractional seconds precision of the data before conversion exceeds that of the data after conversion, the excess part is discarded. In such a case, the return value is SQL_SUCCESS.

• If time stamp data of the TIMESTAMP or CHAR type is converted to date data of the DATE type, only the date part of the time stamp data is converted. In such a case, the return value is SQL_SUCCESS.

• If time stamp data of the TIMESTAMP or CHAR type is converted to time data of the TIME type, only the time part of the time stamp data is converted. In such a case, the return value is SQL_SUCCESS.

• If time stamp data of the TIMESTAMP or CHAR type is converted to time data of the TIME type or time stamp data of the TIMESTAMP type and the fractional seconds precision of the data before conversion exceeds that of the data after conversion, the excess part is discarded. In such a case, the return value is SQL_SUCCESS.

• The VARCHAR and STRING types are handled in the same manner as the CHAR type.

To Page Top

(3) Notes about the SQL_DECIMAL and SQL_NUMERIC types

In HADB, SQL_DECIMAL and SQL_NUMERIC types are treated identically. Either can be used.

If a fractional value less than 1, whose precision value is equal to its scaling value, is converted to the SQL_DECIMAL or SQL_NUMERIC type, and a zero is entered to the left of the decimal point, an error will occur. This is because the zero is also counted as one digit. You can convert data successfully by specifying the data that begins at the decimal point.

Example:

Retrieving or comparing and assigning a value whose data type defined in the table is DECIMAL(3,3):

• 0.123: An error occurs.

• .123: The data is converted successfully.

To Page Top

(4) Notes about the SQL_DOUBLE and SQL_FLOAT types

In HADB, SQL_DOUBLE and SQL_FLOAT types are treated identically. Either can be used.

To Page Top

(5) Notes about the SQL_C_NUMERIC type

• The maximum precision supported for HADB's SQL_C_NUMERIC type is 38.

• When converting data from SQL_REAL, SQL_DOUBLE, or SQL_FLOAT to SQL_C_NUMERIC, or from numeric character strings of SQL_CHAR, SQL_VARCHAR, or SQL_LONGVARCHAR to SQL_C_NUMERIC, if the source data is in floating-point number format, it is first converted to fixed-point and then to SQL_C_NUMERIC.

• The following rules apply when floating-point numbers are converted to fixed-point numbers or fixed-point numbers are converted to the SQL_C_NUMERIC type:

  • The format for floating-point numbers must comply with the format for floating-point numeric constants. A maximum of 38 digits are supported for the mantissa part and a maximum of three digits are supported for the exponent part. For details about the format for floating-point numeric constants, see the topic Description format of literals in the manual HADB SQL Reference.

  • The maximum and minimum values supported for the exponent are 38 and -38, respectively.

  • If the exponent is a positive value and the sum of the number of integer digits in the mantissa part and the value of the exponent exceeds 38, SQL_ERROR is returned because the integer is truncated when the data is converted to the fixed-point number format.

  • If the exponent is a negative value and the sum of the number of digits to the right of the decimal point in the mantissa part and the absolute value of the exponent exceeds 38, digit overflow occurs in the fractional part when data is converted to a fixed-point number. If the digits resulting in the overflow include a nonzero number, SQL_SUCCESS_WITH_INFO is returned; otherwise, SQL_SUCCESS is returned.

To Page Top