db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Numeric Field Precision on JavaDB
Date Tue, 20 Jan 2009 20:56:17 GMT
Hi Carlos,

As Michael says, you risk losing precision when you switch between 
inexact and exact numeric datatypes. According to the JDBC spec, 
BigDecimal is the Java datatype which corresponds with the SQL 
NUMERIC/DECIMAL datatype. The following scrap of Java code

        System.out.println( "11.11F as double " + (new BigDecimal( 
11.11F)).doubleValue() );
        System.out.println( "11.11F as float " + (new BigDecimal( 
11.11F)).floatValue() );
        System.out.println( "11.11F as string " + (new BigDecimal( 
11.11F)) );

produces the following output:

11.11F as double 11.109999656677246
11.11F as float 11.11
11.11F as string 11.10999965667724609375

In contrast, the following scrap of Java code

        System.out.println( "'11.11' as double " + (new BigDecimal( 
"11.11")).doubleValue() );
        System.out.println( "'11.11' as float " + (new BigDecimal( 
"11.11")).floatValue() );
        System.out.println( "'11.11' as string " + (new BigDecimal( 
"11.11")) );

produces the following output:

'11.11' as double 11.11
'11.11' as float 11.11
'11.11' as string 11.11

I recommend that you use the PreparedStatement.setBigDecimal() and 
ResultSet.getBigDecimal() methods when storing and retrieving exact 
numeric values.

Hope this helps,
-Rick

Carlos Eduardo Santin wrote:
> Hi everybody,
>  
> Have someone had a problem like this one before?
>  
> If I have a simple table like:
>  
> create table test(id integer, percent numeric(5,2))
>  
> And I use a java code to insert data into this table:
>  
> //////////////////////////////////////////////////////////////////////////////// 
>
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.SQLException;
> import java.sql.PreparedStatement;
>  
> public class TestJavaDB{
>  
>  public TestJavaDB(){
>   try{
>    executeTest();
>   } catch (SQLException e){
>    e.printStackTrace();
>   }
>  }
>  
>  public void executeTest() throws SQLException{
>   Connection conn = 
> DriverManager.getConnection("jdbc:derby:C:/Derby20090108/data/", 
> "root", "root");
>   
>   Float fltValue = 11.11F;
>  
>   String strQuery = "INSERT INTO test VALUES (?, ?)";
>  
>   PreparedStatement pstmt = conn.prepareStatement(strQuery);
>   pstmt.setInt(1,new Integer(2));
>   pstmt.setFloat(2,fltValue);
>  
>   pstmt.executeUpdate();
>  
>   pstmt.close();
>   conn.close();
>  }
>  
>  public static void main(String[] args){
>   new TestJavaDB();
>  }
> }
> ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////

>
>  
> The value inserted into the field percent in the database is 11.10 and 
> not 11.11 as it would be.
>  
> But if I insert this data directly in the database using a script:
>  
> INSERT INTO test VALUES (1, 11.11);
>  
> The value appears correctly.
>  
> Is there any bug related to the derby jdbc library??
>  
>  
> Thanks in advance,
> Carlos
>  


Mime
View raw message