postgresql-interfaces/psqlodbc GitHub issues and pull requests (mirror)  

help / color / mirror / Atom feed

From: AndreaLFR (@AndreaLFR) <[email protected]>
To: postgresql-interfaces/psqlodbc <[email protected]>
Subject: [postgresql-interfaces/psqlodbc] issue #134: Error in executing stored
Date: Thu, 04 Sep 2025 09:57:16 +0000
Message-ID: <[email protected]> (raw)

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

view thread (5+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: github://postgresql-interfaces/psqlodbc
  Cc: [email protected], [email protected]
  Subject: Re: [postgresql-interfaces/psqlodbc] issue #134: Error in executing stored
  In-Reply-To: <<[email protected]>>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox