Thread: ERROR: no value found for parameter 1 with JDBC and Explain Analyze

Hi,
I am trying to use Explain Analyze to trace a slow SQL statement called from
JDBC.
The SQL statement with the parameters taked 11 seconds. When I run a explain
analyze from psql, it takes < 50 ms with a reasonable explain plan. However
when I try to run an explain analyze from JDBC with the parameters, I get
error :
ERROR: no value found for parameter 1

Here is sample code which causes this exception ...
  pst=prodconn.prepareStatement("explain analyze select count(*) from
jam_heaprel r where heap_id = ? and parentaddr = ?");
  pst.setInt(1,1);
  pst.setInt(2,0);
  rs=pst.executeQuery();

java.sql.SQLException: ERROR: no value found for parameter 1
    at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1471)
    at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1256)
    at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:175)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:389)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:330)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:240)
    at jsp._testexplain_2ejsp._jspService(_testexplain_2ejsp.java:82)
    at org.gjt.jsp.HttpJspPageImpl.service(HttpJspPageImpl.java:75)

Regards,

Virag

"Virag Saksena" <v_saks@hotmail.com> writes:
> ERROR: no value found for parameter 1

> Here is sample code which causes this exception ...
>   pst=prodconn.prepareStatement("explain analyze select count(*) from
> jam_heaprel r where heap_id = ? and parentaddr = ?");

I don't think EXPLAIN can take parameters (most of the "utility"
statements don't take parameters).

The usual workaround is to use PREPARE:

    PREPARE foo(paramtype,paramtype) AS SELECT ...;
    EXPLAIN EXECUTE foo(x,y);

This will generate the same parameterized plan as you'd get from the
other way, so it's a reasonable approximation to the behavior with
JDBC parameters.

            regards, tom lane