| Subject | Re: [firebird-support] commit in stored procedures |
|---|---|
| Author | Helen Borrie |
| Post date | 2010-01-24T00:28:43Z |
At 08:34 AM 24/01/2010, you wrote:
What you have in PSQL is execution blocks, often nested. You use exception handling to manage the flow of control in such a way that, finally, the SP will succeed or fail and return control to the caller. You won't have difficulty with this logic if you think of it as parallel to the try...except blocks in a Delphi method.
Once control returns to your client program, it is then your program's business to commit the work of a successful SP call, or roll back the transaction if the SP failed.
So - as you do now with your Delphi programs - write your SP so that it is ready to handle whatever "go forward" or "go back" logic you want it to. Create an exception object in your user metadata (CREATE EXCEPTION ...) for each situation you want to manage.
In your SP, nest your execution blocks so that you have tight control over the flow. Inside a block, use an EXCEPTION statement to invoke the appropriate custom exception and then, after the END statement of that block, use a ON EXCEPTION SOME_EXCEPTION DO to make a block of code that is to be executed to address the condition.
Without going into detail here (you can read it yourself in the release notes) Firebird 2.5 introduces the "autonomous transaction" in PSQL, which is going to be useful for a few things that are awkward now (such as database and transaction triggers and logging operations). If used for the wrong things, it has a high potential to completely stuff up a workflow that has to maintain data consistency.
You might also be interested to read up on the explicit SAVEPOINT logic and its features that were introduced in v.1.5 and are documented in the 1.5 release notes. This is (in effect) a way to execute a series of server-side operations while controlling the flow from the client side. It can't intervene into a running SP, of course: the technique requires breaking up the workflow of the procedure into chunks of DSQL and procedure calls. It doesn't mix well with the standard Delphi way of doing things (launching activity out of datasets) but it is worth looking at if you are using Delphi components that support direct DSQL execution.
./heLen
>hi ,Yes, there is, but it is NOT a transaction commit. A SP runs *within* the context of the transaction from which it was called. Because of that, under consistency rules, the work done by a SP is either committed in entirety or rolled back in entirety.
>i should write a Stored Procedure where i first delete all
>the records of a table , and then i repopulate
>it with some calculations .
>
>I did it with Delphi so i call applyupdates method of the
>dataset at the end of the Delphi procedure when
>i am sure that no errors happened .
>
>I am tring to have the same result with
>Firebird stored procedures and i ask if there is something
>like :
>
>create procedure TEST
>start transaction
>.... code o the procedure
>commit transaction
What you have in PSQL is execution blocks, often nested. You use exception handling to manage the flow of control in such a way that, finally, the SP will succeed or fail and return control to the caller. You won't have difficulty with this logic if you think of it as parallel to the try...except blocks in a Delphi method.
>I want that the table is updated only at the end of the procedure .Your PSQL logic (what happens when exceptions occur) entirely controls how the processing ends up. Any exceptions that end up unhandled are "handled" by the engine, which simply returns control to the caller, passing the exception back.
Once control returns to your client program, it is then your program's business to commit the work of a successful SP call, or roll back the transaction if the SP failed.
So - as you do now with your Delphi programs - write your SP so that it is ready to handle whatever "go forward" or "go back" logic you want it to. Create an exception object in your user metadata (CREATE EXCEPTION ...) for each situation you want to manage.
In your SP, nest your execution blocks so that you have tight control over the flow. Inside a block, use an EXCEPTION statement to invoke the appropriate custom exception and then, after the END statement of that block, use a ON EXCEPTION SOME_EXCEPTION DO to make a block of code that is to be executed to address the condition.
Without going into detail here (you can read it yourself in the release notes) Firebird 2.5 introduces the "autonomous transaction" in PSQL, which is going to be useful for a few things that are awkward now (such as database and transaction triggers and logging operations). If used for the wrong things, it has a high potential to completely stuff up a workflow that has to maintain data consistency.
You might also be interested to read up on the explicit SAVEPOINT logic and its features that were introduced in v.1.5 and are documented in the 1.5 release notes. This is (in effect) a way to execute a series of server-side operations while controlling the flow from the client side. It can't intervene into a running SP, of course: the technique requires breaking up the workflow of the procedure into chunks of DSQL and procedure calls. It doesn't mix well with the standard Delphi way of doing things (launching activity out of datasets) but it is worth looking at if you are using Delphi components that support direct DSQL execution.
./heLen