Saving zero BigDecimal with big scale
600238Sep 27 2007 — edited Sep 27 2007I’m trying to save BigDecimal zero value with scale 7 into DB and the result of my insertion equals to 7.0000000 instead of 0.0000000 .
1. Create table (Using PL/SQL Developer):
create table TESTTABLE (field number(8,7))
2. insert zero value into the table:
BigDecimal bigDecimal = new BigDecimal(0);
bigDecimal = bigDecimal.setScale(7, RoundingMode.HALF_DOWN);
Connection con = DriverManager.getConnection(URL, LOGIN, PASSWORD);
PreparedStatement ps = con.prepareStatement("INSERT INTO TESTTABLE (FIELD) VALUES (?)");
ps.setBigDecimal(1, value);
ps.executeUpdate();
3. Check the result (Using PL/SQL Developer):
select * from testable t
the result is: 7.0000000 instead of 0.0000000 !
--------------------------------------------
This happens only if I use scale more than 6 and Oracle DB.
Oracle JDBC Driver version - 10.1.0.2.0,
Oracle 10g
Full java code
package com.xxx.xxx;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class OracleScaleTest {
private static final String LOGIN = "login";
private static final String URL = "jdbc:oracle:thin:@//xxx.xxx:1525/XXX";
private static final String PASSWORD = "password";
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.OracleDriver");
Connection con = DriverManager.getConnection(URL, LOGIN, PASSWORD);
PreparedStatement ps = con.prepareStatement("INSERT INTO TESTTABLE (FIELD) VALUES (?)");
BigDecimal zeroWithScale7 = new BigDecimal(0).setScale(7, RoundingMode.HALF_DOWN);
ps.setBigDecimal(1, zeroWithScale7);
ps.executeUpdate();
con.close();
}
}