Re: Prepared statement parameter and timestamp

On Tue, 3 Apr 2007, ste(dot)buffaishere(at)tin(dot)it wrote:

> PreparedStatement ps = conn.prepareStatement(
> "SELECT date_trunc('week', {ts ?})");
> ps.setTimestamp(1, new java.sql.
> Timestamp(
> new java.util.Date().getTime()));
>
> fails miserably with
> org.postgresql.util.PSQLException: ERROR: syntax
> error at or near "$1"

The JDBC driver translates the escape sequence to:

SELECT date_trunc('week', timestamp ?)

but the server can't handle the prefix timestamp cast for anything other
than plain literals, so it doesn't work for the parameter.

I suppose the driver should instead try to rewrite this as:

SELECT date_trunc('week', ?::timestamp)

I'm not sure how much work that would be to do.

Kris Jurka