Обсуждение: PreparedStatement error upon trying to set a '?' after interval keyword

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
`

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

On 08/12/2014 04:34 AM, Sehrope Sarkuni wrote:
>     AND checkin_time > (CURRENT_DATE - (? || ' days')::interval)


It's generally preferable to write

(? * INTERVAL '1' DAY)

instead.

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Yes Craig's way is better. I was only thinking of handling the string
interpolation itself rather than generating the interval itself.

I'd probably use the ::interval cast syntax though, so something like:
checkin_time > (CURRENT_DATE - (? * '1 day'::interval))

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

On Tue, Aug 12, 2014 at 12:02 AM, Craig Ringer <craig@2ndquadrant.com> wrote:
> On 08/12/2014 04:34 AM, Sehrope Sarkuni wrote:
>>     AND checkin_time > (CURRENT_DATE - (? || ' days')::interval)
>
>
> It's generally preferable to write
>
> (? * INTERVAL '1' DAY)
>
> instead.
>
> --
>  Craig Ringer                   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services

On 08/12/2014 08:22 PM, Sehrope Sarkuni wrote:
> I'd probably use the ::interval cast syntax though, so something like:
> checkin_time > (CURRENT_DATE - (? * '1 day'::interval))

I only used

   INTERVAL '1' DAY

because AFAIK that's the standard form. Given the miserable support for
intervals in other RDBMSes, it doesn't matter much in practice.

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Sehrope, Craig:

Thanks a ton for your help. And hopefully this thread (and these
detailed answers) will be searchable for people who may have similar
questions in the future.

Best,
-j

Sehrope Sarkuni wrote:
> Yes Craig's way is better. I was only thinking of handling the string
> interpolation itself rather than generating the interval itself.
>
> I'd probably use the ::interval cast syntax though, so something like:
> checkin_time>  (CURRENT_DATE - (? * '1 day'::interval))
>
> Regards,
> -- Sehrope Sarkuni
> Founder&  CEO | JackDB, Inc. | http://www.jackdb.com/
>
> On Tue, Aug 12, 2014 at 12:02 AM, Craig Ringer<craig@2ndquadrant.com>  wrote:
>> On 08/12/2014 04:34 AM, Sehrope Sarkuni wrote:
>>>      AND checkin_time>  (CURRENT_DATE - (? || ' days')::interval)
>>
>> It's generally preferable to write
>>
>> (? * INTERVAL '1' DAY)
>>
>> instead.
>>
>> --
>>   Craig Ringer                   http://www.2ndQuadrant.com/
>>   PostgreSQL Development, 24x7 Support, Training&  Services