Postgresql, OUT param, error on CallableStatement execution

[adrien....@live.fr]

Hi.

I'm trying to follow these advices: http://www.jooq.org/doc/3.5/manual-single-page/#stored-procedures.

Here is a function I created in a PostgreSQL:

CREATE OR REPLACE FUNCTION public.get_test_out(id_p integer, OUT result test)
 RETURNS test
 LANGUAGE plpgsql
AS $function$
  BEGIN
      SELECT * FROM test into result WHERE id = id_p;
  END
  $function$

JOOQ generates the routine class, so, following the doc, I wrote:

            DSLContext create = DSL.using(conn, SQLDialect.POSTGRES);
            GetTestOut gto = new GetTestOut();
            gto.setIdP(1);
            gto.execute(create.configuration());
            System.out.println(gto.getResult());

This codes raises an exception:

org.jooq.exception.DataAccessException: SQL [{ call "public"."get_test_out"(?, ?) }]; A CallableStatement was executed with an invalid number of parameters
    at org.jooq.impl.Utils.translate(Utils.java:1553)
    at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:571)
    at org.jooq.impl.AbstractRoutine.executeCallableStatement(AbstractRoutine.java:368)
    at org.jooq.impl.AbstractRoutine.execute(AbstractRoutine.java:270)
    at org.jooq.impl.AbstractRoutine.execute(AbstractRoutine.java:256)
    at test.Test.main(Test.java:40)
Caused by: org.postgresql.util.PSQLException: A CallableStatement was executed with an invalid number of parameters
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:437)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:410)
    at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:194)
    at org.jooq.impl.AbstractRoutine.execute0(AbstractRoutine.java:381)
    at org.jooq.impl.AbstractRoutine.executeCallableStatement(AbstractRoutine.java:342)
    ... 3 more

This seems so say that the generated statement is expecting two parameters (there are two ? placeholders).

Did I do something wrong?


Adrien.

[adrien....@live.fr]

Here are more data, about another test:

DB part:
test=> \d simpletable
   Table "public.simpletable"
┌────────┬─────────┬───────────┐
│ Column │  Type   │ Modifiers │
├────────┼─────────┼───────────┤
│ a      │ integer │           │
└────────┴─────────┴───────────┘

test=> \sf testsub2

CREATE OR REPLACE FUNCTION public.testsub2(OUT r simpletable)
 RETURNS simpletable
 LANGUAGE plpgsql
AS $function$
begin
    select * from simpletable into r limit 1;
    end;
$function$

Java part:

            Testsub2 t2 = new Testsub2();
            t2.execute(create.configuration());
            System.out.println("GOT 2 : " + t2.getR());

Got exception:

org.jooq.exception.DataAccessException: SQL [{ call "public"."testsub2"(?) }]; A CallableStatement function was executed and the out parameter 1 was of type java.sql.Types=4 however type java.sql.Types=1111 was registered.

    at org.jooq.impl.Utils.translate(Utils.java:1553)
    at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:571)
    at org.jooq.impl.AbstractRoutine.executeCallableStatement(AbstractRoutine.java:368)
    at org.jooq.impl.AbstractRoutine.execute(AbstractRoutine.java:270)
    at org.jooq.impl.AbstractRoutine.execute(AbstractRoutine.java:256)

    at test.Test.main(Test.java:43)
Caused by: org.postgresql.util.PSQLException: A CallableStatement function was executed and the out parameter 1 was of type java.sql.Types=4 however type java.sql.Types=1111 was registered.
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:468)

[Lukas Eder]

Hello,

Thanks for reporting. Returning single table records from user-defined functions is currently not supported by jOOQ. I have registered an issue for this:

https://github.com/jOOQ/jOOQ/issues/3942

Best Regards,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.