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