Re: PreparedStatement.setDate() behavior with OVERLAPS - Mailing list pgsql-jdbc

Dave,

I can try building it.  I've cloned git@github.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@fastcrypt.com> wrote:

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