Subject Re: [firebird-support] difficult question about a procedure
Author Thomas Steinmaurer
Hello Olaf,

> 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.

The first procedure needs to be used as a "selectable" stored procedure
ala: SELECT * FROM ... where as the second SP as "executable" stored
procedure. I don't know how you execute your SP (Delphi, access
components ...), but possibly the first doesn't work because an EXECUTE
PROCEDURE is issued behind the scene.

Just a wild guess.

--
With regards,

Thomas Steinmaurer
Upscene Productions
http://www.upscene.com
http://blog.upscene.com/thomas/

Download LogManager Series, FB TraceManager today!
Continuous Database Monitoring Solutions supporting
Firebird, InterBase, Advantage Database, MS SQL Server
and NexusDB!