Inserting wrong data if data type is Double and 0(zero) is the last digit

• Key: TORQUE-53
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Major
• Assignee: CG Monroe
• Reporter: Sudhakar Pandey
• Component/s: Runtime, Village
• Affects Version/s: 3.2
• Fix Version/s: 3.3-RC3

Description

Problem: Torque is inserting wrong data in the database for Double type.

Database description
~~~~~~~~~~~~~~~~~
Database: Oracle 10g
Oracle JDBC version: 10.2.0.1.0
Table Name: Bank_Account
Column Name: Current_Balance
Column dataType: NUMBER(15,4)

When I am trying to enter a value new Double(1234567890) it insert/update 12.3456 in the database. This happens only if the number of digits are more than 7 and last digit is 0(zero).

I have tried updating using directly PreparedStatement and it just worked fine. Problem comes only with Torque.

Following is the program I have used for varification:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
<Code>
............
public static void main(String argv[]) {
TorqueInitializer.init();
Connection db_connection = null;

db_connection = Common.getConnection();

if (db_connection == null) {
System.out.println("Unable to get dbConnection");
}

try {
BankAccount bankAccount = showCurrentBalance(db_connection);
long value = 12345l;
for (int i = 5; i < 11; i++, value = ((value * 10) + i)) {
System.out.println("Value=" + value);
System.out.print("<Before update> ");
bankAccount = showCurrentBalance(db_connection);

if (updateUsingPrepStat)
updateCurrentBalanceUsingPStat(db_connection, new Double(
value * 10));
else
updateCurrentBalanceUsingTorque(bankAccount, db_connection,
new Double(value * 10));
System.out.print("<After update> ");
bankAccount = showCurrentBalance(db_connection);
}
} catch (Exception e) {
e.printStackTrace();
} finally {

if (db_connection != null)
try {
db_connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

public static BankAccount showCurrentBalance(Connection db_connection) {
try {
Criteria c = new Criteria();
c.add(BankAccountPeer.BANK_ACCOUNT_ID, new Long(1523764));
List list = BankAccountPeer.doSelect(c, db_connection);
BankAccount bankAccount = (BankAccount) list.get(0);
System.out.println("Current account balance: "
+ bankAccount.getCurrentBalance());
return bankAccount;
} catch (TorqueException e) {
e.printStackTrace();
}
return null;
}

public static void updateCurrentBalanceUsingTorque(BankAccount bankAccount,
Connection db_connection, Double value) throws Exception {
bankAccount.setCurrentBalance(value);
bankAccount.save(db_connection, "ibdv70");
}
............
</Code

Comments

[Sudhakar Pandey]

One more observation!

If we try to enter new Double(1234567890.1234) even Torque also inserts the correct data. It means problem occurs for following case:
1. Number-of-digits is more then seven (in my case where column is of size (15,4))
2. Last digit is zero.
3. No decimal and digits after decimal.

We are facing this problem in our production environment and hence any solution or work around will be helpful.

I am counting on you folks:).
Thanks alot

[CG Monroe]

I reproduced a similar error with MS SQL and a different JDBC driver. In my case, the update did not happen at all. Once you got to 12345670.0000 and greater, the value stayed the same (1234560.0000). Adding in a fraction caused it to work.

So, I started digging thru the code to figure it out. After creating a Village jar with some debug statements, I finally tracked it down to the following:

Village uses the actual DB Meta data to determine the SQL Column type and object value to use in populating the prepared statement. With an SQL Column type of Numeric, it maps to a BigDecimal. In setting the prepared statement parameters (in Value.setPreparedStatementValue), it has to convert the Double value to a BigDecimal. This is done in the Value.asBigDecimal() method using the equivalent of:

new BigDecimal( valueObject.asString() )

7 digits is the point at which a Double object's toString() converts to Exponential notation. There are several Java bug entries about problems with BigDecimal's conversion from text to object. I suspect that the ending in "0" is one of them, but did not find an exact match in a VERY brief scan.

If you convert a Double to a BigDecimal using the BigDecimal( Double) constructor it works fine. The Village Value.asBigDecimal() method should probably be patched to handle this case (and lots more I'm sure).

But in the meantime, I'd suggest having your schema define the field exactly as it is in the DB, e.g.:

    <column name="balance" type="NUMERIC" size="15" scale="4"/>

Then convert your Double values to BigDecimal with the "safe" constructor before saving them.

(You may also try an updated Java VM version, a lot of bugs related to the seemed to have been fixed in newer JVMs (Mine is 1.5.0_06...).

Bottom line is, not sure if this is a Torque or Village bug, or really a Java VM bug. I'll leave that for others to determine.

[Sudhakar Pandey]

Thanks Monroe for your valuable reply. It really helped us alot in understanding the problem.

As I have already mention in my second comment, we are facing this issue only if the number is greater then seven digit, ends with zero and without non-zero digits after decimal. For example we are facing issue with following type of numbers:
123456780
123456780.00

Torque is behaving properly for following numbers(number not ending with zero or has some non-zero digit after decimal; even when number is greater then 7 digit):
1234567890.0001
123456789
1234567890.1234

We are using JVM 1.5.0. I have testing the BigDecimal to Double conversion and it seems to be working fine. Check the following code and its output:
<code>
......
public static void main(String[] args) {
// TODO Auto-generated method stub
Double value = new Double(1234567890);
System.out.println("Double value = " + value);
System.out.println("String value = " + value.toString());
System.out.println("Big Decimal value =" + new BigDecimal(value.toString()));
}
......
</code>

<output>
Double value = 1.23456789E9
String value = 1.23456789E9
Big Decimal value =1.23456789E+9
</output>

Passing BigDecimal, in place of Double, as parameter, seems to be not possible in our case, it will require huge-huge code change and we have yet to check whether it will solve over problem or not.

A quick local fix would be to get the Village source from:

      http://www.softwareforge.de/releases/village/distributions/

Then modify the Value.asBigDecimal() method so that if the valueObject is an instance of Double, it calls the correct conversion method. Then use this modified local Village jar.

[CG Monroe]

Added better conversion handling in Village for BigDecimal values. Double and Float types will be converted using appropriate constructors rather than the somewhat buggy BigDecimal(String) constructor.

[CG Monroe]

No comments on committed fix.. closing issue.