DB2 10.5 for Linux, UNIX, and Windows

Retrieval of special values from DECFLOAT columns in Java applications

Special handling is necessary if you retrieve values from DECFLOAT columns, and the DECFLOAT columns contain the values NaN, Infinity, or -Infinity.

The recommended Java™ data type for retrieval of DECFLOAT column values is java.math.BigDecimal. However, you receive SQL error code -4231 if you perform either of these operations:

Retrieve the value NaN, Infinity, or -Infinity from a DECFLOAT column using the JDBC java.sql.ResultSet.getBigDecimal or java.sql.ResultSet.getObject method
Retrieve the value NaN, Infinity, or -Infinity from a DECFLOAT column into a java.math.BigDecimal variable in an SQLJ clause of an SQLJ program

You can circumvent this restriction by testing for the -4231 error, and retrieving the special value using the java.sql.ResultSet.getDouble or java.sql.ResultSet.getString method.

Suppose that the following SQL statements were used to create and populate a table.

CREATE TABLE TEST.DECFLOAT_TEST
(
 INT_VAL INT,
 DECFLOAT_VAL DECFLOAT
);
INSERT INTO TEST.DECFLOAT_TEST (INT_VAL, DECFLOAT_VAL) VALUES (1, 123.456),
INSERT INTO TEST.DECFLOAT_TEST (INT_VAL, DECFLOAT_VAL) VALUES (2, INFINITY),
INSERT INTO TEST.DECFLOAT_TEST (INT_VAL, DECFLOAT_VAL) VALUES (3, -123.456),
INSERT INTO TEST.DECFLOAT_TEST (INT_VAL, DECFLOAT_VAL) VALUES (4, -INFINITY),
INSERT INTO TEST.DECFLOAT_TEST (INT_VAL, DECFLOAT_VAL) VALUES (5, NaN);

The following code retrieves the contents of the DECFLOAT column using the java.sql.ResultSet.getBigDecimal method. If retrieval fails because the column value is NaN, INFINITY, or -INFINITY, the program retrieves the value using the java.sql.ResultSet.getBigDouble method.

final static int DECFLOAT_SPECIALVALUE_ENCOUNTERED = -4231;
java.sql.Connection con =
  java.sql.DriverManager.getConnection("jdbc:db2://localhost:50000/sample"
    , "userid", "password");
java.sql.Statement stmt = con.createStatement();
java.sql.ResultSet rs = stmt.executeQuery(
 "SELECT INT_VAL, DECFLOAT_VAL FROM TEST.DECFLOAT_TEST ORDER BY INT_VAL");
int i = 0;
while (rs.next()) {
 try {
  System.out.println("\nRow ” + ++i);
  System.out.println("INT_VAL      = " + rs.getInt(1));
  System.out.println("DECFLOAT_VAL = " + rs.getBigDecimal(2));
 } 
 catch (java.sql.SQLException e) {
  System.out.println("Caught SQLException" + e.getMessage());
  if (e.getErrorCode() == DECFLOAT_SPECIALVALUE_ENCOUNTERED) {
  // getBigDecimal failed because the retrieved value is NaN,
  // INFINITY, or -INFINITY, so retry with getDouble.
     double d = rs.getDouble(2);
     if (d == Double.POSITIVE_INFINITY) {
       System.out.println("DECFLOAT_VAL = +INFINITY");
     } else if (d == Double.NEGATIVE_INFINITY) {
       System.out.println("DECFLOAT_VAL = -INFINITY");
     } else if (d == Double.NaN) {
       System.out.println("DECFLOAT_VAL = NaN");
     } else {
       System.out.println("DECFLOAT_VAL = " + d);
     }
  } else {
     e.printStackTrace();
    }
 }

final static int DECFLOAT_SPECIALVALUE_ENCOUNTERED = -4231;
java.sql.Connection con =
  java.sql.DriverManager.getConnection("jdbc:db2://localhost:50000/sample"
    , "userid", "password");
java.sql.Statement stmt = con.createStatement();
java.sql.ResultSet rs = stmt.executeQuery(
 "SELECT INT_VAL, DECFLOAT_VAL FROM TEST.DECFLOAT_TEST ORDER BY INT_VAL");
int i = 0;
while (rs.next()) {
 try {
  System.out.println("\nRow ” + ++i);
  System.out.println("INT_VAL      = " + rs.getInt(1));
  System.out.println("DECFLOAT_VAL = " + rs.getBigDecimal(2));
 } 
 catch (java.sql.SQLException e) {
  System.out.println("Caught SQLException" + e.getMessage());
  if (e.getErrorCode() == DECFLOAT_SPECIALVALUE_ENCOUNTERED) {
  // getBigDecimal failed because the retrieved value is NaN,
  // INFINITY, or -INFINITY, so retry with getString.
     System.out.println("DECFLOAT_VAL = "+rs.getString(2));
  } else {
     e.printStackTrace();
    }
 }