Question
Unwanted auto rounding occur when a decimal column add or subtract 0 without scale (execute by PrepareStatement )
-
language: java
-
driver: sqljdbc4: 4.0
<!-- https://mvnrepository.com/artifact/com.microsoft.sqlserver/sqljdbc4 -->
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>sqljdbc4</artifactId>
<version>4.0</version>
</dependency>
- database server info:
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2) (VM)
test data:
-- create
CREATE TABLE BILL (
id int,
amount decimal(19,4) --amount of money
);
-- insert
INSERT INTO BILL(id,amount) VALUES (1, 100.99);
INSERT INTO BILL(id,amount) VALUES (2, 100.99);
-- fetch
SELECT * FROM BILL;
update bill set amount = amount + 0.00 where id = 100
SELECT * FROM BILL;
GO
Output:
(1 rows affected)
(1 rows affected)
id amount
----------- ---------------------
1 100.9900
2 100.9900
(2 rows affected)
(0 rows affected)
id amount
----------- ---------------------
1 100.9900
2 100.9900
(2 rows affected)
reproduce case:
add 0 without scale to a decimal column will lead to auto rounding
String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String url = "jdbc:sqlserver://ip:1433;databaseName=db1";
String userName = "user";
String pwd = "password";
try{
Class.forName(driver);
}catch (ClassNotFoundException e){
e.printStackTrace();
}
Connection con = null;
try{
con = DriverManager.getConnection(url,userName,pwd);
// 0 and 0.00 should be the same number,but the results are different when added to a decimal column
PreparedStatement ps = con.prepareStatement("update bill set amount = amount + ? where id = ?");
ps.setBigDecimal(1,BigDecimal.ZERO); // zero without scale
ps.setBigDecimal(2,1);
ps.execute();
ps.setBigDecimal(1,new BigDecimal("0.00")); // zero with scale 2
ps.setBigDecimal(2,2);
ps.execute();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}Output:
the first record is auto rounded, and the second record is not affected
id amount
----------- ---------------------
1 101.0000
2 100.9900
(2 rows affected)
Relevant Issues and Pull Requests
#1021 ,#2189,#942
fixed by #2248
property name calcBigDecimalScale changed to calcBigDecimalPrecision by #2269
update the driver to version higher than 12.5.0 preview release, then add a property calcBigDecimalScale = truecalcBigDecimalPrecision = truein the db connection url, then the problem flies away!
String url = "jdbc:sqlserver://{host}:{port};databaseName={dbName};calcBigDecimalPrecision=true;" 
Question
Unwanted auto rounding occur when a decimal column add or subtract 0 without scale (execute by PrepareStatement )
language: java
driver: sqljdbc4: 4.0
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2) (VM)
test data:
-- create
Output:
reproduce case:
add 0 without scale to a decimal column will lead to auto rounding
Relevant Issues and Pull Requests
#1021 ,#2189,#942
fixed by #2248
property name
calcBigDecimalScalechanged tocalcBigDecimalPrecisionby #2269update the driver to version higher than 12.5.0 preview release, then add a property
calcBigDecimalScale = truecalcBigDecimalPrecision = truein the db connection url, then the problem flies away!