Thread: JDBC problem with dates and ANYELEMENT type

We have a function that takes ANYELEMENT type. If the argument is date (set using setObject(column, datevariable,
Types.DATE))the server throws error: 

ERROR: could not determine polymorphic type because input has type "unknown"

I checked JDBC sources and seems like AbstractJdbc2Statement does this:

    public void setDate(int i, java.sql.Date d, java.util.Calendar cal) throws SQLException
    {
        checkClosed();

        if (d == null)
        {
            setNull(i, Types.DATE);
            return;
        }


        if (cal != null)
            cal = (Calendar)cal.clone();

        // 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)

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



So it indeed sets ‘unspecified’ type which works fine if your target datatype is defined, but fails miserably on
ANYELEMENT.

Any suggestions how to work around this so we can still use ANYELEMENT and pass in DATE?

Peter

On Thu, 23 Apr 2009, Peter wrote:

> So it indeed sets ‘unspecified’ type which works fine if your target
> datatype is defined, but fails miserably on ANYELEMENT.
>
> Any suggestions how to work around this so we can still use ANYELEMENT
> and pass in DATE?
>

You can put a cast into the query itself "SELECT ?::date".

Kris Jurka

>> Any suggestions how to work around this so we can still use ANYELEMENT
>> and pass in DATE?

> You can put a cast into the query itself "SELECT ?::date".

Nah... that's no good. The same query string is used for many different types in my app - such approach would require
meto parse the SQL string and append cast to date when argument is java.sql.Date.  I'll leave this as last-ditch
approach.

What are the potential implications if I patch setDate() method in AbstractJdbc2Statement to pass OID.Date instead of
'unspecified'?Would that break anything else? We don’t use timestamps anywhere in the app yet, so I'm not really
worriedabout timezone being potentially screwed up. 

Peter

> What are the potential implications if I patch setDate() method in AbstractJdbc2Statement to pass OID.Date instead of
'unspecified'?Would that break anything else? We don’t use timestamps anywhere in the app yet, so I'm not really
worriedabout timezone being potentially screwed up. 
>
Reading the comments i would guess this would break a few things :(

But i didn't think postgresql supported dates with timezones (times yes
but not dates), so is the driver being overly cautious. Isn't passing it
as unspecified with a timezone, effectively passing a timestamp instead
of a date. Having the result be in the servers timezone might be
unexpected if the calendar parameter has a timezone, but isn't it the
correct behaviour?

Similarly looking at the binary patch posted previously, there is a
comment that dates aren't transmitted in binary because the unit tests
fail as they expect millisecond accuracy, surely in SQL a date has no
time component? If you require time as well shouldn't you be using
timestamp?


Just my thoughts and i guess it probably breaks a few users apps if this
were applied...

JOHN

Peter wrote:
>>> Any suggestions how to work around this so we can still use
>>> ANYELEMENT and pass in DATE?
>
>> You can put a cast into the query itself "SELECT ?::date".
>
> Nah... that's no good. The same query string is used for many
> different types in my app - such approach would require me to parse
> the SQL string and append cast to date when argument is
> java.sql.Date.  I'll leave this as last-ditch approach.

The other way of providing explicit type information is to create an
object that extends PGobject.  Something like:

import java.util.Date;
import java.text.SimpleDateFormat;
import org.postgresql.util.PGobject;

class MyDateWrapper extends PGobject {

     public MyDateWrapper(java.util.Date d) {
         setType("date");
    SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
         setValue(df.format(d));
     }
}

Then call PreparedStatement.setObject with an instance of MyDateWrapper.

Kris Jurka

>The other way of providing explicit type information is to create an
>object that extends PGobject.  Something like:


This one is a keeper! Thanks Kris!

Peter