Looking at the derby code on the main trunk, I see the exception is
intentional, as there is an explicit check for NaN values in
NumberDataType.normalizeDOUBLE().
Not sure if this is another DB2 compatability issue/limitation or not.
Maybe someone with deeper knowledge can give more details on this?
Seems your only option at the moment is to add another column to your
table to flag NaN values.
Thomas
Thomas Nielsen wrote:
I quickly tried both of Andrews 1) and 2) suggestions. Results pasted
below.
Summary:
- both PreparedStatement.setDouble() and setFloat() throw the same
Exception on respective .setXXX( index, XXX.NaN);
- setFloat() has reference to DOUBLE range in exception message, which
would look strange to a user.
I haven't checked the JDBC spec, but I can't imagine storing Double.NaN
or Float.NaN should be illegal in JDBC. It may be implementation defined
though. How the db chooses to actually store the NaN values is another
discussion.
Cheers,
Thomas
--- snip ---
Getting connection ...
Creating tables ...
Inserting Double.NaN ...
java.sql.SQLException: The resulting value is outside the range for the
data type DOUBLE.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
at
org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:202)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:391)
at
org.apache.derby.impl.jdbc.EmbedResultSet.noStateChangeException(EmbedResultSet.java:4381)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.setDouble(EmbedPreparedStatement.java:473)
at TestNaN.test(TestNaN.java:60)
at TestNaN.test(TestNaN.java:30)
at TestNaN.main(TestNaN.java:25)
Caused by: ERROR 22003: The resulting value is outside the range for the
data type DOUBLE.
at
org.apache.derby.iapi.error.StandardException.newException(StandardException.java:290)
at
org.apache.derby.iapi.types.NumberDataType.normalizeDOUBLE(NumberDataType.java:539)
at
org.apache.derby.iapi.types.SQLDouble.setValue(SQLDouble.java:412)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.setDouble(EmbedPreparedStatement.java:470)
... 3 more
Inserting Float.NaN ...
java.sql.SQLException: The resulting value is outside the range for the
data type DOUBLE.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
at
org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:202)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:391)
at
org.apache.derby.impl.jdbc.EmbedResultSet.noStateChangeException(EmbedResultSet.java:4381)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.setFloat(EmbedPreparedStatement.java:452)
at TestNaN.test(TestNaN.java:69)
at TestNaN.test(TestNaN.java:30)
at TestNaN.main(TestNaN.java:25)
Caused by: ERROR 22003: The resulting value is outside the range for the
data type DOUBLE.
at
org.apache.derby.iapi.error.StandardException.newException(StandardException.java:290)
at
org.apache.derby.iapi.types.NumberDataType.normalizeDOUBLE(NumberDataType.java:539)
at
org.apache.derby.iapi.types.SQLDouble.setValue(SQLDouble.java:421)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.setFloat(EmbedPreparedStatement.java:449)
... 3 more
Closing connection ...
--- snip ---
Andrew ``Bass'' Shcheglov wrote:
David,
could you please:
1. post a (partial, at least) stack trace along with a detail message
you get;
2. try a similar insertion of a Float.NaN into a FLOAT field and tell
what happens?
String versions are only good if performance is really not an issue,
or record matching will be extremely slow.
Otherwise, if your double values can be NaNs but are essentially not
nullable,
you probably should use NULLs to represent NaNs at database level.
Regards,
Andrew.
--
Thomas Nielsen
