Re: PreparedStatement.setDate() behavior with OVERLAPS

Поиск
Список
Период
Сортировка
От Dave Cramer
Тема Re: PreparedStatement.setDate() behavior with OVERLAPS
Дата
Msg-id CADK3HHJj8B2UyDVQj+xrN-8b8c1sz6kS5dspqsc14peKyMBcZQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: PreparedStatement.setDate() behavior with OVERLAPS  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: PreparedStatement.setDate() behavior with OVERLAPS
Список pgsql-jdbc

Dave Cramer

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

On 11 August 2015 at 10:01, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Christopher BROWN <brown@reflexe.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

В списке pgsql-jdbc по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: PreparedStatement.setDate() behavior with OVERLAPS
Следующее
От: Christopher BROWN
Дата:
Сообщение: Re: PreparedStatement.setDate() behavior with OVERLAPS