Re: Is derby sql "real" equivalent to java "float"

[Lance J. Andersen]

Given, Derby is an independent Apache product, i do not see a reason 
keep this restriction.

Dag H. Wanvik wrote:
Hi Jim,
I found the following code in Derby, which may be the issue:


       normalizeREAL checks the validity of the given java float that
       it fits within the range of DB2 REALs. In addition it
       normalizes the value, so that negative zero (-0.0) becomes positive.
        */
    public static float normalizeREAL(float v) throws StandardException
        {
        if ( (Float.isNaN(v) || Float.isInfinite(v)) ||
             ((v < Limits.DB2_SMALLEST_REAL) || (v > Limits.DB2_LARGEST_REAL)) 
||
             ((v > 0) && (v < Limits.DB2_SMALLEST_POSITIVE_REAL)) ||
             ((v < 0) && (v > Limits.DB2_LARGEST_NEGATIVE_REAL)) )
        {
              throw StandardException.newException(SQLState.LANG_OUTSIDE_RANGE_FOR_DATATYPE, 
                    TypeId.REAL_NAME); 

The limits are declared as:

    static final float DB2_SMALLEST_REAL = -3.402E+38f;
    static final float DB2_LARGEST_REAL  = +3.402E+38f;
    static final float DB2_SMALLEST_POSITIVE_REAL = +1.175E-37f;
    static final float DB2_LARGEST_NEGATIVE_REAL  = -1.175E-37f;

I think this code is old DB2 compatibility code, which could possibly be
lifted now. Does anyone know?

Thanks,
Dag



Jim Newsham <[EMAIL PROTECTED]> writes:

  
 

I just saw a derby exception in our app, which seemed to have failed while
converting a Java float to a Derby REAL. at least, that's what our code
should be doing, unless we have some sort of generics leak (unsafe
conversion or something).  I'll dig further to confirm that our code is
correct. Our codebase is pretty mature, and this is the first time I've seen
the error.

 

In the meantime, I'd like to ask.  Should Java float always be safely
settable to a field of Derby type REAL?  This has been my assumption all
along, and refderby.pdf seems to imply this.  Or, are there edge cases, such
as perhaps NaN which will cause the set operation to fail.

 

Here is the offending line in our code.  Presumably, a table which contains
a REAL field should have been selected by the same FLOAT enum value, so this
should be setting a REAL field.

 

      case FLOAT: stmt.setFloat(column, (Float) value); break;

 

Here is the stack trace.  It's too bad derby didn't include the offending
float value in the stack trace.  The failure seems to be generated in
NumberDataType.normalizeREAL.

 

Caused by: java.sql.SQLDataException: The resulting value is outside the
range for the data type REAL.

        at
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
Source)

        at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown
Source)

        at
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknow
n Source)

        at
org.apache.derby.impl.jdbc.EmbedResultSet.noStateChangeException(Unknown
Source)

        at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.setFloat(Unknown Source)

        at
com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.setParameter(Der
bySampleBase.java:576)

        at
com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.access$200(Derby
SampleBase.java:71)

        at
com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$1.perform(DerbyS
ampleBase.java:515)

        at
com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransac
tion(DerbySampleBase.java:2755)

        ... 6 more

Caused by: java.sql.SQLException: The resulting value is outside the range
for the data type REAL.

        at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
Source)

        at
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossD
RDA(Unknown Source)

        ... 15 more

Caused by: ERROR 22003: The resulting value is outside the range for the
data type REAL.

        at
org.apache.derby.iapi.error.StandardException.newException(Unknown Source)

        at org.apache.derby.iapi.types.NumberDataType.normalizeREAL(Unknown
Source)

        at org.apache.derby.iapi.types.SQLReal.setValue(Unknown Source)

        ... 11 more

 

Thanks,

Jim