Обсуждение: 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?
Hi Robert,
Based on the test cases in the code this is how it works.
How does one register the out parameters in other drivers ?
On Wed, 19 Sep 2018 at 10:43, Robert Zenz <robert.zenz@sibvisions.com> 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?
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