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