Hi, The following java code works fine using Oracle jdbc driver. It works fine in PostgreSQL for BigDecimal and String etc. but for date it throws error.
example: ps = conn.prepareStatement("SELECT ?, ?, ?,?"); ps.setObject(1, new String("1.2")); ps.setObject(2, new BigDecimal("1.2")); ps.setObject(3, new String("2014-01-11 33:22:11")); ps.setObject(4, Date.valueOf("2014-01-11"));
org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $4 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302) at TestSetDate.main(TestSetDate.java:39)
The above problem is solved after typecasting the 4th argument to date. ps = conn.prepareStatement("SELECT ?, ?, ?,?::date"); OR ps = conn.prepareStatement("SELECT ?, ?, ?,date(?)");
Is there any possibility to add something like PGdate in PostgreSQL jdbc driver? so that during migration from Oracle to PostgreSQL,there is no need to edit the java code.