Re: PreparedStatement error upon trying to set a '?' after interval keyword

Your second parameter is within single quotes so it's treated as part
of the string. Question marks (i.e. parameter placeholders) within
strings are not considered as parameters. The error message is saying
that your SQL only has a single parameter and you're trying to set a
value for parameter #2.

Parameters are not just replaced inline in the SQL string on the
client side. The question marks (i.e. parameter placeholders) are
replaced with $1, $2, etc (the PostgreSQL native syntax for
parameters). The resulting native SQL and the parameter values are
sent separately to the database server to be executed. In your case
though it's never reaching that step as the error is happening when
you try setting a value for a non-existent parameter.

You can either have the entire interval string generated in Java and
pass that as a parameter, or pass in the number of days and do the
concatenation in SQL:

// Assuming you have:
long pid = 123;
int numDays = 7;

-- Option 1:
  SELECT COUNT(*)
  FROM checkin
  WHERE pid = ?
    AND checkin_time > (CURRENT_DATE - ?::interval)

  // Java:
  ps.setLong(1, pid);
  ps.setString(2, numDays + " days");

-- Option 2:
  SELECT COUNT(*)
  FROM checkin
  WHERE pid = ?
    AND checkin_time > (CURRENT_DATE - (? || ' days')::interval)

  // Java:
  ps.setLong(1, pid);
  ps.setInt(2, numDays);

Regards,
-- Sehrope Sarkuni
Founder & CEO | JackDB, Inc. | http://www.jackdb.com/

On Mon, Aug 11, 2014 at 3:06 PM, javadesigner <javadesigner@yahoo.com> wrote:
> Hi:
>
> postgres: 9.3.4
> driver: postgresql-9.3-1102.jdbc4.jar
>
>
> I have the following query:
>
> select
>  count(*) from checkin
> where
>  pid = ? and checkin_time > current_date - interval '? days'
>
> When I try to set the interval (parameter 2, second question mark):
>
> ps.setLong("pid", 123);
> ps.setInt("interval_days", 7);
>
> I get the following error:
>
> ERROR      org.postgresql.util.PSQLException: The column index is out of
> range: 2, number of columns: 1
> at
> org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:56)
>
> =====
>
> I looked at the mailing list archives and whereas others have seen the same
> issue, I am trying to understand the root cause of why the JDBC driver
> throws an error here ? Should it just set the location of the second
> question mark, with the specified data type (in this case in integer) ?
> What's the point of throwing an error, why does the preparedstatement care ?
> Its job is to simply replace question marks with the specified data type,
> right ?
>
> Best
> --j
> `
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc