pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedERROR: no value found for parameter 1 with JDBC and Explain Analyze
2+ messages / 2 participants
[nested] [flat]
* ERROR: no value found for parameter 1 with JDBC and Explain Analyze
@ 2005-11-15 10:06 Virag Saksena <v_saks@hotmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Virag Saksena @ 2005-11-15 10:06 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: ERROR: no value found for parameter 1 with JDBC and Explain Analyze
@ 2005-11-19 00:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: Virag Saksena <v_saks@hotmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2005-11-19 00:06 UTC (permalink / raw)
To: Virag Saksena <v_saks@hotmail.com>; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-11-19 00:06 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-15 10:06 ERROR: no value found for parameter 1 with JDBC and Explain Analyze Virag Saksena <v_saks@hotmail.com>
2005-11-19 00:06 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox