Re: PreparedStatement error for date parameter with overlaps

Thanks Craig and Tom, in summary my understanding is that prepared
statements
need to know the the type of a parameter, in the query itself.
Having the correct type in the jdbc set does not help.

In nearly all case the type is know, like a select col_b from table where
col_a = ?; ,
but doing a test using an overlaps test on dates requires the date type to
be made clear in the query.

In this contrived overlap example, the prepared statement fails on the 4th
parameter, even though I am using setDate.
I can fixed the error by to specifying the type by using date(?), ?::date
or CAST(? AS date) , but "date ?" format fails.

sqlText = "SELECT (date(?), ?::date ) OVERLAPS (CAST(? AS date), ?)";
PreparedStatement prs = db.prepareStatement(sqlText);
prs.setString(1,"2001-02-16");
prs.setString(2,"2001-12-21");
prs.setString(3,"2001-10-30");
prs.setDate(4,myDate));

*The following is some testing of overlaps prepared statement in psql .*
*The bad*
psql=# PREPARE overlaps_test as select ($1, $2) overlaps ($3, $4);
ERROR: function pg_catalog.overlaps(unknown, unknown, unknown, unknown) is
not unique
LINE 1: PREPARE overlaps_test as select ($1, $2) overlaps ($3, $4);

psql=# PREPARE overlaps_test as select (date $1, date $2) overlaps (date
$3, date $4);
ERROR: syntax error at or near "$1"
LINE 1: PREPARE overlaps_test as select (date $1, date $2) overlaps...

*the good*
psql=# PREPARE over_test (date,date,date,date) as select ($1, $2) overlaps
($3, $4);

psql=# PREPARE overlaps_test as select (date($1), date($2)) overlaps
(date($3), date($4));

psql=# PREPARE overlaps_test as select ($1::date, $2::date) overlaps
($3::date, $4::date);

psql=# PREPARE overlaps_test as select (CAST($1 AS date), CAST($2 AS date))
overlaps (CAST($3 AS date), CAST($4 AS date));

Thanks again for your time and help, had not used prepare and deallocate
before in psql.

I note that this overlaps issue, with date ?, happens in a stored procedure
in this 2005 post
http://archives.postgresql.org/pgsql-sql/2005-08/msg00182.php

On Wed, Jun 1, 2011 at 00:10, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Simon Mitchell <simon(at)jseb(dot)com> writes:
> > So from what I understand using the date function in jdbc stuff requires
> > brackets like most functions do,
> > but the psql is happy for me to use the date function with or without
> > brackets.
>
> Craig Ringer already posted a much more long-winded response, but the
> short answer is that this syntax is *not* a function. The SQL spec
> defines
> type_name 'literal string'
> as a way of writing a literal constant of a specific type. (The spec
> actually only seems to allow this for certain type names, but Postgres
> extends that to allow any known type name to be used.) This is not a
> function and it doesn't work for any case except where the second part
> is a quoted literal string. Personally I think it's a wart best
> avoided, but we're stuck with supporting it because of the standard.
>
> regards, tom lane
>

--
simon