Argh. I don't like this behavior also... When you have a 24/7 database and .... Argh.

I remember that once Ann post a message to explain this. I really don't remember the reasons (I'm not working on the code of Firebird, so if someone tell's me a nice reason and I like the explanation, I forget about the reason). Try a search on this newsgroup and maybe you will find it. (Or it was in IB-architect? not sure...). But I remember well about the diferences to M$ engine (Ann expose also the problems to the just-in-time compiler and why you want to avoid M$ way).

One workaround could be delete and create again the SP (if it's not used inside a trigger, or elsewhere by another SP....). I think that ALTER work that way, but if you do a DROP and CREATE it will work. But I'm not sure.

And I didn't answer your question. Sorry about that!

Artur

----- Original Message -----


I found out the hard way how Firebird (I suppose also Interbase) manages
procedure.
Say procedure p2 calls a procedure p1. If you alter the procedure p1, the p2
procedure will continue to use the old version of the p1 procedure until all
the connections to the database are closed. To illustrate this case, simply
create the following procedures:

create procedure p1 returns (a varchar(10))
as begin
a = 'r1';
suspend;
end

create procedure p2 returns (a varchar(10))
as begin
select a from p1 into :a;
suspend;
end

then try: select * from p2
result: 'r1'

then execute the following:

alter procedure p1 returns (a varchar(10))
as begin
a = 'r2';
suspend;
end

then try: select * from p2
result: 'r1' instead of 'r2'


After disconnecting then reconnecting to the database, the 'select * from
p2' returns will return the correct result ('r2').

Is this the normal behavior or a bug?

Salim





[Non-text portions of this message have been removed]