'PostgreSQL ODBC driver and cursor issue
In my app I use cursor to recieve dataset from stored procedure. But I have an error:
ERROR: invalid transaction termination
CONTEXT: PL/pgSQL function inline_code_block line 6 at COMMIT
SQL state: 2D000
This error raise in case when AutoCommit option is not set. In DBeaver IDE if autocommit is ON, I have no error. In pgAdmin 4 this I have error any way (autocommit is on OR is OFF).
In my app I use ODBC driver to connect. How can I set autocommit option in driver settings?
Test code bellow:
create table test_table (spid int);
insert into test_table
select 1;
insert into test_table
select pg_backend_pid();
do $$
declare
var_ImplicitResultSet refcursor := 'var_implicitresultset';
begin
delete from test_table where spid = pg_backend_pid();
commit;
open var_ImplicitResultSet FOR SELECT spid from test_table where spid <> pg_backend_pid();
END
$$;
fetch all from var_implicitresultset;
Or is there another way to get the dataset from the stored procedure? P.S. I'm adapting an old application so I need to learn how to return datasets from within procedures
Thanks for answer!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
