Long values are incorrectly stored on Oracle when using SimpleJdbcInsert, instead of SimpleJdbcTemplate [SPR-6912]

[spring-projects-issues]

Jacques-Henri Berthemet opened SPR-6912 and commented

If connected database is Oracle it is not possible to accurately store long values larger than 9999999999999998 or lesser than -9999999999999998 using SimpleJdbcInsert. Inserting wider values would end in a loss of precision (9999999999999999 will be inserted as 10000000000000000).

Here a table creation script :
create table Test (
CustomerId VARCHAR2(16) not null,
Id NUMERIC(19),
constraint PK_Test primary key (Id)
);

Here is a sample Java code to reproduce the issue :
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("oracle.jdbc.driver.OracleDriver");
dataSource.setUrl("jdbc:oracle:thin:sa/sa@oracleHost:1521:XE");
dataSource.setUsername("sa");
dataSource.setPassword("sa");

    SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(dataSource);
    Map<String, Object> parameters = new HashMap<String, Object>();

    parameters.put("customerid", "1");
    parameters.put("id", -9999999999999999L);

    simpleJdbcInsert.setTableName("Test");
    simpleJdbcInsert.execute(parameters);

Here is the result in DB :
SQL> select * from Test;

CUSTOMERID ID

---

1 -10000000000000000

Now a sample code that works with the same DriverManagerDataSource :

SimpleJdbcTemplate simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
List<Object[]> data = new ArrayList<Object[]>();
data.add(new Object[]{"3",-9999999999999999L});
simpleJdbcTemplate.batchUpdate("insert into Test (customerid, id) values (?,?)", data);

SQL> select * from test;

CUSTOMERID ID

---

1 -10000000000000000
3 -9999999999999999

FYI There no such issue with MS-SQL Server 2000.

---

Affects: 3.0 GA, 3.0.1

[spring-projects-issues]

Juergen Hoeller commented

Thomas, could you please have a look at this for 3.0.2?

Juergen

[spring-projects-issues]

Thomas Risberg commented

This is interesting and seems to depend on the behavior of the Oracle JDBC driver.

You'd think the following would be equivalent:

ps.setObject(2, -9999999999999999L, Types.DECIMAL);
ps.setObject(2, -9999999999999999L, Types.NUMERIC);
ps.setObject(2, new BigDecimal(-9999999999999999L), Types.DECIMAL);
ps.setLong(2, -9999999999999999L);

but they are not. The first one ends up as -10000000000000000 in the database.

This is the reason you are seeing this result with the SimpleJdbcInsert. We query the metadata and that column comes up as Types.DECIMAL so then Spring issues the call "ps.setObject(2, -9999999999999999L, Types.DECIMAL)" which gives the erroneous result.

Wrapping the Long value in a BigDecimal works fine, so this should work:

SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(dataSource);
Map<String, Object> parameters = new HashMap<String, Object>();
parameters.put("customerid", "1");
parameters.put("id", new BigDecimal(-9999999999999999L));
simpleJdbcInsert.setTableName("Test");
simpleJdbcInsert.execute(parameters);

Turning off metadata lookup works too:

SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(dataSource);
simpleJdbcInsert.withoutTableColumnMetaDataAccess().usingColumns("customerid", "id");
Map<String, Object> parameters = new HashMap<String, Object>();
parameters.put("customerid", "1");
parameters.put("id", -9999999999999999L);
simpleJdbcInsert.setTableName("Test");
simpleJdbcInsert.execute(parameters);

[spring-projects-issues]

Jacques-Henri Berthemet commented

Do you think you will fix this behavior ?

We might either change all our SimpleJdbcInsert to SimpleJdbcTemplate or apply one or your fixes. First fix is simple but our code is very generic at this level, it could involve inspecting the type of value and building a new BigDecimal when we detect a Long, it is not good for performance.

Second one seems better, what are the consequences of turning off metadata lookups ?

[spring-projects-issues]

Thomas Risberg commented

I'm considering adding an automatic override of the metadata using Types.NUMERIC for anything reported as NUMBER with a type of Types.DECIMAL where the number of decimals is zero. That would fix this issue and seems safe for any other use cases as well.

If you have an Oracle support contract the you should file a bug against the JDBC driver. I would expect the following method calls to generate the same results:

ps.setObject(2, -9999999999999999L, Types.DECIMAL);
ps.setObject(2, -9999999999999999L, Types.NUMERIC);
ps.setObject(2, new BigDecimal(-9999999999999999L), Types.DECIMAL);
ps.setLong(2, -9999999999999999L);

Right now they do not.

[spring-projects-issues]

Thomas Risberg commented

I've added this override to use Types.NUMERIC which should work around this problem. It will be part of the 3.0.2 release.

[spring-projects-issues]

Jacques-Henri Berthemet commented

I'm having difficulties finding out if we have an Oracle Support Contract. Do you think it is worth opening a bug or the current fix is acceptable ?