'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