Bug #21990 DECIMAL datatype PreparedStatement Java JDBC

• Submitted: 4 Sep 2006 10:03
• Modified: 13 Sep 2006 7:30
• Reporter: Stefan Duffner
• Status: Not a Bug
• Category: Connector / J
• Severity: S2 (Serious)
• Version: 3.1.3, 5.0.3
• OS: Windows (Windows2000)
• CPU Architecture: Any

[4 Sep 2006 10:03] Stefan Duffner

Description:
I am using Mysql 5.0.24 with jdbc Driver 3.1.3 and 5.0.3 and I have a really strange problem with the datatype DECIMAL. I created the following table:

create table TEST (OID CHAR(15) NOT NULL,HEIGHT DECIMAL(20,5));

Then I make an insert statement via PreparedStatement (see code example) and set the HEIGHT column via PreparedStatement.setDouble(), which is working for the value 100.24, but not for the value 81.65. I was checking different values, but only as I know 81.65 is not working.

PreparedStatement.setFloat and PreparedStatement.setBigDecimal are also not working. But PreparedStatement.setString works and if I enter the HEIGHT directly in the string (insert into TEST (OID, HEIGHT) values ('1', 81.65)) this is working, too.

How to repeat:
use this code example:

Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            //&jdbcCompliantTruncation=false
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database?user=user&password=password");

            PreparedStatement ps = conn.prepareStatement("insert into TEST (OID, HEIGHT) values (?, ?)");
            ps.setString(1,"1");
            ps.setDouble(2, 81.65d);
            ps.executeUpdate();
        } catch (InstantiationException e) {
            e.printStackTrace();            
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }  finally {
            if(conn != null) {
                PreparedStatement ps = conn.prepareStatement("delete from MOTIF where OID = '1'");
                ps.executeUpdate();
                conn.close();
            }
        }

Suggested fix:
- enter the value via PreparedStatement.setString
- enter the value directly in the sql query
- add parameter jdbcCompliantTruncation=false to connection string

[13 Sep 2006 7:30] Tonci Grgin

Hi Stefan and thank you for your problem report. It do sound weird and I was unable to repeat it (not with 81.65 nor with any other value):
  this.pstmt.setString(1, "1");  		
  this.pstmt.setDouble(2, 81.65);  		
  System.out.println("Query to be executed: "+pstmt.toString());
  assertEquals(1, this.pstmt.executeUpdate());
  this.pstmt.clearParameters();
I presume what's happening is that you're using double variable incremented in steps but double arithmetics is far from perfect as you can se from my next example:
    		double i = 0.00;
    		String s = "";
    		while (i < 150.00) {
    			s = Double.toString(i*10);
        		this.pstmt.setString(1, s);  		
        		this.pstmt.setDouble(2, i);  		
        		System.out.println("Query to be executed: "+pstmt.toString());
        		assertEquals(1, this.pstmt.executeUpdate());
        		this.pstmt.clearParameters();
				i = i + 0.01;
			}
...
Query to be executed: com.mysql.jdbc.ServerPreparedStatement[1] - insert into test_table (OID,HEIGHT) values ('0.5', 0.05);
Query to be executed: com.mysql.jdbc.ServerPreparedStatement[1] - insert into test_table (OID,HEIGHT) values ('0.6000000000000001', 0.060000000000000005);
...
This is in no way connector's or server problem. Putting jdbcCompliantTruncation=false into connection string supresses reported warning as it is it's job, so nothing weird there.

Environment:
  MySQL server 5.0.24 BK on WinXP Pro SP2 localhost
  connector/J 5.0 SVN
  JDK 1.5.07