PreparedStatement ps = conn.prepareStatement(
"SELECT date_trunc('week', {ts ?})");
ps.setTimestamp(1, new java.sql.
Timestamp(
new java.util.Date().getTime()));
ResultSet rs = ps.
executeQuery();
while(rs.next()) {
debug(rs.getString(1));
}
fails miserably with
org.postgresql.util.PSQLException: ERROR: syntax
error at or near "$1"
at org.postgresql.core.v3.
QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1531)
at org.postgresql.core.v3.QueryExecutorImpl.processResults
(QueryExecutorImpl.java:1313)
at org.postgresql.core.v3.
QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
at org.
postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.
java:452)
at org.postgresql.jdbc2.AbstractJdbc2Statement.
executeWithFlags(AbstractJdbc2Statement.java:354)
at org.
postgresql.jdbc2.AbstractJdbc2Statement.executeQuery
(AbstractJdbc2Statement.java:258)
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
On Tue, 3 Apr 2007, ste.buf...@tin.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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
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
>
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at