Re: Out parameters for functions.
От | Robert Zenz |
---|---|
Тема | Re: Out parameters for functions. |
Дата | |
Msg-id | 5BAB4765.7020408@sibvisions.com обсуждение исходный текст |
Ответ на | Re: Out parameters for functions. (Mark Rotteveel <mark@lawinegevaar.nl>) |
Ответы |
Re: Out parameters for functions.
|
Список | pgsql-jdbc |
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
В списке pgsql-jdbc по дате отправления: