Re: Out parameters for functions.

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