Archives of the TeradataForum

Message Posted: Wed, 14 Jun 2006 @ 10:56:15 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Forwarding for our client...

Hi all,

I'm encountering some problems with PreparedStatements in the JDBC driver version 03.02.00.02 and Teradata release V2R.05.00.03.26 version 05.00.03.50A. According to the release notes this error should only apply to releases < V2R5.0.0.39. Whenever I attempt to use the "setTimestamp(int index, java.sql.Timestamp)" method, I receive the following error. I've included a stack trace, the release notes I'm referring to, as well as a unit test that exhibits the behavior. Has anyone else encountered this error, and if so, how did you get around it? We're using Hibernate as our persistence framework, so I must use a prepared statement, executing a sql insert as a string is not an option.

Thanks in advance,

Todd

     com.ncr.teradata.jdbc_4.util.JDBCException: [NCR] [Teradata DBMS] : Invalid timestamp.
     at com.ncr.teradata.jdbc_4.statemachine.ReceiveInitSubState.action(ReceiveInitSubState.java:87)
     at com.ncr.teradata.jdbc_4.statemachine.StatementReceiveState.subStateMachine(StatementReceiveState.java:257)
     at com.ncr.teradata.jdbc_4.statemachine.StatementReceiveState.action(StatementReceiveState.java:141)
     at com.ncr.teradata.jdbc_4.statemachine.StatementController.runBody(StatementController.java:96)
     at com.ncr.teradata.jdbc_4.statemachine.PreparedStatementController.run(PreparedStatementController.java:42)
     at com.ncr.teradata.jdbc_4.Statement.executeStatement(Statement.java:230)
     at com.ncr.teradata.jdbc_4.PreparedStatement.executeUpdate(PreparedStatement.java:60)
     at com.ncr.teradata.jdbc_3.ifjdbc_4.TeraLocalPreparedStatement.executeUpdate
     (TeraLocalPreparedStatement.java:118)
     at com.ata.partnership.dataaccess.dao.JDBCTest.testJDBCInsert(JDBCTest.java:89)
     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor Impl.java:25)
     at java.lang.reflect.Method.invoke(Method.java:324)
     at junit.framework.TestCase.runTest(TestCase.java:154)
     at junit.framework.TestCase.runBare(TestCase.java:127)
     at junit.framework.TestResult$1.protect(TestResult.java:106)
     at junit.framework.TestResult.runProtected(TestResult.java:124)
     at junit.framework.TestResult.run(TestResult.java:109)
     at junit.framework.TestCase.run(TestCase.java:118)
     at junit.framework.TestSuite.runTest(TestSuite.java:208)
     at junit.framework.TestSuite.run(TestSuite.java:203)

     SQLState=HY000
     vendorCode=6760

Release Notes regarding the error:

Invalid Time/Timestamp Errors with V2R5.0
-----------------------------------------

Teradata Database releases V2R5.0.0.0 through V2R5.0.0.38 return error 6760 "Invalid time" or "Invalid timestamp" when you use a PreparedStatement and bind a java.sql.Time value to a TIME column, or bind a java.sql.Timestamp value to a TIMESTAMP column.

This problem is documented in DR 81487. The fix for the problem was shipped to Teradata Database V2R5.0.0.39.

Solution:
If you encounter this problem when using JDBC 3.2 with V2R5.0, upgrade to Teradata Database V2R5.0.0.39 or a subsequent V2R5.0 version, or upgrade to Teradata Database V2R5.1 or V2R6.0.

Here is a simple test case that causes the error. As you can see, I'm just inserting a timestamp.

     package com.ata.partnership.dataaccess.dao;

     import java.sql.Connection;
     import java.sql.Driver;
     import java.sql.DriverManager;
     import java.sql.PreparedStatement;
     import java.sql.Timestamp;
     import java.util.Calendar;

     import junit.framework.TestCase;

     /**
     * @author Todd Nine
     *
     */
     public class JDBCTest extends TestCase {

     /**
     * @param arg0
     */
     public JDBCTest(String arg0) {
     super(arg0);
     }

     public void testJDBCInsert() throws Exception {

     DriverManager.registerDriver((Driver) Class.forName(
     "com.ncr.teradata.TeraDriver").newInstance());
     String url = "jdbc:teradata://ncr1/DATABASE=Devdb";
     String username = "foo";
     String password = "bar";

     Connection con = DriverManager.getConnection(url, username, password);

     String insertStmnt = "insert into DevDB.Partner (PARTNER_NM,
     PARTNER_DESC, PARTNER_TYPE, PARTNER_PROG_CD, DEFAULT_ACTIVITY_TYPE_CD,
     EFFECTIVE_DT, STATUS, Address_Line_1, Address_Line_2, City, State_CD,
     ZIP_CD, Phone_Nbr, ROW_CRT_DTTM, ROW_CRT_USER_ID, ROW_LAST_PROC_ID,
     ROW_UPDT_DTTM, ROW_UPDT_USER_ID, PARTNER_ID) values (?, ?, ?, ?, ?, ?,
     ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

     PreparedStatement ps = con.prepareStatement(insertStmnt);

     ps.setString(1, "Test partner name");
     ps.setString(2, "Test Partner");
     ps.setString(3, "T");
     ps.setString(4, "test code");
     ps.setString(5, "T");

     Calendar cal = Calendar.getInstance();
     ps.setDate(6, new java.sql.Date(cal.getTimeInMillis()), cal);

     ps.setString(7, "Y");

     ps.setString(8, "addr line 1");
     ps.setString(9, "addr line 2");
     ps.setString(10, "testcity1");
     ps.setString(11, "IN");
     ps.setString(12, "47933");
     ps.setString(13, "5555554343");
     ps.setTimestamp(14, new Timestamp(cal.getTimeInMillis()));
     ps.setString(15, "testuser");
     ps.setInt(16, 1);
     ps.setTimestamp(17, new Timestamp(cal.getTimeInMillis()));
     ps.setString(18, "testuser");
     ps.setString(19, "ts1");

     // this fails

     ps.executeUpdate();

     ps.close();
     con.close();

     }

     }

--
Brett Brennan
Principal Consultant
Homenode Group Inc

	<Prev	Next>		<<First	<Prev	Next>	Last>>