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.
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.