Procedure support improvements

Поиск
Список
Период
Сортировка
От David Rader
Тема Procedure support improvements
Дата
Msg-id CAOcA-58PFqVxri+C9rZjfG1omtXcdeEWRuY2LaS6igJ7o+4UXw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Procedure support improvements
Re: Procedure support improvements
Список pgsql-jdbc
Hello - 

Since Procedures were introduced in PG 11, the workaround to invoke them with JDBC is to send the native "CALL proc()" SQL and let it be treated as a SQL statement, not a specific stored routine invocation.

1) When using transaction control inside the stored proc, an exception is generated if autoCommit is false - see example code attached.
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: invalid transaction termination

2) Output parameters are not mapped as parameters, and app code cannot use registerOutputParameter or getInt() style retrieval. Instead, outputs are left in the result set and app code must retrieve the result and pull, creating a big difference between how Procedures and Functions are invoked.

I propose improving support for procedures. Either:
(1) add support for "CALL proc()" to be treated as a routine invocation so that output parameters can be registered, no begin transaction is silently sent from driver, and calling a procedure and calling a function would be very similar (only differing in function still using the {call} escape syntax.
or
(2) change the {call} syntax to optionally support procedures. {? = call} would still be mapped to functions. Add a connection setting to control this change, and make default false, so that default stays backwards compatible with pre pg11 functionality.

Thoughts?


      

Вложения

В списке pgsql-jdbc по дате отправления:

Предыдущее
От: Craig Ringer
Дата:
Сообщение: [pgjdbc/pgjdbc] 08d812: Make ConnectTimeout test acceptNoRouteToHostExcep...
Следующее
От: Sehrope Sarkuni
Дата:
Сообщение: [pgjdbc/pgjdbc] 51f3d0: docs: Add note to GitHub PR templates abouttest s...