passing parameter to java prepare statement

Question

I'm actually trying to execute delete from mytable where CreationDate < now() - interval '5 month'

I want to pass a parameter 5 to ?

public static final StringBuilder SQL_MY_Query= new StringBuilder(
        "delete from mytable where CreationDate < now() - interval '? month'");

ps = con.prepareStatement(SQL_MY_Query.toString());
ps.setInt(1, 5);

I'm getting an error Sql Exception org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0. How do we fix this?

A question mark inside a string literal is not a parameter placeholder. — Mark Rotteveel, Mar 01 '16 at 09:30
Possible duplicate: http://stackoverflow.com/questions/7796657/using-a-variable-period-in-an-interval-in-postgres — Mark Rotteveel, Mar 01 '16 at 09:32
if column index is out of range, does the table exist? 0 columns... — ThoFin, Mar 01 '16 at 09:32
@ThoFin No it is the index of the parameter in the query (in this case there is no parameter 1 because there are no parameters in the query.). — Mark Rotteveel, Mar 01 '16 at 09:35

score 1 · Accepted Answer · edited May 23 '17 at 11:54

1

The question mark must replace a token; not be in a string literal.

Try this:

public static final StringBuilder SQL_MY_Query= new StringBuilder(
        "delete from mytable where CreationDate < now() - interval ? || ' month'");

Or (based on: Using a variable period in an interval in Postgres ):

public static final StringBuilder SQL_MY_Query= new StringBuilder(
        "delete from mytable where CreationDate < now() - (? || ' month')::interval");

edited May 23 '17 at 11:54

Community

1
1

answered Mar 01 '16 at 09:37

cadrian

7,332
2
33
42

1

Have you tried this, the link I posted in the comments suggest it should be `(? || ' month')::interval` – Mark Rotteveel Mar 01 '16 at 09:38
1

I don't have postgresql at hand right now, so we are actually in the same boat ;) – Mark Rotteveel Mar 01 '16 at 09:43

score 0 · Answer 2 · answered Mar 01 '16 at 09:47

One of the below might help. Have a look.

Option 1: delete from mytable where CreationDate < now() - interval ? "

ps.setString(1, numMonth + " month");

Option 2: delete from mytable where CreationDate < now() - interval (? || ' month')"

ps.setString(1, numMonth);

passing parameter to java prepare statement

2 Answers2