PreparedStatement TIME and DATE field
I have reached an error in my Spring JDBC DAO layer when attempting to create a prepared statement for a TIME field going against a DB2 database (v6.1). I believe that I need to intantiate the java field as a java.sql.Time object. Then pass this directly into the prepared statement and allow the DB2Java driver to take over and convert this into the time value that will ultimately get stored in the database. I found the following information on a web page: http://www.cs.tut.fi/lintula/manual/java/jdbc/jdbcprguide.htm


SOLID JDBC Driver Programmer's Guide: "Statement with parameters

The code below creates a PreparedStatement object for a query, assigns values for its parameters and executes the query. Check the the available methods for setting values to different column types from JDBC Type Conversion Matrix . The code expects a Connection object conn to be established.

PreparedStatement pstmt;
int count, cnt;
int i;

sQuery = 'INSERT INTO ALLTYPES (TI,SI,II,RR,FF,DP,DE,NU,CH,VC,DT,TM,TS) VALUES ';
sQuery = sQuery '(?,?,?,?,?,?,?,?,?,?,?,?,?)';

pstmt= conn.prepareStatement(sQuery);

pstmt.setInt(1,101);
pstmt.setInt(2,102);
pstmt.setInt(3,103);
pstmt.setDouble(4,2104.56);
pstmt.setDouble(5,104.56);
pstmt.setDouble(6,3104.56);
pstmt.setDouble(7,204.56);
pstmt.setDouble(8,304.56);
pstmt.setString(9,'cccc');
pstmt.setString(10,'longer string');

java.sql.Time pTime = new java.sql.Time(11,11,11);
java.sql.Date pDate = new java.sql.Date(96,1,2);
java.sql.Timestamp pTimestamp = new java.sql.Timestamp(96,1,2,11,11,11,0);
pstmt.setDate(11,pDate);
pstmt.setTime(12,pTime);
pstmt.setTimestamp(13,pTimestamp);

pstmt.executeUpdate();

See source code for example application sample3.

Note that the insert is not committed by the code unless the database is in autocommit mode."

********************************************************************************

Solution!!!
This was strictly a JAVA issue. What happens with the SQL Driver is that it attempts to convert the Java field that we are passing into the preparedStatement into the correct SQLParameter.
Type.VARCHAR would result in a (Object instanceof String) call.
Type.TIME would result in a (Object instanceof Time) call.
Our java fields are strings so that they can be formatted properly for DB2. That means that we can only describe our DATE and TIME fields as Type.VARCHAR or else we will get a "Program type out of range error".

org.springframework.jdbc.UncategorizedSQLException: (executing PreparedStatementCallback [PreparedStatementCreatorFactory.PreparedStatementCreatorImpl: sql=[UPDATE PBBA.BBAB20H SET BBAB20H_CDVLDT = ?,BBAB20H_DCUPDT = ?,BBAB20H_TMUPDT = ?,BBAB20H_IDUPDT = ? WHERE BBAB20H_CDFSYR = ? AND BBAB20H_NODOCU = ?]: params=[CHG,09/09/1999,10:38:40,CTK552,2005,090250074]]): encountered SQLException [[IBM][JDBC Driver] CLI0613E Program type out of range. SQLSTATE=S1003]; nested exception is COM.ibm.db2.jdbc.app.DB2Exception: [IBM][JDBC Driver] CLI0613E Program type out of range. SQLSTATE=S1003
COM.ibm.db2.jdbc.app.DB2Exception: [IBM][JDBC Driver] CLI0613E Program type out of range. SQLSTATE=S1003
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.pstmtException(SQLExceptionGenerator.java:436)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.setTargetType(DB2PreparedStatement.java:2280)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.setObject(DB2PreparedStatement.java:1939)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.setObject(DB2PreparedStatement.java:1832)
at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.setValues(PreparedStatementCreatorFactory.java:188)
at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.createPreparedStatement(PreparedStatementCreatorFactory.java:170)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:286)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:418)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:434)
at org.springframework.jdbc.object.SqlUpdate.update(SqlUpdate.java:152)
at edu.uga.busfin.dao.jdbc.CommonDAOJdbc$BudgetAmendmentAuditUpdate.update(CommonDAOJdbc.java:102)
at edu.uga.busfin.dao.jdbc.CommonDAOJdbc.updateBudgetAmendmentAudit(CommonDAOJdbc.java:61)
at edu.uga.busfin.dao.CommonDAOTest.testUpdateBudgetAmendmentAudit(CommonDAOTest.java:51)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:41)
at java.lang.reflect.Method.invoke(Method.java:386)
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)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:392)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:276)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:167)

So from here on out our DATE cols and TIME cols in our database must be described in our prepared statement as Type.VARCHAR and the java field must be of type String.

protected BudgetAmendmentAuditUpdate(DataSource ds){
super(ds,"UPDATE PBBA.BBAB20H SET " +
"BBAB20H_CDVLDT = ?," +
"BBAB20H_DCUPDT = ?," +
"BBAB20H_TMUPDT = ?," +
"BBAB20H_IDUPDT = ? " +
"WHERE BBAB20H_CDFSYR = ? AND BBAB20H_NODOCU = ?");
this.declareParameter(new SqlParameter(Types.VARCHAR)); // Validation Status
this.declareParameter(new SqlParameter(Types.VARCHAR)); // Date Updated (MUST BE A VARCHAR)
this.declareParameter(new SqlParameter(Types.VARCHAR)); // Time Updated (MUST BE A VARCHAR)
this.declareParameter(new SqlParameter(Types.VARCHAR)); // Updated By
this.declareParameter(new SqlParameter(Types.VARCHAR)); // Fiscal Year
this.declareParameter(new SqlParameter(Types.VARCHAR)); // Document Number
this.compile();
}

- posted by ThinkEddy @ 10:14 AM