Thanks Craig and Tom, in summary my understanding is that prepared statementsneed 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
On Wed, Jun 1, 2011 at 00:10, Tom Lane
<tgl@sss.pgh.pa.us> wrote:
> 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