Re: PreparedStatement error for date parameter with overlaps

This is a very minor issue, the problem only occurs with the prepared
statement, a query string without prepared works fine. I was just following
it up as it was a problem posted on an Australian forum, the original issue
has been resolved.

http://forums.whirlpool.net.au/forum-replies.cfm?t=1708081&p=-1&#bottom

Anyway, so the sql I am using is just based on the overlaps example from the
postgresql doc
http://www.postgresql.org/docs/9.0/static/functions-datetime.html

The strange thing is the doing a toString() before executeQuery() prints out
the sql as per the postgres example, I can cut and past it into psql and it
runs fine.

Both ?::date and CAST(? AS date) fail in java.

?::date works fine in psql, but CAST(? AS date) fails in psql.

SELECT (date CAST('2001-02-16' AS date), date ('2001-12-21')) OVERLAPS (date
('2001-10-30'), date ('2002-10-30'));
ERROR: syntax error at or near "CAST"

I have achieved my main aim, to put a work around on the net, so it should
turn up in a search. As I could not find one while looking at the opening
post on this forumn

I think i had an issue with setDate as well, but may have been just my poor
java.

Thanks guys for your replies.

On Tue, May 31, 2011 at 11:52, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Simon Mitchell <simon(at)jseb(dot)com> writes:
> > // some bad code
> > sqlText = "SELECT (date ?, date ?) OVERLAPS (date ?, date ?);";
>
> That's not going to work, because the syntax
> date 'something'
> only works for literal constants, and a ? marker is not a literal constant.
> You could try
> ?::date
> or
> CAST(? AS date)
> if you prefer to avoid Postgres-specific syntax. Even better, don't use
> setString --- that's really only meant for parameters that are of string
> datatypes.
>
> regards, tom lane
>

--
simon