The Quietly Failing INSERT (or Whyohwhyohwhybase)
Interesting problem uncovered today. We're using a fairly standard Spring JDBC template to insert some entities into Sybase:
The number of rows inserted into the database table was not the same as the number of times the SQL was executed. No exceptions thrown, so no key or constraint violations.
It turns out that an insert of a value into a NUMERIC column which requires rounding fails silently on Sybase. For example, inserting 1.23456 into a NUMERIC(6, 4) column would fail but not throw any corresponding SQLException from JDBC.
Aside from ensuring the column type is precise enough and rounding BigDecimals before insertion, we've now coded defensively by actually verifying that an insert returns a value of 1:
Will I be able to resist the urge to check the return value from inserts in the future?
int rows = jdbcTemplate.update("INSERT INTO my_table ...", values, types);
The number of rows inserted into the database table was not the same as the number of times the SQL was executed. No exceptions thrown, so no key or constraint violations.
It turns out that an insert of a value into a NUMERIC column which requires rounding fails silently on Sybase. For example, inserting 1.23456 into a NUMERIC(6, 4) column would fail but not throw any corresponding SQLException from JDBC.
Aside from ensuring the column type is precise enough and rounding BigDecimals before insertion, we've now coded defensively by actually verifying that an insert returns a value of 1:
if(rows == 0) {
log.error("Unable to insert entity", ...);
}
Will I be able to resist the urge to check the return value from inserts in the future?
