V3 protocol + DECLARE problems - Mailing list 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.postgresql.Driver@ad3ba4]
> simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@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)