Re: Procedure support improvements - Mailing list pgsql-jdbc

>(2) change the {call} syntax to optionally support procedures. {? = call}
>would still be mapped to functions. Add a connection setting to control
>this change, and make default false, so that default stays backwards
>compatible with pre pg11 functionality.

Given that stored procedures were added in PG11, but PGJDBC doesn't
support calling them using JDBC's escape call syntax ("{call ...}"), I
agree that an option to allow it is required, and would be beneficial.

Resorting to using the Postgres-native "CALL ..." is not always
viable, for reasons such as:
- It's not really desirable to use "non-portable" JDBC code.
- You can't use PGJDBC and native "CALL ..." to invoke PostgreSQL
stored procedures that have INOUT arguments.
  For example, if you attempt to invoke registerOutParameter() on a
CallableStatement in this case, it results in the following error:
       This statement does not declare an OUT parameter.  Use { ?=
call ... } to declare one.
- Some software such as ORMs (e.g. JPA implementations like Hibernate,
and similar) generate JDBC code that uses the JDBC escape call syntax
(with the expectation that it will work), but attempted invocation of
PostgreSQL stored procedures using such code fails (since PGJDBC
always transforms the JDBC escape call syntax into a SELECT statement,
which can only invoke functions, not stored procedures).

Inability to support stored procedure invocation via the JDBC escape
call syntax might also be viewed as a(nother) migration issue, for
those wishing to migrate to PostgreSQL from another database vendor.

The suggested optional connection setting for JDBC escape call syntax
could be more granular than true/false.
For example, it could support different modes to:
- map to SELECT always (default)
- map to CALL if no return value
  i.e. when "{call …}" is specified
- map to CALL if no return or output parameters
  i.e. when "{call …}" is specified, and no out parameters are registered
- map to CALL always


Greg Nancarrow
Fujitsu Australia


On Thu, Aug 22, 2019 at 3:03 PM David Rader <david.rader@gmail.com> wrote:
>
> Hello -
>
> Since Procedures were introduced in PG 11, the workaround to invoke them with JDBC is to send the native "CALL
proc()"SQL and let it be treated as a SQL statement, not a specific stored routine invocation. 
>
> 1) When using transaction control inside the stored proc, an exception is generated if autoCommit is false - see
examplecode attached. 
> Exception in thread "main" org.postgresql.util.PSQLException: ERROR: invalid transaction termination
>
> 2) Output parameters are not mapped as parameters, and app code cannot use registerOutputParameter or getInt() style
retrieval.Instead, outputs are left in the result set and app code must retrieve the result and pull, creating a big
differencebetween how Procedures and Functions are invoked. 
>
> I propose improving support for procedures. Either:
> (1) add support for "CALL proc()" to be treated as a routine invocation so that output parameters can be registered,
nobegin transaction is silently sent from driver, and calling a procedure and calling a function would be very similar
(onlydiffering in function still using the {call} escape syntax. 
> or
> (2) change the {call} syntax to optionally support procedures. {? = call} would still be mapped to functions. Add a
connectionsetting to control this change, and make default false, so that default stays backwards compatible with pre
pg11functionality. 
>
> Thoughts?
>
>
>
>