Problem with decimal precision

[fabio_patricio <fa...@terra.com.br>]

  Hi,

  I have a field decimal(10,2) where I insert "32,78", but when I go
to look at in the table the inserted value was "32,77", alguem which
the problem?

  My code:

    try {
        // Prepare a statement to insert a record
        String sql = "INSERT INTO TESTE_DECIMAL (VALOR_DECIMAL,
VALOR_DOUBLE) VALUES(?,?)";
        PreparedStatement pstmt = conexao.prepareStatement(sql);
    
        // Insert 10 rows
        for (int i=0; i<10; i++) {
            // Set the value
            pstmt.setDouble(1, new Double("32.78").doubleValue());
            pstmt.setDouble(2, new Double("2324234.69").doubleValue());
    
            // Insert the row
            pstmt.executeUpdate();
        }
        conexao.commit();
    } catch (SQLException e) {
        e.printStackTrace();
    }


  Best Regards.

  Fabio Patricio

Re: Problem with decimal precision

[Jeffrey Lichtman <sw...@rcn.com>]

>   I have a field decimal(10,2) where I insert "32,78", but when I go
>to look at in the table the inserted value was "32,77", alguem which
>the problem?

This is probably caused by the fact that you are inserting the value as a 
double. Doubles are not exact values - my guess is that 32.78 is being 
converted to 32.779999... (or something like that) and then being truncated 
to 32.77.

To insert into a decimal column, it's better to use the method 
setBigDecimal() with the class java.math.BigDecimal.


                        -        Jeff Lichtman
                                 swazoo@rcn.com
                                 Check out Swazoo Koolak's Web Jukebox at
                                 http://swazoo.com/ 

Re: Problem with decimal precision

[Daniel John Debrunner <dj...@debrunners.com>]

fabio_patricio wrote:
>   Hi,
> 
>   I have a field decimal(10,2) where I insert "32,78", but when I go
> to look at in the table the inserted value was "32,77", alguem which
> the problem?
> 

This is a bug Derby-123 and is fixed in the development trunk.

http://issues.apache.org/jira/browse/DERBY-123

Though as Jeff said, using doubles and DECIMAL may lead to unexpected
results due to mixing of imprecise and precise values.


>   My code:

>             pstmt.setDouble(1, new Double("32.78").doubleValue());

You can also use setString() on most types in Derby, thus the above line
could have been

pstmt.setString(1, "32.78");

That will insert the correct value in Derby's current release 10.0.2.1

Dan.