Subject difficult question about a procedure
Author Olaf Kluge
Hello,

I have created a procedure: (a customer wants a selective change)


CREATE PROCEDURE P_UPD_RENTA
Returns(
Oldvalue double precision,
Newvalue double precision,
iTeilenr varchar(8))
AS/**/
declare variable ibsnr integer;
declare variable iarbkrenta double precision;
BEGIN
for select bsnr, teilenr, arbkostenrenta from tarbg where
(((upper(arbeitsgang) not containing 'SCHWEI├čEN')
and(upper(arbeitsgang) not containing 'SCHWEISSEN')) and arbkostenrenta >
0)
into :ibsnr, :iteilenr, :iarbkrenta do
begin
oldvalue = iarbkrenta;
iarbkrenta = iarbkrenta / 10.9 * 12.4;
newvalue = iarbkrenta;
update tarbg set arbkostenrenta = :iarbkrenta where teilenr = :iteilenr
and
bsnr = :ibsnr;
suspend;
end
END

The return-values are correct, but after i have commited, there have been no
changes in table tarbg.
After I changed this as below, this works!

CREATE PROCEDURE P_UPD_RENTA
AS/**/
declare variable ibsnr integer;
declare variable iarbkrenta double precision;
declare variable iteilenr varchar(8);
BEGIN
for select bsnr, teilenr, arbkostenrenta from tarbg where
(((upper(arbeitsgang) not containing 'SCHWEIEN')
and(upper(arbeitsgang) not containing 'SCHWEISSEN')) and arbkostenrenta >
0)
into :ibsnr, :iteilenr, :iarbkrenta do
begin
iarbkrenta = iarbkrenta / 10.9 * 12.4;
update tarbg set arbkostenrenta = :iarbkrenta where teilenr = :iteilenr
and
bsnr = :ibsnr;
end
END

I'm using firebird 2.1 cs and ems interbase/firebird manager.

Thank you.

Best regards.

Olaf