V3 protocol + DECLARE problems

From: Oliver Jowett <oliver(at)opencloud(dot)com>
To: "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: V3 protocol + DECLARE problems
Date: 2004-07-20 22:09:49
Message-ID: 40FD982D.2080006@opencloud.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-jdbc

(cc: -hackers as I think this has been raised there before)

It's going to be fun using anything more than very basic cursors via the
V3 protocol in the JDBC driver. DECLARE does not work with parameters
passed via a Parse/Bind combination -- which is how we currently always
pass parameters when talking V3. I'm reluctant to have two
implementations of the parameter logic for V3 (one that does direct
substitution, one that uses Bind) since that's extra unnecessary code
and a recipe for inconsistent behaviour.

Logs follow; basically this is issuing a Parse/Bind/Execute for a
parameterized DECLARE, which blows up with "no value found for parameter
1" despite there definitely being one there. (also, that error appears
on Execute, not Parse/Bind).

Any chance of getting this fixed for 7.5? Alternatively, if we can get
WITH HOLD / SCROLL behaviour in portals created by Execute (probably
means a protocol change) that works too. I don't have a runnable 7.5 on
hand to test against so it's possible this has already been fixed.

-O

> Trying to establish a protocol version 3 connection to localhost:5432
> FE=> StartupPacket(user=oliver, database=test, client_encoding=UNICODE, DateStyle=ISO)
> <=BE AuthenticationOk
> <=BE ParameterStatus(client_encoding = UNICODE)
> <=BE ParameterStatus(DateStyle = ISO, DMY)
> <=BE ParameterStatus(is_superuser = off)
> <=BE ParameterStatus(server_version = 7.4.1)
> <=BE ParameterStatus(session_authorization = oliver)
> <=BE BackendKeyData(pid=676,ckey=704988999)
> <=BE ReadyForQuery(I)
> compatible = 7.5
> loglevel = 0
> prepare threshold = 0
> getConnection returning driver[className=org.postgresql.Driver,org(dot)postgresql(dot)Driver(at)ad3ba4]
> simple execute, handler=org(dot)postgresql(dot)jdbc2(dot)AbstractJdbc2Statement$StatementResultHandler(at)1dd7056, maxRows=0, fetchSize=0, flags=21
> FE=> Parse(stmt=null,query="DECLARE c CURSOR WITH HOLD FOR SELECT typname,oid from pg_type WHERE typname LIKE $1",oids={25})
> FE=> Bind(stmt=null,portal=null,$1=<%>)
> FE=> Describe(portal=null)
> FE=> Execute(portal=null,limit=1)
> FE=> Sync
> <=BE ParseComplete [null]
> <=BE BindComplete [null]
> <=BE NoData
> <=BE CommandStatus(DECLARE CURSOR)
> <=BE ErrorMessage(ERROR: no value found for parameter 1
> Location: File: execQual.c, Routine: ExecEvalParam, Line: 518
> ServerSQLState: 42704)
> java.sql.SQLException: ERROR: no value found for parameter 1
> Location: File: execQual.c, Routine: ExecEvalParam, Line: 518
> ServerSQLState: 42704
> at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1130)
> at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:933)
> at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:139)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:343)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:291)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:246)
> at TestDeclare.main(TestDeclare.java:11)
> SQLException: SQLState(42704)
> <=BE NoticeResponse(WARNING: AbortTransaction and not in in-progress state
> Location: File: xact.c, Routine: AbortTransaction, Line: 1034
> ServerSQLState: 01000)
> SQLWarning:
> <=BE ReadyForQuery(I)

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2004-07-20 22:30:23 Re: How to display privileges in psql
Previous Message Mark Kirkwood 2004-07-20 22:09:29 Re: [HACKERS] Point in Time Recovery

Browse pgsql-jdbc by date

  From Date Subject
Next Message susmitha vemprala 2004-07-21 05:54:10 unsubscribe
Previous Message Oliver Jowett 2004-07-20 21:53:07 Re: JDBC3 + HOLD_CURSORS_OVER_COMMIT