Re: PreparedStatement.setDate() behavior with OVERLAPS

Dave,

I can try building it. I've cloned git(at)github(dot)com:pgjdbc/pgjdbc.git but
running "ant -projecthelp" just lists a lot of Maven Central-related tasks,
and running "ant jar" yielded the following message:
build.xml:107: snapshot-version or release-version target must run as the
first task

I'm using Ant 1.9.6 and Java 8 by default. What command should I use to
build it? (and what command should I use to clean up generated stuff?)

Is it already fixed, or do you want me to check before you try fixing it
(I'm guessing the second option)?

Thanks,
Christopher

On 11 August 2015 at 16:10, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:

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