Hi!
I have a problem with an interval field.
Currently I have a field "INTERVAL HOUR(3) TO MINUTE".
Our customer wants to store 2600 hours in this field (estimated time for a project).
I changed the field to "INTERVAL HOUR(5) TO MINUTE" and thought, it´s done.
But,when I try to store 2600 hours over JDBC, I get the Error "java.sql.SQLException: Intervals or datetimes are incompatible for the operation.".
So I started a loop to check, where the limitations are:
- From 0 to 8639999 seconds (2399 hours, 59 minutes, 59 seconds), everything works as expected
- From 8640000 to 22118399 seconds (6143 hours, 59 minutes, 59 seconds), I get the SQLException: "Intervals or datetimes are incompatible for the operation."
- From 22118400: I can store the value, but I get the wrong value returned.
- Insert with 22118400 is ok, But I get 0 as Select-Result.
Tested with 14.10.FC5, JDBC-VERSION=4.50.JC3 (I also tried 4.50.JC4W1).
My Test-Code (Delete all rows; Insert value; select and check the row).
this.connection = C_glob.getSQLContext().getConnection();
//create table tjs_intervalhmsX1 (col1 INTERVAL HOUR(5) TO MINUTE)
PreparedStatement insertStmt = connection.prepareStatement("INSERT INTO " + table + "(col1) VALUES (?) ");
PreparedStatement selStmt = connection.prepareStatement(" SELECT col1 FROM " + table + "");
PreparedStatement delStmt = connection.prepareStatement(" DELETE FROM " + table + "");
int startValue = 0;
for (int totalSeconds = startValue; totalSeconds < Integer.MAX_VALUE; totalSeconds++) {
delStmt.execute(); //Delete all Rows
//Insert the value
IntervalDF valueInsert = new IntervalDF(totalSeconds, 0);
insertStmt.setObject(1, valueInsert);
try {
insertStmt.execute();
} catch (SQLException x) {
System.out.println("Error: " + totalSeconds + ": " + x); //8640000;2400:00:00;java.sql.SQLException: Intervals or datetimes are incompatible for the operation.
}
//Check the result:
try (ResultSet res = selStmt.executeQuery()) {
if (res.next()) {
IntervalDF valueSelect = (IntervalDF) res.getObject(1);
if (valueSelect.equals(valueInsert)) {
System.out.println("Diff: " + valueSelect + " <> " + valueInsert);
} else {
System.out.println("OK: " + totalSeconds);
}
}
}
}
Should I define the field in an other way?
Or is this really a JDBC-Bug (I´m wondering, that this bug doesn´t occur more often).
My ugly workaround is currently, to execute the Insert with 0 and than I execute an update over JDBC:
"UPDATE tablexy set co1='2600:00' WHERE id=?"
------------------------------
Kind Regards
Stefan
------------------------------
#Informix