Re: Prepared statement parameter and timestamp

Kris Jurka a écrit :
> 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 purpose of the {ts 'XXX'} JDBC escape syntax is to have a standard
syntax to write timestamp literal values. But if you are using a
PreparedStatement you should directly write : SELECT date_trunc('week',?).

>
>
> 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.

This is a one line change.

>
> Kris Jurka
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>