Обсуждение: Out parameters for functions.

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?

On 20-9-2018 15:10, Dave Cramer wrote:
> Based on the test cases in the code this is how it works.
> 
> How does one register the out parameters in other drivers ?

See section 13.3.2.2 OUT parameters and 13.3.2.3 INOUT parameters in the 
JDBC 4.3 specification:

However, the OP has a wrong assumption regarding parameter order in his 
question.

Mark

-- 
Mark Rotteveel

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

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

On 26-9-2018 10:46, Robert Zenz wrote:
> 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?

I don't have time to test right now, maybe later this week.

I think you should be able to drop the `returns record` clause from your 
function definition, and then use `{class TEST(?, ?, ?, ?)}` instead.

Your `registerOutParameter` indexes seem off as well, as you're 
currently registering an IN parameter as an OUT parameter (as far as I 
understand; I haven't done that much with stored procedure calling using 
PostgreSQL JDBC).

Mark
-- 
Mark Rotteveel