Re: PreparedStatement.setDate() behavior with OVERLAPS

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 11 August 2015 at 10:01, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Christopher BROWN <brown(at)reflexe(dot)fr> writes:
> > When I use the following query as a PreparedStatement with the 9.4.1201
> > JDBC driver, using a 9.4.4 database, the SQL is rejected:
>
> > SELECT id, ctime, mtime, is_archived, ref_store, ref_supplier,
> > period_begins, period_ends, received_by, received_on, received_qty,
> > disposed_qty FROM store_delivery WHERE (period_begins, period_ends +
> > interval '1 day') OVERLAPS (?, ? + interval '1 day') AND ref_store = ?
> > ORDER BY period_begins, ctime
>
> > Specifically, with "invalid input syntax for type interval". I'm setting
> > the first two parameters to java.sql.Date values, using "setDate" method
> of
> > PreparedStatement (the third parameter is an integer, ex 4251).
>
> FWIW, the same would happen if you just did this in psql:
>
> regression=# select '2015-09-06' + interval '1 day';
> ERROR: invalid input syntax for type interval: "2015-09-06"
>
> The server uses various heuristics to determine the type of an unmarked
> literal or parameter symbol, and the first one that applies in this
> context is "assume it's the same type as the other input to the binary
> operator". So the only way to make this work is to explicitly tell the
> server that the parameter is of type date or timestamp. You could do that
> within the SQL string with "::date", which as you mentioned fixes the
> problem. However, I'd have expected that if you set the parameter with
> setDate or equivalent, the JDBC driver would pass along the information
> that the value is of type date. I'm not sure what the restrictions are on
> making that happen, but that's the area to sniff around in. Maybe you're
> actually using setString, for example? Or using protocol version 2, which
> doesn't have a provision for passing parameter type data?
>
> regards, tom lane
>

Tom,

I suspect it is getting lumped into the time/timestamp and we send it over
as unknown... If so we can fix setDate.

Christopher, can you try this on HEAD. If you can build it ?

>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>