public inbox for pgsql-odbc@postgresql.org
help / color / mirror / Atom feedFrom: Andrea Ricci <andrea.ricci@dedalus.eu>
To: Dave Cramer <davecramer@postgres.rocks>
Cc: pgsql-odbc@postgresql.org
Subject: Re: Error in executing stored
Date: Thu, 4 Sep 2025 12:02:26 +0200
Message-ID: <49112dad-f422-466b-8214-890b2b3b07cc@dedalus.eu> (raw)
In-Reply-To: <CADK3HHJPYF1cRR+Ljd-ajPeBtqHbokFp7Xd6M3HUgjfLV9Z3GQ@mail.gmail.com>
References: <32c001e8-652e-48cf-99e9-d72191195cee@dedalus.eu>
<CADK3HHJPYF1cRR+Ljd-ajPeBtqHbokFp7Xd6M3HUgjfLV9Z3GQ@mail.gmail.com>
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.ricci@dedalus.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
>
> --
>
--
Attachments:
[image/png] Immagine.png (30.1K, 3-Immagine.png)
download | view image
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: pgsql-odbc@postgresql.org
Cc: andrea.ricci@dedalus.eu, davecramer@postgres.rocks
Subject: Re: Error in executing stored
In-Reply-To: <49112dad-f422-466b-8214-890b2b3b07cc@dedalus.eu>
* 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