--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@t...> wrote:

> At 02:25 PM 25/09/2003 +0000, you wrote:
>
> >Hi Svein,
> >
> >You are right !!!
> >This problem occurs because of "CommitAction = caRefresh" setting.
> >I changed with "CommitAction = caClose" and the problem disapeared.
>
> Yes: because a "selectable stored procedure" is NOT a table - so
> "refreshing" it means executing it again!!
>
> >I think these kind of problems can be solved breaking down the SP

in

> >2: one for select and another one for update/insert.
>
> Exactly. The database will not prevent you from executing DML

inside a SP

> that outputs a multi-row set -- but that is not a good reason to

choose to

> do so. "Render unto Caesar that which is Caesar's". There are

other good

> reasons not to execute DML inside a selectable set.
> 1. Most importantly, when you write that sort of SP, your user is

looking

> at generated data inside the same transaction that has pending

uncommitted

> DML. Should an exception condition arise during the time when the

worked

> was posted and the transaction commits, there could be great

confusion for

> the user as to whether her work was done at all.
>
> 2. Another way to cause unwanted re-execution in this style of SP

is to

> read the dataset's RecordCount property! It will do select count

(*) from

> TheProc(param..) and run the executable code all over again. This

is

> currently the source of a serious bug in IBOConsole's query utility.
>
> Helen

Hi Helen,

You are perfectly right. Your reasoning are very good pointed here.
I realize I need to think closer from Interbase...
In the past, I worked with SQL Server 2000... and Interbase is pretty
much different...

Jean.