Subject: [postgresql-interfaces/psqlodbc] issue #134: Error in executing stored

From: "AndreaLFR (@AndreaLFR)" <[email-masked]>
Date: Thu, 04 Sep 2025 09:57:16 +0000

Hi,

using 17.0.6 version (and 13 yet, in another test) of ODBC driver against a PostGreSQL 17 database, I got an unexplicable error when, from a PowerBuilder 12 application using the ODBC, I call a stored procedure; but the same procedure, when I execute it on PGAdmin works fine!

The error is:
**SQLSTATE = 34000
ERROR: cursor "doppie_ricette" does not exists; Error while executing the query**

The stored is:

`CREATE OR REPLACE PROCEDURE public.lomb_amb3_verifica_1()
LANGUAGE 'plpgsql'
AS $BODY$
    declare ultima_riga     integer;
            ultima_nr         char(16);
            ultima_id         char(8);
            nR100             integer;
            ultima_prov     char(1);
            doppie_ricette    record;
begin
   
    ultima_nr     := '' ;
    ultima_id     := '';
    ultima_riga := 0;
    ultima_prov    := '';
    
    for doppie_ricette in
        select     numero_ricetta as nR,
                id_univoco as id,
                numero_riga_2 as _riga,
                anno as _anno,
                ospedale as osp,
                id_riga as idR,
                provenienza as _prov,
                  controllo as _controllo
        from AMB_LOMB
        where numero_ricetta is not null
                and Length(Trim(numero_ricetta))>0
                and pronto_soccorso<>'P'
                and regime<>'7'
                and provenienza<>'S'
        order by 1 asc,7 asc,5 asc,4 asc,2 asc,6 asc
        FOR UPDATE    
    loop
        if     doppie_ricette.nr = ultima_nr
            and doppie_ricette._prov = ultima_prov
            and doppie_ricette.id <> ultima_id then
           
            -- Marcare la seconda (E TUTTE LE COLLEGATE) con codice di errore
            update amb_lomb
            set errore='10C',
                errore_grave=1,
                riferimento_errore=Trim(to_char(ultima_riga, '99999'))
            where current of doppie_ricette ;
        else
            -- TEST SU NOS_100 !
            select Count(1) into nR100
            from nos_100
            where     ris7 = doppie_ricette.nr
                    and ris8 = doppie_ricette._prov
                    and(ris15<>doppie_ricette.id or ris18<>doppie_ricette._anno or ris1<>doppie_ricette.osp);
           
            if nR100>0 then
                if doppie_ricette._controllo in('A','C') then
                    update amb_lomb
                    set    errore='10E',
                        errore_grave=1,
                        riferimento_errore=''
                    where current of doppie_ricette ;
                else
                    update amb_lomb
                    set errore='10C',
                        errore_grave=1,
                        riferimento_errore='archivio'
                    where current of doppie_ricette ;
                end if ;
            end if ;

        end if;

        ultima_nr     := doppie_ricette.nr;
        ultima_id    := doppie_ricette.id;
        ultima_riga    := doppie_ricette._riga;
        ultima_prov    := doppie_ricette._PROV ;
       
    end loop ;
    
    call LOMB_AMB45_ERR_GEN('10C',3);
    call LOMB_AMB45_ERR_GEN('10E',3);

end;
$BODY$;`

The mentioned table (amb_lomb) are without blobs or similar; there are many others stored like this, working fine.

I call the procedure with a simple "call lomb_amb3_verifica_1()" in both environments, and in PB using a EXECUTE IMMEDIATE.

I have also the suspect that the behaviour of stored is - without errors like this - different between direct execution by PgAdmin and by ODBC 

What I can do ?

thanks

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 04 Sep 2025 10:51:18 +0000

@AndreaLFR can you provide me with a c program that reproduces this problem ?

---

From: "AndreaLFR (@AndreaLFR)" <[email-masked]>
Date: Mon, 08 Sep 2025 13:54:12 +0000

Hi, I'm sorry but I cannot do it... 
the ODBC-using program is written in Powerbuilder 12.6, using a standard ODBC connection to reach a PG 17 DB; the call is: 
		cExec = "CALL lomb_amb3_VERIFICA_1();"
		EXECUTE IMMEDIATE :cExec ;

I'm using only one db, only one session, only one connection, so by PgAdmin and ODBC.

The function has the variable "doppie_ricette" declared locally, and this name is never used elsewhere.

The very problems are:

    In the test cases, the query used in the cursor "doppie_ricette" never returns any row.

    If I execute - like all the processing data before this procedure - in the PgAdmin environment, everything goes well, without errors; but if I do this in a PowerBuilder (12.6) program, doing exactly the same steps, when I call this procedure I obtain the error. The sequence of operations (INSERTs, CALLs) is obviously the same, and it's on a unique connection on the same PG 17 DB.

I have done only one direct setting of the ODBC connection: "SET CLIENT_ENCODING TO 'UTF8'", for the rest the configuration of the ODBC is the original one.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 10 Sep 2025 13:52:54 +0000

well it's difficult for me to figure this out without a reproducer. I will try when I get some time.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 16 Sep 2025 15:22:12 +0000

can I get the schema for the tables involved to test this ?