Subject Re: [firebird-support] transactions in nested stored procedures
Author Helen Borrie
At 12:42 PM 12/12/2003 +0000, you wrote:
>I have the following question(problem) :
>I created two stored procedures SP1 and SP2.
>SP1 calls SP2 to prepare (insert) some data in a table. After SP2
>finishes, SP1 tries to read the data out of the table which
>previously SP2 stored into it. At this moment the table seems to be
>still empty. Is it possible, that after finishing SP2 the insert
>operations in SP2 are not committed?
>If testing SP2 alone, the insert operations are working as expected...
>If a SP do commit data changes after finishes, then maybe the SP
>debugger in IBExpert isn't committing when I trace step by step
>through the SP.....?

It's a bit unclear - are you saying you expect a stored procedure to commit
the changes it makes? (It doesn't..) But SP1 should be able to see
changes pending from SP2's work. Nothing will get committed until the
first-called procedure returns to the client and the client purposely
commits the transaction. No changes happen at all if there is an unhandled
exception anywhere and the transaction is rolled back by the client.

I don't know how a debugger could actually be executing a compiled SP while
tracing through its source code. Perhaps it translates the sources into
DSQL statements and executes, then rolls back those before moving on...