Re: PreparedStatement.setDate() behavior with OVERLAPS

Christopher,

Yes, the latter, and the command that should work is

ant clean
ant snapshot

Dave Cramer

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

On 11 August 2015 at 10:20, Christopher BROWN <brown(at)reflexe(dot)fr> wrote:

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