Re: Out parameters for functions.

Okay...I've whipped up the following test cases. Feel free to correct any
misconceptions and things that I got wrong here.

This is how it looks in Oracle:

dba.getConnection().createStatement().execute("create or replace function
TESTCALL("
+ "VALUE2 in number,"
+ "VALUE3 out number,"
+ "VALUE4 in out number)"
+ "return number is begin "
+ "value3 := 300;"
+ "value4 := 400 + VALUE4;"
+ "return 100 + VALUE2;"
+ "end;");

CallableStatement statement = dba.getConnection().prepareCall("{ ? = call
TESTCALL(?, ?, ?) }");
// RETURN
statement.registerOutParameter(1, Types.NUMERIC);
// VALUE3
statement.registerOutParameter(3, Types.NUMERIC);
// VALUE4
statement.registerOutParameter(4, Types.NUMERIC);
// VALUE2
statement.setObject(2, Integer.valueOf(1));
// VALUE4
statement.setObject(4, Integer.valueOf(2));

statement.execute();

Assert.assertEquals(BigDecimal.valueOf(101), statement.getObject(1));
Assert.assertEquals(BigDecimal.valueOf(300), statement.getObject(3));
Assert.assertEquals(BigDecimal.valueOf(402), statement.getObject(4));

And this is how it looks in PostgreSQL:

dba.getConnection().createStatement().execute("create function TEST("
+ "out VALUE1 numeric,"
+ "in VALUE2 numeric,"
+ "out VALUE3 numeric,"
+ "inout VALUE4 numeric)"
+ "returns record as $$ declare ret record; begin "
+ "value1 := 100 + VALUE2;"
+ "value3 := 300;"
+ "value4 := 400 + VALUE4;"
+ "end; $$ language plpgsql;");

CallableStatement statement = dba.getConnection().prepareCall("{ ? = call
TEST(?, ?) }");
// RETURN
statement.registerOutParameter(1, Types.NUMERIC);
// VALUE1
statement.registerOutParameter(2, Types.NUMERIC);
// VALUE3
statement.registerOutParameter(3, Types.NUMERIC);
// VALUE2
statement.setObject(2, Integer.valueOf(1));
// VALUE4
statement.setObject(3, Integer.valueOf(2));

statement.execute();

Assert.assertEquals(BigDecimal.valueOf(101), statement.getObject(1));
Assert.assertEquals(BigDecimal.valueOf(300), statement.getObject(2));
Assert.assertEquals(BigDecimal.valueOf(402), statement.getObject(3));

Note that the first parameter here is used as replacement for the return value.

Did I get the PostgreSQL example right?

On 25.09.2018 17:32, Mark Rotteveel wrote:
> 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