Thread: Make procedure OUT parameters work with JDBC
A follow-up to the recently added support for OUT parameters for procedures. The JDBC driver sends OUT parameters with type void. This makes sense when calling a function, so that the parameters are ignored in ParseFuncOrColumn(). For a procedure call we want to treat them as unknown. This is of course a bit of a hack on top of another hack, but it's small and contained and gets the job done. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachment
On 10/19/20 5:19 AM, Peter Eisentraut wrote: > A follow-up to the recently added support for OUT parameters for > procedures. The JDBC driver sends OUT parameters with type void. > This makes sense when calling a function, so that the parameters are > ignored in ParseFuncOrColumn(). For a procedure call we want to treat > them as unknown. This is of course a bit of a hack on top of another > hack, but it's small and contained and gets the job done. > > I've tested this and it works as expected. +1 to apply. cheers andrew -- Andrew Dunstan EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Mon, 19 Oct 2020, 19:16 Andrew Dunstan, <andrew@dunslane.net> wrote:
On 10/19/20 5:19 AM, Peter Eisentraut wrote:
> A follow-up to the recently added support for OUT parameters for
> procedures. The JDBC driver sends OUT parameters with type void.
> This makes sense when calling a function, so that the parameters are
> ignored in ParseFuncOrColumn(). For a procedure call we want to treat
> them as unknown. This is of course a bit of a hack on top of another
> hack, but it's small and contained and gets the job done.
>
The JDBC spec defines CallableStatement.registerOutPararameter(...) variants that take SQLType enumeration value and optionally type name.
It's important that this change not break correct and fully specified use of the CallableStatement interface.
On 10/19/20 8:35 PM, Craig Ringer wrote: > > > On Mon, 19 Oct 2020, 19:16 Andrew Dunstan, <andrew@dunslane.net > <mailto:andrew@dunslane.net>> wrote: > > > On 10/19/20 5:19 AM, Peter Eisentraut wrote: > > A follow-up to the recently added support for OUT parameters for > > procedures. The JDBC driver sends OUT parameters with type void. > > This makes sense when calling a function, so that the parameters are > > ignored in ParseFuncOrColumn(). For a procedure call we want to > treat > > them as unknown. This is of course a bit of a hack on top of > another > > hack, but it's small and contained and gets the job done. > > > > > The JDBC spec defines CallableStatement.registerOutPararameter(...) > variants that take SQLType enumeration value and optionally type name. > > It's important that this change not break correct and fully specified > use of the CallableStatement interface. The JDBC driver currently implements this but discards any type information and sends VOIDOID. This patch accommodates that. This actually works fine, except in the case of overloaded procedures, where the workaround is to include an explicit cast in the CALL statement. Modifying the JDBC driver to send real type info for these cases is something to be done, but there are some difficulties in that the class where it's handled doesn't have enough context. And there will also always be cases where it really doesn't know what to send (user defined types etc.), so sending VOIDOID or UNKNOWNOID will still be done. cheers andrew -- Andrew Dunstan EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 2020-10-19 13:15, Andrew Dunstan wrote: > On 10/19/20 5:19 AM, Peter Eisentraut wrote: >> A follow-up to the recently added support for OUT parameters for >> procedures. The JDBC driver sends OUT parameters with type void. >> This makes sense when calling a function, so that the parameters are >> ignored in ParseFuncOrColumn(). For a procedure call we want to treat >> them as unknown. This is of course a bit of a hack on top of another >> hack, but it's small and contained and gets the job done. > > I've tested this and it works as expected. +1 to apply. committed -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services