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
Attachments:
0001-Make-procedure-OUT-parameters-work-with-JDBC.patchtext/plain; charset=UTF-8; name=0001-Make-procedure-OUT-parameters-work-with-JDBC.patch; x-mac-creator=0; x-mac-type=0Download
From 760ae8f7c7fa2c1c5c8b4981eebbfc4088be4d8d Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Mon, 19 Oct 2020 11:13:45 +0200
Subject: [PATCH] Make procedure OUT parameters work with JDBC
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.
---
src/backend/parser/parse_param.c | 9 +++++++++
1 file changed, 9 insertions(+)
diff --git a/src/backend/parser/parse_param.c b/src/backend/parser/parse_param.c
index 17a96abfa8..93c9d82d01 100644
--- a/src/backend/parser/parse_param.c
+++ b/src/backend/parser/parse_param.c
@@ -163,6 +163,15 @@ variable_paramref_hook(ParseState *pstate, ParamRef *pref)
if (*pptype == InvalidOid)
*pptype = UNKNOWNOID;
+ /*
+ * If the argument is of type void and it's procedure call, interpret it
+ * as unknown. This allows the JDBC driver to not have to distinguish
+ * function and procedure calls. See also another component of this hack
+ * in ParseFuncOrColumn().
+ */
+ if (*pptype == VOIDOID && pstate->p_expr_kind == EXPR_KIND_CALL_ARGUMENT)
+ *pptype = UNKNOWNOID;
+
param = makeNode(Param);
param->paramkind = PARAM_EXTERN;
param->paramid = paramno;
--
2.28.0
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 totreat
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