Muhammad Catubig - 1 year ago 48

SQL Question

I am trying to convert a double value e.g.

`12.55`

`BigDecimal`

`BigDecimal unitPrice = new BigDecimal(product.getUnitPrice());`

As a result, I got something like this

`12.550000000000000710542735760100185871124267578125`

`unitPrice.setScale(2, RoundingMode.HALF_EVEN)`

and got a result like this

`12.55`

`addProduct.setBigDecimal(4, unitPrice.setScale(2, RoundingMode.HALF_EVEN));`

So when I run it, I got this SQL Error:

`Data truncation: Out of range value for column 'price_per_unit' at row 2`

I know that the problem was in this code:

`addProduct.setBigDecimal(4, unitPrice.setScale(2, RoundingMode.HALF_EVEN));`

as I have debugged it at this line of code, the value was

`12.550000000000000710542735760100185871124267578125`

The column's data type in MySQL that I have set was

`DECIMAL(3,2)`

How come it is not working even when I rounded it off? I have tried searching for a solution and cannot find anything relevant. How do you really set the decimal properly? I assume that you know what I'm trying to do. Thanks in advance.

Answer

`Decimal (3,2)`

means the number has a total of three digits, one to the left and two to the right. `12.55`

does not fit into this.
Was you intention to store three to the left? It would be `decimal(5,2)`

Source (Stackoverflow)