Floats become decimals when read from a resultset.

[Koen]

Hi,

first of all, the machine setup
server 1:
- UDB2 7.2.5; COUNTRY=1, location = US, IBM1250 codepage
- Locale: US English; Regional Settings: English; Keyboard: Dutch;
decimal separator: .
server 2:
- UDB2 7.2.5 Client Tools
- Websphere 4.0.5
- Locale: US English; Regional Settings: English; Keyboard: Dutch;
decimal separator: .
db2cli.ini has PATCH2=15 on both servers.

We run a webapplication. For example, when reading contract data, the
following happens:
- the webinterface on server 2 requests the result of a stored
procedure on server 1
--> when we run the Stored Procedure on server 1, we see that the
bookvalue from a car = 10000.21 (yes, with a dot)
--> when we do rs.getFloat or rs.getDouble to put the values in a
bizness object in the java code, we see 10000
We assume that the 10000.21 is placed in the resultset. It however
seems to be modified there. When we set 10000.21 in the bizness
object by ourself, it works.
==> Data read on server 1 becomes a decimal when read or seen on
server 2.

Now, when we post 10000.22 as new bookvalue on server 2, we can write
10000.22 in the database on server 1 without a problem!!

We use a jdbc connection, jdbc2 is in use.

We've searched more than 16 hours for a solution yesterday. I hope
someone here is smarter, or has a more original input then we can
produce at the moment.
Anything that could help will be grateful appreciated.

Does the locale setting during installation matters?

Thanks in advance,

Koen

 

[Paul Lutus]

Hi,

first of all, the machine setup
server 1:
- UDB2 7.2.5; COUNTRY=1, location = US, IBM1250 codepage
- Locale: US English; Regional Settings: English; Keyboard: Dutch;
decimal separator: .
server 2:
- UDB2 7.2.5 Client Tools
- Websphere 4.0.5
- Locale: US English; Regional Settings: English; Keyboard: Dutch;
decimal separator: .
db2cli.ini has PATCH2=15 on both servers.

We run a webapplication. For example, when reading contract data, the
following happens:
- the webinterface on server 2 requests the result of a stored
procedure on server 1
--> when we run the Stored Procedure on server 1, we see that the
bookvalue from a car = 10000.21 (yes, with a dot)
--> when we do rs.getFloat or rs.getDouble to put the values in a
bizness object in the java code, we see 10000
We assume that the 10000.21 is placed in the resultset. It however
seems to be modified there. When we set 10000.21 in the bizness
object by ourself, it works.

It seems obvious that, if you can see your code and cannot sort out what is
wrong, we, who cannot see your code, are even less likely to be able to
sort it out.

We might conclude there is a library bug involved, but we would certainly
want to eliminate a coding error first.

 

[Oscar kind]

first of all, the machine setup
server 1:
- UDB2 7.2.5; COUNTRY=1, location = US, IBM1250 codepage

I happen to recognise this, as I worked with DB2 before. It might be nice
to inform us you use a database.

- Locale: US English; Regional Settings: English; Keyboard: Dutch;
decimal separator: .
server 2:
- UDB2 7.2.5 Client Tools
- Websphere 4.0.5
- Locale: US English; Regional Settings: English; Keyboard: Dutch;
decimal separator: .
db2cli.ini has PATCH2=15 on both servers.

We run a webapplication.

With a databases behind it...

For example, when reading contract data, the
following happens:
- the webinterface on server 2 requests the result of a stored
procedure on server 1
--> when we run the Stored Procedure on server 1, we see that the
bookvalue from a car = 10000.21 (yes, with a dot)
--> when we do rs.getFloat or rs.getDouble to put the values in a
bizness object in the java code, we see 10000

^^^^^^^-> business

How do you retreive the value? As an Integer, Float, BigDecimal, ...?
Actual code helps here.

We assume that the 10000.21 is placed in the resultset. It however
seems to be modified there. When we set 10000.21 in the bizness
object by ourself, it works.

It is probably not changed by the driver. But there may be conversions
you're not aware of. Again, nobody can see anything without the code.

==> Data read on server 1 becomes a decimal when read or seen on
server 2.

Yes: a database may not support floating point numbers, and store them as
fixed point numbers instead. These are most accurately represented using a
BigDecimal (not a "decimal"), which is what the driver returns.

Now, when we post 10000.22 as new bookvalue on server 2, we can write
10000.22 in the database on server 1 without a problem!!

This depends on what you put into the database, and how this differs from
what you get out of it. Again, actual code helps here...

 

[Koen]

Hi,

thanks for the reply.
UDB2 = IBM Universal Database, so we indeed use a webapplication (runs
on server 2) with a database behind it on server 1.

In the database is the value 342.16 stored as price for a carrepair.
The stored procedure looks like this:
<code>
import java.sql.*; // JDBC classes
import sqlj.runtime.*;
import sqlj.runtime.ref.*;

#sql iterator SPJ_DAMA_GETANALYSIS_Cursor1 ( float );

public class SPJ_DAMA_GETANALYSIS
{
public static void sPJ_DAMA_GETANALYSIS ( ResultSet[] rs ) throws
SQLException, Exception
{
SPJ_DAMA_GETANALYSIS_Cursor1 cursor1 = null;
#sql cursor1 =
{
select
COALESCE(fda.fld_cfd_parts_pr, -2147483648) as partspr,
from
tbl_formaldamageass fda
where
ccl.fld_ccl_id_pk = 1
fetch first 1 row only
for read only
};
rs[0] = cursor1.getResultSet();
}
}
</code>
If we run the stored procedure on the server 1, it returns 342.16

Now, we go to the code on the server 2. We call our data placeholders
bizness objects (yes I know it's supposed to be business... we just
call them bizness).
<code>
package ubench.claim.damageanalysis.bizness;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import framework.bean.DataAccess;

public class DamageAnalysisBizObj {
protected float totalPrice = 0;

public void read() throws Throwable {
framework.bean.DataAccess ds = null;
CallableStatement cs = null;

try {
ds = AppEnvUbclaims.getInstance().getDataAccess();
cs = ds.executeStoredProc("SPJ_DAMA_GETANALYSIS");
ResultSet rs = cs.getResultSet();

if (rs.next()) {
totalPrice = rs.getFloat(11);
}

finally {
cs.close();
ds.finish();
}
}
}
</code>
Keep in mind this is a code snip, the stored procedure is one A4 long,
and we read all results from it into the object.
If we do a System.out.println from totalprice here, it is 342.
We tried reading it as double, same result: 342.
We tried placing a float in the bizobj, and if we read it, it remains
a float.
As far as we can see, no conversion happens... and yet the result
isn't expected.
I hope this helps towards a solution of the problem, thanks in
advance.

Koen

 

[Sudsy]

Koen wrote:

import framework.bean.DataAccess;

ds = AppEnvUbclaims.getInstance().getDataAccess();
cs = ds.executeStoredProc("SPJ_DAMA_GETANALYSIS");

A couple of clarifications are necessary here. First off, I'm not
familiar with the framework.bean.DataAccess package. Secondly, if
you're actually executing a stored procedure then you're comparing
apples and oranges. Why don't you try calling the stored procedure
on server 1 (the DB/2 host)? It could be as simple as the wrong
return type from the stored procedure...