Re: Out parameters for functions.

On 19-9-2018 16:42, Robert Zenz wrote:
> Hello everybody.
>
> We've recently had to use functions which used OUT parameters and we are a
> little bit confused regarding the handling of such. In other JDBC drivers one
> does simply register the OUT parameter at the appropriate position in the
> parameter list. However, with the PostgreSQL JDBC driver we had to skip the
> registration of the OUT parameter altogether, the return value for the OUT
> parameter is then returned *after* all other parameters.
>
> Example, there is function which does accept three parameters:
>
> 1. IN numeric
> 2. OUT varchar
> 3. INOUT timezone
>
> When calling this function we have to do the following:
>
> CallableStatement statement = connection.prepareCall("{ ? = call test(?, ?) }");
> statement.registerOutParameter(1, Types.NUMERIC); // Return value.
> statement.setObject(2, value); // IN numeric
> statement.setObject(3, value); // INOUT timezone
>
> statement.execute();
>
> statement.getObject(4); // OUT varchar
>
> This is a little bit confusing at first, is that intended to behave as such?

What is your actual definition of this function?

From the perspective of the JDBC specification, your assumption on
parameter order is not correct for the code shown. Given you use
`{?=call ...}`, the first parameter is the return value (as JDBC
requires), not the IN numeric you specified as your expectation.

I haven't tested it myself, but tests in the PostgreSQL JDBC codebase
(see org.postgresql.test.jdbc3.Jdbc3CallableStatementTest) suggest that
doing this should work (assuming a function defined as CREATE FUNCTION
test(in f1 int, out f2 varchar, inout f3 timezone):

CallableStatement statement = connection.prepareCall("{call test(?,
?, ?) }");
statement.registerOutParameter(2, Types.INTEGER);
statement.registerOutParameter(3, Types.OTHER); // ? maybe
different type?
statement.setObject(1, value); // IN numeric
statement.setObject(3, value); // INOUT timezone

statement.execute();

statement.getObject(2); // OUT varchar

Mark
--
Mark Rotteveel