Bug #19615 Passing BigDecimal to setObject(int,Object,int) loses accuracy
Submitted: 8 May 2006 14:56 Modified: 26 Jul 2006 18:17
Reporter: Colin McFarlane Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:3.1.12 OS:Any (All)
Assigned to: CPU Architecture:Any

[8 May 2006 14:56] Colin McFarlane
Description:
When I pass a BigDecimal object to the 3 argument version of the setObject method it removes everything after the decimal point. If I use the 2 argument method then the accuracy is retained. Note when I use the 3 argument method the third argument is 3 representing the SQL type DECIMAL.

Note that the behaviour of the 3.0.14 driver did not truncate the values, but I'm unsure which version between 3.0.14 and 3.1.12 actually introduced the change.

I have attached a java program that reproduces the error. 
You will need to have a table created as follows to run the test
CREATE TABLE `test` (
  `val` decimal(19,12) NOT NULL
)

The output from the program is:

num:java.math.BigDecimal type:3

The results in the database table are:

select * from test

1.000000000000
1.234567000000

How to repeat:
package testjdbc;
import java.sql.*;
import javax.sql.*;

public class Main {
    
    /** Creates a new instance of Main */
    public Main() {
    }
    
    
    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        Connection src = null,
                dest = null;
        try {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");           
            String url = "jdbc:jtds:sqlserver://localhost:1433;DatabaseName=master";
            src = DriverManager.getConnection(url,"user","password");  

            ResultSet rs = src.createStatement().executeQuery("select convert(decimal(19,12), 1.234567) as num");
            
            ResultSetMetaData rsmd = rs.getMetaData();

            rs.next();
            System.out.println(rsmd.getColumnName(1) + ":" + rsmd.getColumnClassName(1) + " type:" + rsmd.getColumnType(1));
            
            Class.forName("com.mysql.jdbc.Driver");           
            url = "jdbc:mysql://localhost:3306/test";
            dest = DriverManager.getConnection(url,"user","password");
                        
            PreparedStatement ps = dest.prepareStatement("insert into test (val) values (?)"); 
            ps.setObject(1, rs.getObject(1), rsmd.getColumnType(1));
            ps.execute();
            ps.setObject(1, rs.getObject(1));
            ps.execute();

        } catch (Exception e) {
            System.err.println(e);
            e.printStackTrace();
        }

    }

    
}

Suggested fix:
I think that the 3 argument method should not lose accuracy if the 3rd argument is a DECIMAL sql type.
[9 May 2006 14:20] Mark Matthews
Your example shows usage of a SQL Server driver. Does the error actually happen with MySQL or with SQL Server, or is your example code a cut-and-paste error?
[9 May 2006 14:24] Mark Matthews
Never mind, I see what's going on in your testcase.
[9 May 2006 15:56] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6158