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
