Martin,

> --- In firebird-support@yahoogroups.com, Thomas Steinmaurer<ts@...> wrote:
>>
>>> --- In firebird-support@yahoogroups.com, Mark Rotteveel<mark@> wrote:
>>>>
>>>> On Mon, 21 Nov 2011 08:59:30 -0000, "martin.agren"
>>>> <martin_gbg_@> wrote:
>>>>> Data output from my stored proc isnt reflecting the changes I have made.
>>>> I
>>>>> make changes in DDL, check the "new" dataset (Flamerobin or my client
>>>> app)
>>>>> and am not getting the desired results as data is unchanged. I make
>>>> another
>>>>> change, still no effect. Then, when disconnectiong and restarting my
>>>> client
>>>>> side apps all the changes suddenly appear.
>>>>>
>>>>> Very frustrating.. :)
>>>>
>>>> If you are using Classic that is - as far as I know - a known issue. Every
>>>> connection has its own metadata cache, so DDL changes are not always
>>>> directly visible to other connections. Therefor it is usually advisable to
>>>> execute DDL in isolation. Why are you executing DDL as part of - I assume
>>>> - your normal application execution? It is usually advisable not to change
>>>> the database layout as part of the your application execution and only
>>>> change it when upgrading to a newer version of the application.
>>>>
>>>
>>> Hi,
>>>
>>> No, I am using SS. And the DDL changes are never changed as part of application execution. But when developing, i often work from both Flamerobin and my app to see effects of metadata changes.
>>
>> Can you provide a re-producable test case with e.g. a simple script
>> executable in isql?
>>
>> This still sounds a bit like a transaction issue.
>>
>>
>>
>
> To me it seems more to be a per connection issue. Effects on (selectable stored procedure) data as a result of Metadata changes aren't recognized until the last active connection is dropped.
>
> But since noone else identified this behaviour, it likely is something I am doing wrong.
> But strange tho, I have been working with FB for 10 years and this shows up when going from 1.5 to 2.5. Never saw it before

I can't reproduce with the following test case.

Two isql sessions. The first one issues:

SET TERM ^ ;

CREATE OR ALTER PROCEDURE P_SIMPLE_SELECT returns (
RVALUE Integer)
AS
begin
rvalue = 3;
suspend;
end^

SET TERM ; ^

commit;



The second queries the SP with:

SQL> select * from p_simple_select;

RVALUE
===========
3

I won't commit the transaction of the second isql session. Now, I alter
the SP in the first isql session again:

SET TERM ^ ;

CREATE OR ALTER PROCEDURE P_SIMPLE_SELECT returns (
RVALUE Integer)
AS
begin
rvalue = 4;
suspend;
end^

SET TERM ; ^

commit;


Re-executing the select in the second isql session gives me:

SQL> select * from p_simple_select;

RVALUE
===========
4


So, the second isql session immediately sees the altered SP. This even
works, although a isql session uses a CONCURRENCY isolation level per
default and not READ COMMITTED.


--
With regards,
Thomas Steinmaurer

* Firebird Foundation Committee Member
http://www.firebirdsql.org/en/firebird-foundation/

* Upscene Productions - Database Tools for Developers
http://www.upscene.com/

* My Blog
http://blog.upscene.com/thomas/index.php