Re: Error in executing stored

Good morning Dave,

I've posted the issue on github (as AndreaLFR user).

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

cExec = "CALL lomb_amb3_VERIFICA_1();"
EXECUTE IMMEDIATE :cExec ;

The strange things are:

1. The query of the cursor, in the test cases, returns 0 rows, so never
enters in the loop.
2. There are many others stored build in similar way, without errors

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

Thanks

Andrea

Il 04/09/2025 11:40, Dave Cramer ha scritto:
>
> *CAUTION* - This e-mail originates outside of Dedalus. Be vigilant
> with content, links and attachments!
>
> Good Morning Andrea,
>
> 1/ Could you post this on github please?
> https://github.com/postgresql-interfaces/psqlodbc/issues
> <https://github.com/postgresql-interfaces/psqlodbc/issues>
> There are more eyes there.
> 2/ Can you show me how you call this function in a simple reproducer ?
>
> Thanks,
> Dave
>
>
>
> On Thu, 4 Sept 2025 at 03:57, Andrea Ricci <andrea(dot)ricci(at)dedalus(dot)eu>
> wrote:
>
> 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
> <http://doppie_ricette.nr/>
> = ultima_nr
>             and doppie_ricette._prov = ultima_prov
>             and doppie_ricette.id
> <http://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
> <http://doppie_ricette.nr/>
>                     and ris8 = doppie_ricette._prov
>                     and(ris15<>doppie_ricette.id
> <http://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
> <http://doppie_ricette.nr/>;
>         ultima_id    := doppie_ricette.id
> <http://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.
>
> What I can do ?
>
> thanks
>
> Andrea
>
> --
>

--