Message-ID: <gh-postgresql-interfaces-psqlodbc-134@github.com>
From: "AndreaLFR (@AndreaLFR)" <noreply+AndreaLFR@github.com>
To: "postgresql-interfaces/psqlodbc" <noreply+postgresql-interfaces-psqlodbc@github.com>
Date: Thu, 04 Sep 2025 09:57:16 +0000
Subject: [postgresql-interfaces/psqlodbc] issue #134: Error in executing stored
List-Id: <gh-postgresql-interfaces-psqlodbc.github.com>
X-GitHub-Author-Id: 58087675
X-GitHub-Author-Login: AndreaLFR
X-GitHub-Issue: 134
X-GitHub-Repo: postgresql-interfaces/psqlodbc
X-GitHub-State: open
X-GitHub-Type: issue
X-GitHub-Url: https://github.com/postgresql-interfaces/psqlodbc/issues/134
Content-Type: text/plain; charset=utf-8

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