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.
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.htmlThe 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@sss.pgh.pa.us> wrote:
Simon Mitchell <
simon@jseb.com> writes:
> // some bad code
> sqlText = "SELECT (date ?, date ?) OVERLAPS (date ?, date ?);";
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
