Re: 7.3 -> 8.0.4 migration timestamp problem

Eliézer Madeira de Campos wrote:

>>> Timestamp ts = new Timestamp(c.getTimeInMillis());
>>> pst = con.prepareStatement("select date_trunc('day', TIMESTAMP ?)");
>>> pst.setObject(1, ts);
>
>

>>Use "CAST (? AS TIMESTAMP)" instead of "TIMESTAMP ?".
>
> Why should I, if "TIMESTAMP ?" works when I run the insert in psql (or via unprepared statement)?

There are lots of other places where you can't blindly use '?'
placeholders -- for example, you can't use them where a column or table
name is expected. Prepared statements aren't just textual substitution.
The 8.0 driver's implementation uses protocol-level parameter binding
that is roughly equivalent to PREPARE/EXECUTE at the SQL level. Try that
same query via PREPARE in psql and you will see that it fails in the
same way.

> That might be valid as a workaround, but it would cost me thousands changes in the application, so it's not really a solution to the problem.

You need to talk to the backend developers then -- it's a limitation of
the SQL grammar used by the backend.

As a workaround, set protocolVersion=2 as a URL parameter, but you will
lose other driver functionality if you do that (e.g. parameter
metadata), and the v2 protocol path will not stay around forever.

-O