Thread: INTERVAL parameter in PreparedStatement worked in 7.4 but not in 8.0 driver

Hello,

I used to use the following style statement with the 7.4 series JDBC driver:

select * from users users where users.createdDate < (CURRENT_DATE -
INTERVAL ?)

and would set the parameter to a string value of "14 DAYS"

but in the Postgres 8 driver, this query executes but does not seem to
ever return anything. Is there a better syntax I could use to make this
work?

The query does work in the 8 driver if the ? is explicitly part of the
query, i.e. not a positional parameter, like this:

select * from users users where users.createdDate < (CURRENT_DATE -
INTERVAL '14 DAYS')

Any help much appreciated,
m@

I forgot to add that the 8 driver throws an exception when trying to parse
the statement with the positional parameter:

java.sql.SQLException: ERROR: syntax error at or near "$1"

-- m@

> Hello,
>
> I used to use the following style statement with the 7.4 series JDBC
> driver:
>
> select * from users users where users.createdDate < (CURRENT_DATE -
> INTERVAL ?)
>
> and would set the parameter to a string value of "14 DAYS"
>
> but in the Postgres 8 driver, this query executes but does not seem to
> ever return anything. Is there a better syntax I could use to make this
> work?
>
> The query does work in the 8 driver if the ? is explicitly part of the
> query, i.e. not a positional parameter, like this:
>
> select * from users users where users.createdDate < (CURRENT_DATE -
> INTERVAL '14 DAYS')
>
> Any help much appreciated,
> m@

Matt Magoffin wrote:

> I used to use the following style statement with the 7.4 series JDBC driver:
>
> select * from users users where users.createdDate < (CURRENT_DATE -
> INTERVAL ?)
>
> and would set the parameter to a string value of "14 DAYS"
>
> but in the Postgres 8 driver, this query executes but does not seem to
> ever return anything. Is there a better syntax I could use to make this
> work?

"CAST (? AS INTERVAL)" should work.

Or use org.postgresql.util.PGInterval:

   stmt.setObject(1, new PGInterval("14 days"));

-O

Thank you, the CAST worked perfectly.

-- m@

> "CAST (? AS INTERVAL)" should work.
>
> Or use org.postgresql.util.PGInterval:
>
>    stmt.setObject(1, new PGInterval("14 days"));
>
> -O