Error in executing stored

[AndreaLFR]

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

[davecramer]

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

[AndreaLFR]

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.

[davecramer]

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

[davecramer]

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