Обсуждение: Problem with DATE

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.

Regards,
Vinayak



-----
Regards,
Vinayak,

--
View this message in context: http://postgresql.nabble.com/Problem-with-DATE-tp5833008.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

  Try connecting to PostgreSQL using the version 2 protocol (older version).
    jdbc:postgresql:...?protocolVersion=2

  I've had (different) problems with dates in PostgreSQL due to the
fact, that PostgreSQL has two date formats (with and without a Time
Zone), and there are some shenanigans detecting what type the date
would have to be, and ends up using neither, blah blah (can't
remember).


2015-01-06 14:21 GMT+02:00 Vinayak <vinpokale@gmail.com>:
> 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.
>
> Regards,
> Vinayak
>
>
>
> -----
> Regards,
> Vinayak,
>
> --
> View this message in context: http://postgresql.nabble.com/Problem-with-DATE-tp5833008.html
> Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc

Hi Dave,
I am using 9.2-1004 JDBC 4 driver i.e postgresql-9.2-1004.jdbc4.jar



-----
Regards,
Vinayak,

--
View this message in context: http://postgresql.nabble.com/Problem-with-DATE-tp5833008p5833147.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

I tried to connect PostgreSQL using the version 2 protocol.
but the result showing the timezone part.
example:
2014-01-11 +05:30:00

PostgreSQL has TIMESTAMP and TIMESTAMPTZ type but in JAVA there is only
setTimestamp() and if we use protocolVersion "2" then the result showing the
time zone value.

Example:
For timestamp(without timezone):
java.sql.Timestamp  sqlDate = new java.sql.Timestamp(new
java.util.Date().getTime());
ps.setTimestamp(5, sqlDate);
Result:
2015-01-07 12:18:03.150000 +05:30:00

So I think instead of using protocolVersion "2" its better to typecast the
argument.



-----
Regards,
Vinayak,

--
View this message in context: http://postgresql.nabble.com/Problem-with-DATE-tp5833008p5833152.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Hi Dave,

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
change
the java code.



-----
Regards,
Vinayak,

--
View this message in context: http://postgresql.nabble.com/Problem-with-DATE-tp5833008p5833623.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Hi Dave,

The below example works fine using Oracle but in PostgreSQL it throws error
for date type so we need to typecast that argument.
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"));

I don't know whether to add PGDATE class is appropriate or not but if the
above example works like Oracle in PostgreSQL then not necessary to change
java code during migration from Oracle to PostgreSQL.

Am I missing something?



-----
Regards,
Vinayak,

--
View this message in context: http://postgresql.nabble.com/Problem-with-DATE-tp5833008p5833702.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Hi,

I have checked the below description in one post:
If the timestamp parameter is given, the jdbc does not know if it is to be
used timestamp or timestamptz. The jdbc driver passes it to the server as an
unknown data type letting the server figure out what to do with it.
Example:
timestampcol = ? or if you're inserting into a timestamp field so the server
can determine the datatype of parameter being used at, in this case since
its just being sent back to the user the server cannot determine the data
type.
so we need to typecast it.
"SELECT ?::timestamp"

Is the same problem for date type also?




-----
Regards,
Vinayak,

--
View this message in context: http://postgresql.nabble.com/Problem-with-DATE-tp5833008p5833708.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

  This does not seem to be true.

  Quote: AbstractJdbc2Connection:194-199

  /*
   * Does not pass unit tests because unit tests expect setDate to have
   * millisecond accuracy whereas the binary transfer only supports
   * date accuracy.
   */
  useBinarySendForOids.remove(Oid.DATE);

  And if the date is not sent in binary form, then it is sent as UNSPECIFIED:
  Quote: AbstractJdbc2Statement:3319

  bindString(i, connection.getTimestampUtils().toString(cal, d),
Oid.UNSPECIFIED);



2015-01-13 13:45 GMT+02:00 Dave Cramer <pg@fastcrypt.com>:
> No there is no similar problem for Date as there is only one date type in
> postgresql. If all you need is date then you should able to use that
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
> On 13 January 2015 at 01:42, Vinayak <vinpokale@gmail.com> wrote:
>>
>> Hi,
>>
>> I have checked the below description in one post:
>> If the timestamp parameter is given, the jdbc does not know if it is to be
>> used timestamp or timestamptz. The jdbc driver passes it to the server as
>> an
>> unknown data type letting the server figure out what to do with it.
>> Example:
>> timestampcol = ? or if you're inserting into a timestamp field so the
>> server
>> can determine the datatype of parameter being used at, in this case since
>> its just being sent back to the user the server cannot determine the data
>> type.
>> so we need to typecast it.
>> "SELECT ?::timestamp"
>>
>> Is the same problem for date type also?
>>
>>
>>
>>
>> -----
>> Regards,
>> Vinayak,
>>
>> --
>> View this message in context:
>> http://postgresql.nabble.com/Problem-with-DATE-tp5833008p5833708.html
>> Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
>>
>>
>> --
>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>

Hi,
Thank you for reply.
>No there is no similar problem for Date as there is only one date type in
postgresql. If all you need is >date then you should able to use that
Understood.

PreparedStatement throws error for date parameter
org.postgresql.util.PSQLException: ERROR: could not determine data type of
parameter $4

Is this a bug?



-----
Regards,
Vinayak,

--
View this message in context: http://postgresql.nabble.com/Problem-with-DATE-tp5833008p5833860.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Hi Dave,
Thank you for explanation.

I understood that the behavior of date is different in PostgreSQL and Java.
PostgreSQL DATE type return date part only.
Java DATE return date and time with timezone.
>I am guessing what you really want is to just take the date in your Oracle
db, load it into java and store >it into postgresql without  any
transformation.
>This would require a custom date type.
I have created custom date type which return date and time in PostgreSQL.
postgres=# select now()::date;
               now
---------------------------------
 2015-01-16 10:51:41
(1 row)
but still PreparedStatement throws error for date parameter.




-----
Regards,
Vinayak,

--
View this message in context: http://postgresql.nabble.com/Problem-with-DATE-tp5833008p5834215.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Vinayak wrote:
> Hi Dave,
> Thank you for explanation.
>
> I understood that the behavior of date is different in PostgreSQL and Java.
> PostgreSQL DATE type return date part only.
> Java DATE return date and time with timezone.
>> I am guessing what you really want is to just take the date in your Oracle
> db, load it into java and store >it into postgresql without  any
> transformation.
>> This would require a custom date type.
> I have created custom date type which return date and time in PostgreSQL.
> postgres=# select now()::date;
>                 now
> ---------------------------------
>   2015-01-16 10:51:41
> (1 row)
> but still PreparedStatement throws error for date parameter.
>
> -----
> Regards,
> Vinayak,

Hello,

If you are able to perform the conversion of the Oracle Date to a java.sql.Date
type then perhaps instead of trying to force this into a setObject() statement
have you just tried setDate()?

The code you have shown works fine with my application, but not as setObject()

if (columnType.equals("DATE"))
{
    java.sql.Date dateValue;

    dateString = MyJSQLView_Utils.convertViewDateString_To_DBDateString(

dateTimeFormString,DBTablesPanel.getGeneralDBProperties().getViewDateFormat());

     dateValue = java.sql.Date.valueOf(dateString);
     prepared_sqlStatement.setDate(i++, dateValue);
}

I know you have said that you do not wish to change your code throughout, but
this may be the easist approach rather then rewriting the driver. You  might
have already changed the code in a day's work.

danap.
MyJSQLView Project Manager
dandymadeproductions.com

Hi,
I tried to use setDate() instead of setObject() but still it gives the same
error.
Example:
ps = conn.prepareStatement("SELECT ?");
java.sql.Date sqlDate1 = new java.sql.Date(new java.util.Date().getTime());
ps.setDate(6, sqlDate1);

The problem here is java DATE have timezone and time.
The comment in the PostgreSQL jdbc driver code:
         // We must use UNSPECIFIED here, or inserting a Date-with-timezone
into a
        // timestamptz field does an unexpected rotation by the server's
TimeZone:
        //
        // We want to interpret 2005/01/01 with calendar +0100 as
        // "local midnight in +0100", but if we go via date it interprets it
        // as local midnight in the server's timezone:

        // template1=# select '2005-01-01+0100'::timestamptz;
        //       timestamptz
        // ------------------------
        //  2005-01-01 02:00:00+03
        // (1 row)

        // template1=# select '2005-01-01+0100'::date::timestamptz;
        //       timestamptz
        // ------------------------
        //  2005-01-01 00:00:00+03
        // (1 row)




-----
Regards,
Vinayak,

--
View this message in context: http://postgresql.nabble.com/Problem-with-DATE-tp5833008p5834546.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Hi Dave,

Thank you for reply.
Actually I was thinking to add this in driver(core code) but as you said
this is not a bug and will not included in the driver code.

but depending on requirement user can add DATE instead of UNSPECIFIED and
use it.
I will try to build the jar and test it.

Thank you.



-----
Regards,
Vinayak,

--
View this message in context: http://postgresql.nabble.com/Problem-with-DATE-tp5833008p5834547.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Vinayak wrote:
> Hi,
> I tried to use setDate() instead of setObject() but still it gives the same
> error.
> Example:
> ps = conn.prepareStatement("SELECT ?");
> java.sql.Date sqlDate1 = new java.sql.Date(new java.util.Date().getTime());
> ps.setDate(6, sqlDate1);

That will not work has you said and is not the same code you demostrated.

Use the Oracle TO_DATE('db_date_content', 'YYYY-MM-dd') to collect
the correct formatted date string from the existing source database,

oracleDateString = SELECT TO_DATE(SELECT dateField, 'YYYY-MM-dd')

Use dateValue = java.sql.Date.valueOf(oracleDateString), and
             prepared_sqlStatement.setDate(i++, dateValue)
to set the date in PostgreSQL database.

danap.

Hi,

ps = conn.prepareStatement("SELECT to_date(?,'yyyy-mm-dd')");
java.sql.Date sqlDate1 = new java.sql.Date(new java.util.Date().getTime());
ps.setDate(1, sqlDate1);
This works fine because here driver sent the DATE type instead of
UNSPECIFIED to the server.
we can also use the below solution for such problems:
ps = conn.prepareStatement("SELECT to_date(?,'yyyy-mm-dd')");
OR
ps = conn.prepareStatement("SELECT ?::date");
OR
ps = conn.prepareStatement("SELECT date(?)");




-----
Regards,
Vinayak,

--
View this message in context: http://postgresql.nabble.com/Problem-with-DATE-tp5833008p5834706.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.