Problem with TimeStamp and Foreign key

Connectivity
N/A

Problem with TimeStamp and Foreign key

Hello,
I have a strange error.
I am creating one table with foreign key on the same table :

CREATE TABLE TEST_B
(TID INTEGER NOT NULL,
PASSRESETDATE TIMESTAMP,
BACTIVE BYTEINT NOT NULL,
TEST_ID INTEGER,
CONSTRAINT PK_TEST_B PRIMARY KEY (TID));

ALTER TABLE TEST_B
ADD CONSTRAINT FK_TEST_B FOREIGN KEY (TEST_ID)
REFERENCES WITH CHECK OPTION TEST_B(TID);

I insert some data with java program :

PreparedStatement pstmt =null;
pstmt= conn.prepareStatement("INSERT INTO TEST_B (TID,BACTIVE,PASSRESETDATE,TEST_ID) VALUES (?,?,?,?)");
for(int i=1;i<3;i++){
int pos=1;
pstmt.setInt(pos++, i);
pstmt.setBoolean(pos++, true);
pstmt.setNull(pos++, Types.TIMESTAMP);
pstmt.setNull(pos++, Types.INTEGER);
pstmt.addBatch();
}
pstmt.executeBatch();
pstmt.close();
conn.commit();
conn.close();

I have this error :

java.sql.BatchUpdateException: [Teradata Database] [TeraJDBC 13.00.00.06] [Error 5407] [SQLState HY000] Invalid operation on an ANSI Datetime or Interval value.

at com.teradata.jdbc.jdbc_4.util.ErrorFactory.convertToBatchUpdateException(ErrorFactory.java:115)

at com.teradata.jdbc.jdbc_4.PreparedStatement.executeBatchDMLArray(PreparedStatement.java:149)

at com.teradata.jdbc.jdbc_3.ifjdbc_4.TeraLocalPreparedStatement.executeBatch(TeraLocalPreparedStatement.java:299)

at Test.main(Test.java:31)

Caused by: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata Database] [TeraJDBC 13.00.00.06] [Error 5407] [SQLState HY000] Invalid operation on an ANSI Datetime or Interval value.

at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDatabaseSQLException(ErrorFactory.java:277)

at com.teradata.jdbc.jdbc_4.statemachine.ReceiveInitSubState.action(ReceiveInitSubState.java:102)

at com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.subStateMachine(StatementReceiveState.java:285)

at com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.action(StatementReceiveState.java:176)

at com.teradata.jdbc.jdbc_4.statemachine.StatementController.runBody(StatementController.java:108)

at com.teradata.jdbc.jdbc_4.statemachine.PreparedBatchStatementController.run(PreparedBatchStatementController.java:59)

at com.teradata.jdbc.jdbc_4.Statement.executeStatement(Statement.java:331)

at com.teradata.jdbc.jdbc_4.PreparedStatement.executeBatchDMLArray(PreparedStatement.java:138)

... 2 more

Can someone help me.
Thanks
Tags (2)
4 REPLIES
Teradata Employee

Re: Problem with TimeStamp and Foreign key

This is a Teradata Database problem; it has been reported to the Engineering team.

You can work around the problem by reversing the order of the BACTIVE,PASSRESETDATE columns in the INSERT statement, and also reversing the corresponding binds.

INSERT INTO TEST_B (TID,PASSRESETDATE,BACTIVE,TEST_ID) VALUES (?,?,?,?)");

pstmt.setInt(pos++, i);
pstmt.setNull(pos++, Types.TIMESTAMP);
pstmt.setBoolean(pos++, true);
pstmt.setNull(pos++, Types.INTEGER);

Re: Problem with TimeStamp and Foreign key

Hello,

Are there any rules to define the column order ?
Do you know if the problem comes from the jdbc driver or the database engine ?

Thanks
Enthusiast

Re: Problem with TimeStamp and Foreign key

The problem is occurring even without the batch functionality using executeUpdate on PreparedStatement :
for(int i=1;i<3;i++){
int pos=1;
pstmt.setInt(pos++, i);
pstmt.setShort(pos++, (short)1);
pstmt.setNull(pos++, Types.TIMESTAMP);
pstmt.setNull(pos++, Types.INTEGER);
pstmt.executeUpdate();
}
Teradata Employee

Re: Problem with TimeStamp and Foreign key

Yes, the database problem is present regardless of whether a PreparedStatement batch or single-insert is used.

For reference, the DR number for this problem is DBS DR 146606.

Unfortunately, problems reported on Developer Exchange don't get much attention. If you are a customer, or represent a customer, a much more effective approach is to open a customer incident with Teradata Customer Support. You can reference the DR number and request a Teradata Database Efix.