public inbox for pgsql-odbc@postgresql.org
help / color / mirror / Atom feedError in executing stored
3+ messages / 2 participants
[nested] [flat]
* Error in executing stored
@ 2025-09-04 07:56 Andrea Ricci <andrea.ricci@dedalus.eu>
0 siblings, 1 reply; 3+ messages in thread
From: Andrea Ricci @ 2025-09-04 07:56 UTC (permalink / raw)
To: pgsql-odbc
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.
What I can do ?
thanks
Andrea
--
Attachments:
[image/png] Immagine.png (30.1K, 3-Immagine.png)
download | view image
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Error in executing stored
@ 2025-09-04 09:40 Dave Cramer <davecramer@postgres.rocks>
parent: Andrea Ricci <andrea.ricci@dedalus.eu>
0 siblings, 1 reply; 3+ messages in thread
From: Dave Cramer @ 2025-09-04 09:40 UTC (permalink / raw)
To: Andrea Ricci <andrea.ricci@dedalus.eu>; +Cc: pgsql-odbc
Good Morning Andrea,
1/ Could you post this on github please?
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 = 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.
>
> What I can do ?
>
> thanks
>
> Andrea
>
> --
>
Attachments:
[image/png] Immagine.png (30.1K, 3-Immagine.png)
download | view image
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Error in executing stored
@ 2025-09-04 10:02 Andrea Ricci <andrea.ricci@dedalus.eu>
parent: Dave Cramer <davecramer@postgres.rocks>
0 siblings, 0 replies; 3+ messages in thread
From: Andrea Ricci @ 2025-09-04 10:02 UTC (permalink / raw)
To: Dave Cramer <davecramer@postgres.rocks>; +Cc: pgsql-odbc
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-09-04 10:02 UTC | newest]
Thread overview: 3+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-09-04 07:56 Error in executing stored Andrea Ricci <andrea.ricci@dedalus.eu>
2025-09-04 09:40 ` Dave Cramer <davecramer@postgres.rocks>
2025-09-04 10:02 ` Andrea Ricci <andrea.ricci@dedalus.eu>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox