Clément,

At 08:04 PM 19/07/2004 +0000, you wrote:

>Hi,
>
>Well I searched the docs but I have not found how to start a
>transaction inside a stored procedure.

As others have said, Firebird's transaction model doesn't support user
transactions being started on the server.

>For example:
>
>CREATE PROCEDURE SP_FOO( aID Integer )
>begin
>
> SET TRANSACTION READ WRITE WAIT ISOLATION LEVEL READ COMMITED;
>
> DELETE FROM TABLE1 WHERE INT_ID = :aID;
> DELETE FROM TABLE2 WHERE INT_ID = :aID;
>
> COMMIT;
>END;
>
>Is raising a syntax error. Unknow token: SET

Yes: because, apart from not supporting user transactions that are not
started on the client, the "SET" commands are not legitimate in PSQL.

>But I would like some more complex error handling. For example,
>SP_IMPORTMEMBERS_MNGR will call a few other stored procedure.
>Those called procedure will report success or failure. On failure,
>the *_MNGR procedure will execute a rollback and report the error to
>the application. On Success, a commit will be issued.
>
>SP_IMPORTMEMBERS_MNGR
> -Start Transaction
> -Result = import partner input file -- This is a stored procedure
> -if result<>0 then Goto SomeError
> -Result = Validate Partner File -- This is a stored procedure
> - if Result<>0 then Goto SomeError
>
> - Commit
> Return 0 -- Here I must exit the procedure
>
> -:Somerror
> Rollback
> Return Result
>
>end;
>
>
>How can I code this in Firebird?

It's much easier than that!!

Define exceptions in your database to react to failures (either handle the
exception, or stop execution and return a failure message) and use return
parameters to report success. If you're using Fb 1.5, you can also read
the error codes for internal exceptions, return run-time messages for
exceptions and re-raise an exception.

Always remember, though, that stored procedures are atomic. Either the
whole work succeeds or the whole work fails.

/heLen