469,913 Members | 2,234 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,913 developers. It's quick & easy.

IBM JDBC driver behaves differently on Linux than on Solaris

Hi. Below is a simple JDBC program to insert and extract
a numerical value. When ResultSet.getDouble() is called,
the same program produces different output on solaris
than it does on Linux. I would be grateful for any
discussion of this!
thanks,
Joe Weinstein at BEA Systems

Results on Linux Box
-----------------------------------------------------------------------
-bash-2.05b$ java db2
08.02.0001
Database version is 08.02.0000
Testing value 1.001
native driver object is a class java.math.BigDecimal
value is 1.001
as getDouble it's 1.0010000000000001 <<<<<<<<<< SEE THIS!
native driver object is a class java.math.BigDecimal
value is 1.001
as getDouble it's 1.0010000000000001 <<<<<<<<<< SEE THIS!
double d = 1.001d gives 1.001
new Double("1.001").doubleValue() gives 1.001
they are equal.
new BigDecimal(1.001d) gives 1.000999999999999889865875957184471189975738525390 6
25
new BigDecimal( new Double("1.001").doubleValue() ) gives 1.000999999999999889865875957184471189975738525390 625
Results on Solaris Box
---------------------------------------------------------------------------
bash-3.00$java db2
08.02.0002
Database version is 08.02.0000
Testing value 1.001
native driver object is a class java.math.BigDecimal
value is 1.001
as getDouble it's 1.001 <<<<<<<<<< DIFFERENT!
native driver object is a class java.math.BigDecimal
value is 1.001 <<<<<<<<<< DIFFERENT!
as getDouble it's 1.001
double d = 1.001d gives 1.001
new Double("1.001").doubleValue() gives 1.001
they are equal.
new BigDecimal(1.001d) gives 1.000999999999999889865875957184471189975738525390 625
new BigDecimal( new Double("1.001").doubleValue() ) gives 1.000999999999999889865875957184471189975738525390 625

The program:

------------------------------------------------------------------------------------------
import java.io.*;
import java.util.*;
import java.net.*;
import java.sql.*;

import weblogic.common.*;

public class db2
{
public static void main(String argv[])
throws Exception
{
Connection c = null;
try
{

java.util.Properties props = new java.util.Properties();
Driver d = (Driver)Class.forName("COM.ibm.db2.jdbc.app.DB2Dri ver").newInstance();
props.put("user", "wls");
props.put("password", "wls");
props.put("DatabaseName", "wls");
String URL = "jdbc:db2:wls1";

c = d.connect(URL, props);

DatabaseMetaData dm = c.getMetaData();
System.out.println(dm.getDriverVersion());
System.out.println("Database version is " + dm.getDatabaseProductVersion() );

Statement s = c.createStatement();
try{s.executeUpdate("drop table joe");}catch (Exception ignore){}
s.executeUpdate("create table joe (bar decimal(4,3))");
s.executeUpdate("insert into joe values(1.001)");
PreparedStatement p = c.prepareStatement("insert into joe values(?)");
p.setDouble(1, new Double("1.001").doubleValue());
p.executeUpdate();

System.out.println("Testing value " + new Double("1.001").doubleValue() );
ResultSet r = s.executeQuery("select * from joe");
while (r.next())
{
System.out.println("native driver object is a " + r.getObject(1).getClass() );
System.out.println(" value is " + r.getObject(1) );
System.out.println("as getDouble it's " + r.getDouble(1) );
}

double d = 1.001d;
double dd = new Double("1.001").doubleValue();
System.out.println("double d = 1.001d gives " + d );
System.out.println("new Double(\"1.001\").doubleValue() gives " + dd );
if (d == dd)
System.out.println("they are equal." );
else
System.out.println("they are not equal." );

System.out.println("new BigDecimal(1.001d) gives " + new BigDecimal(1.001d) );
System.out.println("new BigDecimal( new Double(\"1.001\").doubleValue() ) gives "
+ new BigDecimal( new Double("1.001").doubleValue() ) );

}
catch (Exception e)
{ e.printStackTrace(); }
finally
{ try {c.close();}catch (Exception e){} }
}
}

Feb 16 '06 #1
15 2228
Joe Weinstein wrote:
Results on Linux Box
--------------------------------------------------------------
-bash-2.05b$ java db2
08.02.0001
Database version is 08.02.0000
Testing value 1.001
native driver object is a class java.math.BigDecimal
value is 1.001
as getDouble it's 1.0010000000000001 <<<<<<<<<< SEE
THIS! native driver object is a class java.math.BigDecimal
value is 1.001
as getDouble it's 1.0010000000000001 <<<<<<<<<< SEE
THIS! double d = 1.001d gives 1.001
new Double("1.001").doubleValue() gives 1.001
they are equal.
new BigDecimal(1.001d) gives
1.000999999999999889865875957184471189975738525390 6 25
new BigDecimal( new Double("1.001").doubleValue() ) gives
1.000999999999999889865875957184471189975738525390 625
Results on Solaris Box
---------------------------------------------------------------
bash-3.00$java db2
08.02.0002
Database version is 08.02.0000
Testing value 1.001
native driver object is a class java.math.BigDecimal
value is 1.001
as getDouble it's 1.001 <<<<<<<<<<
DIFFERENT! native driver object is a class java.math.BigDecimal
value is 1.001 <<<<<<<<<<
DIFFERENT!
as getDouble it's 1.001
double d = 1.001d gives 1.001
new Double("1.001").doubleValue() gives 1.001
they are equal.
new BigDecimal(1.001d) gives
1.000999999999999889865875957184471189975738525390 625 new BigDecimal( new
Double("1.001").doubleValue() ) gives
1.000999999999999889865875957184471189975738525390 625
------------------------------------------------------------------


You should compare the binary representation of the double value. I assume
that _those_ are identical and what you are seeing is due to varying
implementations in your JVMs when the double value is converted to a
string.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Feb 17 '06 #2
Joe Weinstein wrote:
Hi. Below is a simple JDBC program to insert and extract
a numerical value. When ResultSet.getDouble() is called,
the same program produces different output on solaris
than it does on Linux.


String-Output of double/float values depends on the JVM.

But this isn't a problem of Java - its a problem we have since the
invention of floating point operations.

Thats why mercantile applications are using BCD, fixed point or simply
rounding every result to the precision needed.

Bernd
Feb 17 '06 #3
Thanks much. I think you're right. I'll do that.
thanks,
Joe

Knut Stolze wrote:
Joe Weinstein wrote:

Results on Linux Box
--------------------------------------------------------------
-bash-2.05b$ java db2
08.02.0001
Database version is 08.02.0000
Testing value 1.001
native driver object is a class java.math.BigDecimal
value is 1.001
as getDouble it's 1.0010000000000001 <<<<<<<<<< SEE
THIS! native driver object is a class java.math.BigDecimal
value is 1.001
as getDouble it's 1.0010000000000001 <<<<<<<<<< SEE
THIS! double d = 1.001d gives 1.001
new Double("1.001").doubleValue() gives 1.001
they are equal.
new BigDecimal(1.001d) gives
1.0009999999999998898658759571844711899757385253 906 25
new BigDecimal( new Double("1.001").doubleValue() ) gives
1.0009999999999998898658759571844711899757385253 90625
Results on Solaris Box
---------------------------------------------------------------
bash-3.00$java db2
08.02.0002
Database version is 08.02.0000
Testing value 1.001
native driver object is a class java.math.BigDecimal
value is 1.001
as getDouble it's 1.001 <<<<<<<<<<
DIFFERENT! native driver object is a class java.math.BigDecimal
value is 1.001 <<<<<<<<<<
DIFFERENT!
as getDouble it's 1.001
double d = 1.001d gives 1.001
new Double("1.001").doubleValue() gives 1.001
they are equal.
new BigDecimal(1.001d) gives
1.0009999999999998898658759571844711899757385253 90625 new BigDecimal( new
Double("1.001").doubleValue() ) gives
1.0009999999999998898658759571844711899757385253 90625
------------------------------------------------------------------

You should compare the binary representation of the double value. I assume
that _those_ are identical and what you are seeing is due to varying
implementations in your JVMs when the double value is converted to a
string.


Feb 17 '06 #4


Bernd Hohmann wrote:
Joe Weinstein wrote:
Hi. Below is a simple JDBC program to insert and extract
a numerical value. When ResultSet.getDouble() is called,
the same program produces different output on solaris
than it does on Linux.

String-Output of double/float values depends on the JVM.

But this isn't a problem of Java - its a problem we have since the
invention of floating point operations.

Thats why mercantile applications are using BCD, fixed point or simply
rounding every result to the precision needed.

Bernd


Thanks. I should have checked this out. I will.
Joe

Feb 17 '06 #5
Joe Weinstein wrote:
Thanks. I should have checked this out. I will.


Glad to help. If you need more or something else - DB2 JDBC bothers me
since years :-)

Bernd
Feb 17 '06 #6
Hi Knut. I just rechecked the code I sent, and it includes
some stuff to verify whether the JVM itself is printing
stuff differently, and it's not. It's just the driver's
getDouble() that behaves differently.
If you see anything I missed about checking the
JVM itself, let me know what to try. Thanks
Joe

Statement s = c.createStatement();
try{s.executeUpdate("drop table joe");}catch (Exception ignore){}
s.executeUpdate("create table joe (bar decimal(4,3))");
s.executeUpdate("insert into joe values(1.001)");
PreparedStatement p = c.prepareStatement("insert into joe values(?)");
p.setDouble(1, new Double("1.001").doubleValue());
p.executeUpdate();

System.out.println("Testing value " + new Double("1.001").doubleValue() );
ResultSet r = s.executeQuery("select * from joe");
while (r.next())
{
System.out.println("native driver object is a " + r.getObject(1).getClass() );
System.out.println(" value is " + r.getObject(1) );
System.out.println("as getDouble it's " + r.getDouble(1) );
}

double d = 1.001d;
double dd = new Double("1.001").doubleValue();
System.out.println("double d = 1.001d gives " + d );
System.out.println("new Double(\"1.001\").doubleValue() gives " + dd );
if (d == dd)
System.out.println("they are equal." );
else
System.out.println("they are not equal." );

System.out.println("new BigDecimal(1.001d) gives " + new BigDecimal(1.001d) );
System.out.println("new BigDecimal( new Double(\"1.001\").doubleValue() ) gives "
+ new BigDecimal( new Double("1.001").doubleValue() ) );

Knut Stolze wrote:
Joe Weinstein wrote:

Results on Linux Box
--------------------------------------------------------------
-bash-2.05b$ java db2
08.02.0001
Database version is 08.02.0000
Testing value 1.001
native driver object is a class java.math.BigDecimal
value is 1.001
as getDouble it's 1.0010000000000001 <<<<<<<<<< SEE
THIS! native driver object is a class java.math.BigDecimal
value is 1.001
as getDouble it's 1.0010000000000001 <<<<<<<<<< SEE
THIS! double d = 1.001d gives 1.001
new Double("1.001").doubleValue() gives 1.001
they are equal.
new BigDecimal(1.001d) gives
1.0009999999999998898658759571844711899757385253 906 25
new BigDecimal( new Double("1.001").doubleValue() ) gives
1.0009999999999998898658759571844711899757385253 90625
Results on Solaris Box
---------------------------------------------------------------
bash-3.00$java db2
08.02.0002
Database version is 08.02.0000
Testing value 1.001
native driver object is a class java.math.BigDecimal
value is 1.001
as getDouble it's 1.001 <<<<<<<<<<
DIFFERENT! native driver object is a class java.math.BigDecimal
value is 1.001 <<<<<<<<<<
DIFFERENT!
as getDouble it's 1.001
double d = 1.001d gives 1.001
new Double("1.001").doubleValue() gives 1.001
they are equal.
new BigDecimal(1.001d) gives
1.0009999999999998898658759571844711899757385253 90625 new BigDecimal( new
Double("1.001").doubleValue() ) gives
1.0009999999999998898658759571844711899757385253 90625
------------------------------------------------------------------

You should compare the binary representation of the double value. I assume
that _those_ are identical and what you are seeing is due to varying
implementations in your JVMs when the double value is converted to a
string.


Feb 17 '06 #7
Joe Weinstein wrote:
Hi Knut. I just rechecked the code I sent, and it includes
some stuff to verify whether the JVM itself is printing
stuff differently, and it's not. It's just the driver's
getDouble() that behaves differently.
If you see anything I missed about checking the
JVM itself, let me know what to try. Thanks
Joe

Statement s = c.createStatement();
try{s.executeUpdate("drop table joe");}catch (Exception ignore){}
s.executeUpdate("create table joe (bar decimal(4,3))");
s.executeUpdate("insert into joe values(1.001)");
PreparedStatement p = c.prepareStatement("insert into joe
values(?)"); p.setDouble(1, new Double("1.001").doubleValue());
p.executeUpdate();

System.out.println("Testing value " + new
Double("1.001").doubleValue() ); ResultSet r =
s.executeQuery("select * from joe"); while (r.next())
{
System.out.println("native driver object is a " +
r.getObject(1).getClass() ); System.out.println(" value is " +
r.getObject(1) ); System.out.println("as getDouble it's " +
r.getDouble(1) );
}

double d = 1.001d;
double dd = new Double("1.001").doubleValue();
System.out.println("double d = 1.001d gives " + d );
System.out.println("new Double(\"1.001\").doubleValue() gives " +
dd ); if (d == dd)
System.out.println("they are equal." );
else
System.out.println("they are not equal." );

System.out.println("new BigDecimal(1.001d) gives " + new
BigDecimal(1.001d) ); System.out.println("new BigDecimal( new
Double(\"1.001\").doubleValue() ) gives "
+ new BigDecimal( new Double("1.001").doubleValue() ) );


You are not looking at the binary representation of the numbers. Everything
else are just the usual rounding issues related to the conversion between
the internal binary format and the external decimal format.

You should also read this article to understand how floating point numbers
are represented in today's computer systems:
http://cch.loria.fr/documentation/IE...M/goldberg.pdf

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Feb 17 '06 #8


Knut Stolze wrote:
Joe Weinstein wrote:

Hi Knut. I just rechecked the code I sent, and it includes
some stuff to verify whether the JVM itself is printing
stuff differently, and it's not. It's just the driver's
getDouble() that behaves differently.
If you see anything I missed about checking the
JVM itself, let me know what to try. Thanks
Joe

Statement s = c.createStatement();
try{s.executeUpdate("drop table joe");}catch (Exception ignore){}
s.executeUpdate("create table joe (bar decimal(4,3))");
s.executeUpdate("insert into joe values(1.001)");
PreparedStatement p = c.prepareStatement("insert into joe
values(?)"); p.setDouble(1, new Double("1.001").doubleValue());
p.executeUpdate();

System.out.println("Testing value " + new
Double("1.001").doubleValue() ); ResultSet r =
s.executeQuery("select * from joe"); while (r.next())
{
System.out.println("native driver object is a " +
r.getObject(1).getClass() ); System.out.println(" value is " +
r.getObject(1) ); System.out.println("as getDouble it's " +
r.getDouble(1) );
}

double d = 1.001d;
double dd = new Double("1.001").doubleValue();
System.out.println("double d = 1.001d gives " + d );
System.out.println("new Double(\"1.001\").doubleValue() gives " +
dd ); if (d == dd)
System.out.println("they are equal." );
else
System.out.println("they are not equal." );

System.out.println("new BigDecimal(1.001d) gives " + new
BigDecimal(1.001d) ); System.out.println("new BigDecimal( new
Double(\"1.001\").doubleValue() ) gives "
+ new BigDecimal( new Double("1.001").doubleValue() ) );

You are not looking at the binary representation of the numbers. Everything
else are just the usual rounding issues related to the conversion between
the internal binary format and the external decimal format.


Actually, yes I am. I am banking off the spec for BigDecimal:
"Translates a double into a BigDecimal which is the exact decimal
representation of the double's binary floating-point value."

I'll see if I can make some more additions to make it clearer to me.
thanks,
Joe

You should also read this article to understand how floating point numbers
are represented in today's computer systems:
http://cch.loria.fr/documentation/IE...M/goldberg.pdf


Feb 17 '06 #9
Joe Weinstein wrote:
Actually, yes I am. I am banking off the spec for BigDecimal:
"Translates a double into a BigDecimal which is the exact decimal
representation of the double's binary floating-point value."


The issue I still have with this is that you are dealing with different
representations of floating point numbers and that you are still not
comparing the internal binary IEEE754 representation.

The Java manual (http://java.sun.com/j2se/1.4.2/docs/api/index.html) gives a
nice example, which most probably applies to your question:
-----------------------------------------------------
public BigDecimal(double val)

Translates a double into a BigDecimal. The scale of the BigDecimal is
the smallest value such that (10scale * val) is an integer.

Note: the results of this constructor can be somewhat unpredictable. One
might assume that new BigDecimal(.1) is exactly equal to .1, but it is
actually equal to .1000000000000000055511151231257827021181583404541 015625.
This is so because .1 cannot be represented exactly as a double (or, for
that matter, as a binary fraction of any finite length). Thus, the long
value that is being passed in to the constructor is not exactly equal
to .1, appearances notwithstanding.

The (String) constructor, on the other hand, is perfectly predictable:
new BigDecimal(".1") is exactly equal to .1, as one would expect.
Therefore, it is generally recommended that the (String) constructor be
used in preference to this one.
-----------------------------------------------------

I would recommend that you try "Double.compare" or
"Double.doubleToRawLongBits" instead.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Feb 20 '06 #10


Knut Stolze wrote:
Joe Weinstein wrote:

Actually, yes I am. I am banking off the spec for BigDecimal:
"Translates a double into a BigDecimal which is the exact decimal
representation of the double's binary floating-point value."

The issue I still have with this is that you are dealing with different
representations of floating point numbers and that you are still not
comparing the internal binary IEEE754 representation.

The Java manual (http://java.sun.com/j2se/1.4.2/docs/api/index.html) gives a
nice example, which most probably applies to your question:
-----------------------------------------------------
public BigDecimal(double val)

Translates a double into a BigDecimal. The scale of the BigDecimal is
the smallest value such that (10scale * val) is an integer.

Note: the results of this constructor can be somewhat unpredictable. One
might assume that new BigDecimal(.1) is exactly equal to .1, but it is
actually equal to .1000000000000000055511151231257827021181583404541 015625.
This is so because .1 cannot be represented exactly as a double (or, for
that matter, as a binary fraction of any finite length). Thus, the long
value that is being passed in to the constructor is not exactly equal
to .1, appearances notwithstanding.

The (String) constructor, on the other hand, is perfectly predictable:
new BigDecimal(".1") is exactly equal to .1, as one would expect.
Therefore, it is generally recommended that the (String) constructor be
used in preference to this one.
-----------------------------------------------------

I would recommend that you try "Double.compare" or
"Double.doubleToRawLongBits" instead.


I have also compared the doubles. It is only the Linux IBM getDouble()
method that fails. The getBigDecimal obviously gets the correct input
from the DBMS and produces an exact 1.001 in both cases, so we can
assume that the getDouble() has the same input. Using "1.001" as well
as the double approximation for 1.001 in every way I can on both
platforms, and I always get the same long decimal approximation on
Linux as I do on Solaris. It's not unpredictable:
1.001000000000000111910480882215779274702072143554 6875. I believe the
problem is somewhere in the IBM driver's code, that it comes up with
1.0010000000000001.

I may be taking up too much of your time, but I thank you for what you've
posted so far. Can you direct me to any IBM forum that might be more
closely interested in JDBC? I would pursue it there. Or if you have some
generic Java code that you'd suggest to prove/disprove your theory that
it's the JVM, I'll run it in both places.
thanks again,
Joe Weinstein at BEA


Feb 20 '06 #11


Oh, I just checked that last reply, sorry: The exact decimal
representation of the double I get on both platforms for
1.001, either directly declared ar via a string constructor
or the getDouble() on Solaris, is actually:

1.000999999999999889865875957184471189975738525390 625

On both platforms, Linux and Solaris, the double I construct
with those bits, or from the Solaris getDouble() prints as
1.001.

The value I misquoted in the last post was what I get
by displaying the bits exactly of the double that the
driver's getDouble() on linux returned. It prints as
1.0010000000000001

it's bits (as decimal) are:

1.001000000000000111910480882215779274702072143554 6875

Feb 20 '06 #12
Joe Weinstein wrote:
I have also compared the doubles. It is only the Linux IBM getDouble()
method that fails. The getBigDecimal obviously gets the correct input
from the DBMS and produces an exact 1.001 in both cases, so we can
assume that the getDouble() has the same input.


Have you used the "Double.doubleToRawLongBits" and printed that number?
This is giving you the internal binary representation.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Feb 20 '06 #13


Knut Stolze wrote:
Joe Weinstein wrote:

I have also compared the doubles. It is only the Linux IBM getDouble()
method that fails. The getBigDecimal obviously gets the correct input
from the DBMS and produces an exact 1.001 in both cases, so we can
assume that the getDouble() has the same input.

Have you used the "Double.doubleToRawLongBits" and printed that number?
This is giving you the internal binary representation.


Hi, yes I did. If the internal binary representation was different in any way,
then the BigDecimal constructor, which makes an exact decimal version of the
given double, would also differ. On both platforms, the double that prints as
1.001 has the same raw bits pattern as well as BigDecimal conversion:
doubleToRawLongBits gives 4607186922399644778
for a BigDecimal value 1.000999999999999889865875957184471189975738525390 625
This is the same for the Solaris getDouble().

It is *only* the Double returned by the driver's
getDouble() on Linux that prints as 1.0010000000000001,
with a raw bits pattern of 4607186922399644779,
which makes a BigDecimal 1.001000000000000111910480882215779274702072143554 6875

I can send you or anyone else in IBM the standalone
repro.
thanks
Joe Weinstein at BEA Systems

Feb 21 '06 #14
Joe Weinstein wrote:
Hi, yes I did. If the internal binary representation was different in any
way, then the BigDecimal constructor, which makes an exact decimal version
of the given double, would also differ. On both platforms, the double that
prints as 1.001 has the same raw bits pattern as well as BigDecimal
conversion: doubleToRawLongBits gives 4607186922399644778

It is *only* the Double returned by the driver's
getDouble() on Linux that prints as 1.0010000000000001,
with a raw bits pattern of 4607186922399644779,


Now I got it, I believe. Sorry that I was a bit too dense until now.

I tried this myself, and on Linux I get the raw bits: 4607186922399644778
from the following SQL statement:

SELECT DECIMAL(1.001, 5, 3) FROM sysibm.sysdummy1

I'm using the "com.ibm.db2.jcc.DB2Driver" and a type 4 driver.

So it would be a good idea to know more details about your environment, i.e.
DB2 version and FP level, etc.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Feb 21 '06 #15


Knut Stolze wrote:
Joe Weinstein wrote:

Hi, yes I did. If the internal binary representation was different in any
way, then the BigDecimal constructor, which makes an exact decimal version
of the given double, would also differ. On both platforms, the double that
prints as 1.001 has the same raw bits pattern as well as BigDecimal
conversion: doubleToRawLongBits gives 4607186922399644778

It is *only* the Double returned by the driver's
getDouble() on Linux that prints as 1.0010000000000001,
with a raw bits pattern of 4607186922399644779,

Now I got it, I believe. Sorry that I was a bit too dense until now.

I tried this myself, and on Linux I get the raw bits: 4607186922399644778
from the following SQL statement:

SELECT DECIMAL(1.001, 5, 3) FROM sysibm.sysdummy1

I'm using the "com.ibm.db2.jcc.DB2Driver" and a type 4 driver.

So it would be a good idea to know more details about your environment, i.e.
DB2 version and FP level, etc.


Thanks much. Database version is 08.02.0000. The driver is type-2.
That is the likely reason for the bug (platform dependence)
Joe

Feb 21 '06 #16

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Axel Dachtler | last post: by
2 posts views Thread by Raquel | last post: by
1 post views Thread by eugene | last post: by
3 posts views Thread by kavallin | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.