Hi.
It seems I have found a bug with the FIXED column.
I am using SapDB 7.3.0.25 on RedHat 7.3.
I have created this table :
CREATE TABLE Test (
ID INT NOT NULL,
Amount FIXED(10, 2) NOT NULL
)
INSERT INTO Test VALUES (1, 5.5);
INSERT INTO Test VALUES (1, 4.3);
INSERT INTO Test VALUES (2, 4.3);
INSERT INTO Test VALUES (2, 2.45);
1 / In dbmcli :
dbmcli on KASSA>sql_execute SELECT ID, SUM(Amount) FROM Test GROUP BY ID
OK
END
1;9
2;6
The sum is not correct. It should be 9.80 and 6.75
2 / I tried with the JDBC driver (7.3.0.29a) I got :
9.80000000000000000000000000000000000000
6.75000000000000000000000000000000000000
which is not correct (the scale should be 2).
If I tried to get the scale from the metadata I got 0 !!!
I attached the Java file.
Laurent
import java.sql.*;
/**
* Before executing this program, create a table Test like this :
* CREATE TABLE Test (
* ID INTEGER NOT NULL,
* Amount FIXED NOT NULL
* );
* INSERT INTO Test VALUES (1, 5.5);
* INSERT INTO Test VALUES (1, 4.3);
* INSERT INTO Test VALUES (2, 4.3);
* INSERT INTO Test VALUES (2, 2.45);
*/
public class BugSUMFixed {
public static void main(String[] args)
throws SQLException, ClassNotFoundException
{
if (args.length != 4) {
System.err.println("usage: java BugSUMFixed hostname database username password");
System.exit(1);
}
new BugSUMFixed(args[0], args[1], args[2], args[3]).run();
}
private BugSUMFixed(String hostname,
String database,
String username,
String password)
throws SQLException, ClassNotFoundException
{
Class.forName("com.sap.dbtech.jdbc.DriverSapDB");
connection = DriverManager.getConnection("jdbc:sapdb://" + hostname + "/" + database,
username,
password);
// Class.forName("transbase.jdbc.Driver");
// connection = DriverManager.getConnection("jdbc:transbase://" + hostname + "/" + database,
// username,
// password);
}
private void run() throws SQLException {
Statement select;
ResultSet rset;
select = connection.createStatement();
rset = select.executeQuery("SELECT ID, SUM(Amount) FROM Test GROUP BY ID");
while (rset.next()) {
final int pos = 2;
System.out.println(" Amount : " + rset.getBigDecimal(pos));
int scale = rset.getMetaData().getScale(pos);
System.out.print("scale : " + scale);
System.out.println(" Amount : " + rset.getBigDecimal(pos, scale));
}
rset.close();
select.close();
}
// ----------------------------------------------------------------------
private final Connection connection;
}
