Driver version
12.2.0
SQL Server version
Microsoft SQL Server 2019 (RTM-GDR) (KB5021125) - 15.0.2101.7 (X64) Jan 23 2023 13:08:05 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise LTSC 2019 10.0 (Build 17763: ) (Hypervisor)
Client Operating System
Windows 10
JAVA/JVM version
1.8.0
Table schema
n/a
Problem description
The precision for BigDecimals < 0 is always 1 higher than it should.
For example a BigDecimal less than 0 with a precision of 38: 0.98432319763138435186412316842316874322
results: decimal(39,38)
This caused an error:
SQL Error: 2717, SQLState: S0001
Die Größe (39) von "decimal" (Typ) überschreitet den zulässigen maximalen Wert (38)
But the value fits to a decimal(38,38). Tested with the following sql:
DECLARE @numberVal decimal(38,38);
SET @numberVal = 0.98432319763138435186412316842316874322;
On the other side a BigDecimal biger than 1 and a precision of 38: 1.9843231976313843518641231684231687432
results: decimal(38,37)
So the problem only occurs for BigDecimals < 0.
#####################
Quick test class:
import java.math.BigDecimal;
import java.math.MathContext;
import java.math.RoundingMode;
class Scratch
{
public static BigDecimal roundIfNeeded( final BigDecimal toRound )
{
BigDecimal rounded = toRound;
if ( rounded != null )
{
if ( rounded.precision() 38 )
{
rounded = rounded.round( new MathContext( 38, RoundingMode.HALF_UP ) );
}
if ( rounded.scale() 38 )
{
rounded = rounded.setScale( 38, RoundingMode.HALF_UP );
}
}
return rounded;
}
public static void main( String[] args )
{
BigDecimal number = new BigDecimal( 0.984323197631384351864123168423168743216874321684132168451946546357451684134654654561465 );
System.out.println(number.toPlainString() + scale + number.scale() + prec+ number.precision());
number=roundIfNeeded( number );
System.out.println(number.toPlainString() + scale + number.scale() + prec+ number.precision());
[START] Copied snipped from httpsgithub.commicrosoftmssql-jdbcpull2051files
String[] plainValueArray = number.abs().toPlainString().split(.);
System.out.println(number.abs().toPlainString());
String sqlTypeDef = decimal + ( +
Precision
(plainValueArray.length == 2 plainValueArray[0].length()
+ plainValueArray[1].length() plainValueArray[0].length())
+ , +
Scale
(plainValueArray.length == 2 plainValueArray[1].length() 0) + );
[END] Copied snipped from httpsgithub.commicrosoftmssql-jdbcpull2051files
System.out.println(sqlTypeDef);
System.out.println(++++++++++++++++++++++++++++);
number = new BigDecimal( 1.984323197631384351864123168423168743216874321684132168451946546357451684134654654561465 );
System.out.println(number.toPlainString() + scale + number.scale() + prec+ number.precision());
number=roundIfNeeded( number );
System.out.println(number.toPlainString() + scale + number.scale() + prec+ number.precision());
[START] Copied snipped from httpsgithub.commicrosoftmssql-jdbcpull2051files
plainValueArray = number.abs().toPlainString().split(.);
System.out.println(number.abs().toPlainString());
sqlTypeDef = decimal + ( +
Precision
(plainValueArray.length == 2 plainValueArray[0].length()
+ plainValueArray[1].length() plainValueArray[0].length())
+ , +
Scale
(plainValueArray.length == 2 plainValueArray[1].length() 0) + );
[END] Copied snipped from httpsgithub.commicrosoftmssql-jdbcpull2051files
System.out.println(sqlTypeDef);
}
}
Output:
0.984323197631384351864123168423168743216874321684132168451946546357451684134654654561465| scale:87| prec:87
0.98432319763138435186412316842316874322| scale:38| prec:38
0.98432319763138435186412316842316874322
decimal(39,38)
++++++++++++++++++++++++++++
1.984323197631384351864123168423168743216874321684132168451946546357451684134654654561465| scale:87| prec:88
1.9843231976313843518641231684231687432| scale:37| prec:38
1.9843231976313843518641231684231687432
decimal(38,37)
Driver version
12.2.0
SQL Server version
Microsoft SQL Server 2019 (RTM-GDR) (KB5021125) - 15.0.2101.7 (X64) Jan 23 2023 13:08:05 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise LTSC 2019 10.0 (Build 17763: ) (Hypervisor)
Client Operating System
Windows 10
JAVA/JVM version
1.8.0
Table schema
n/a
Problem description
The precision for BigDecimals < 0 is always 1 higher than it should.
For example a BigDecimal less than 0 with a precision of 38:
0.98432319763138435186412316842316874322results: decimal(39,38)
This caused an error:
But the value fits to a decimal(38,38). Tested with the following sql:
On the other side a BigDecimal biger than 1 and a precision of 38:
1.9843231976313843518641231684231687432results: decimal(38,37)
So the problem only occurs for BigDecimals < 0.
#####################
Quick test class:
Output: