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

> At 05:54 PM 4/02/2005 +0000, you wrote:
>
> >I'm a new in using stored procedures in firebird. I'm trying to

catch

> >'Exceptions' in a stored procedure in such way it will skip rows

for

> >which
> >the exception occured but will continue to loop the remaining rows.
>
> OK, first understand, without exception handling, a SP either

completes its

> work entirely without exceptions and can then returns control to

client, to

> be committed or rolled back by the client in its entirety; or it

fails on

> the first exception and returns to the client with an exception
> structure. Return values are returned on success, an error

structure,

> consisting of an error code and a message, are returned on

failure. That

> is, there is no way that putting exception stuff into a procedure's

return

> values will return those details to the client.
>
> Inside the procedure, you can use a WHEN handler at the appropriate

place,

> to swallow an exception and/or write a log message.
>
> A WHEN ANY handler will do its stuff regardless of what the

exception

> was. You can get much finer control of the exceptions trapped by

writing

> handlers that trap specific SQLCodes and gdscodes. You can also

create

> custom exceptions and throw them conditionally from the preceding

code

> block, having a corresponding WHEN block following that execution

block to

> trap it.
>
> >The body of the procedure consists of 2 updates on 2 different

tables. I have

> >3 rows in table 1 and 3 rows in table 2. When fi row 2 from table

2 is in a

> >state that it has been updated but not committed a deadlock will

occur when

> >the procedure is executed by some other user.
>
> Correct.
>
> >After executing the procedure I get 3 updated rows in table 1 and

2

> >updated rows in table 2, so I have inconsistent data in my db.
>
> Not correct. If you allow the exception to occur, the work cannot

be

> committed. You will keep your locks on those rows until you roll

back the

> transaction but the database state isn't changed.
>
> >I thought that everything within the begin..end block would

be 'undone'

> >when an exception occured.
>
> You can write exception handlers to make execution behave in

whatever way

> you need it to. If you want your procedure to complete both

updates ONLY

> if both updates can succeed then isolate the two operations inside

one

> begin...end block and place the WHEN handler immediately following

*that*

> block. The exception can be made to "skip over" the faulty pair of
> operations by just writing a log record -- or doing nothing. After

the

> handler has executed, control moves to the line immediately

following the

> begin...end block defined for WHEN whatever DO.
>
> If the block where the exception occurred was a repeating block in

a loop,

> then that iteration of the block is undone and execution resumes at

the

> next iteration. Of course, the loop itself should not have any

code

> following the exception handler, or you will come unstuck.
>
> >What i want to achieve is to loop each row in table 1, do the

updates, if

> >all updates succeeded, continue looping. When some exception

occured

> >within the for select loop, ALL changes have to be 'undone' and

continue

> >looping.
> >
> >CREATE PROCEDURE SET_STATUS (
> > RETURNERRORS SMALLINT)
> >RETURNS (
> > ERRORCODE VARCHAR(20),
> > ERRORVALUE INTEGER,
> > ERRORTEXT VARCHAR(80))
> >AS
> >DECLARE VARIABLE AID INTEGER;
> >DECLARE VARIABLE ACODE VARCHAR(8);
> >DECLARE VARIABLE ABEDRAG NUMERIC(15,2);
> >DECLARE VARIABLE ASTATUS SMALLINT;
>
> No. It's fine to have a FOR SELECT loop to iterate through a set

and use

> those data for DML operations, but this is not a select

procedure. This

> has to be called with EXECUTE PROCEDURE and, if it succeeds, you

can

> optionally return a value set to the client. But it will be a

siingle-row,

> global set.
>
> >begin
> > FOR
> > SELECT TSTID,TSTCODE,TSTBEDRAG,TSTSTATUS
> > FROM TEST
> > ORDER BY TSTID
> > INTO AID,ACODE,ABEDRAG,ASTATUS
>
> This syntax is wrong. You need colons in front of the variable

references:

>
> INTO :AID,:ACODE,:ABEDRAG,:ASTATUS
>
> > DO
> > BEGIN <--- This block must isolate the two operations
> > UPDATE TEST
> > SET
> > TSTSTATUS = 1
> > WHERE
> > TSTID = :AID;
> >
> > UPDATE TEST_CODE
> > SET
> > TCBEDRAG = TCBEDRAG + :ABEDRAG
> > WHERE
> > TCCODE = :ACODE;
> end <----------------------------- closes the execution block
> Now handle the exceptions:
> > WHEN ANY DO
> > BEGIN <------------- starts the exception handler
>
> > /* if (RETURNERRORS <> 0) then
> > BEGIN
>
> > ERRORCODE = 'SKIPPED';
> > ErrorValue = GDSCODE;
> > ErrorText = CAST(AID as VARCHAR(80));
>
> > /* SUSPEND; No!! not valid */
>
> /* Do nothing, or write a record to an errorlog */
>
> > END */
>
> > END <----- end of the exception handler
> > END <------------ end of the FOR loop. After an exception,

execution

> > will resume at the next iteration of the loop and the updates in

the

> > enclosed block will have been undone. Locks remain on the

affected rows.

>
> >end <---------- end of the procedure
>
>
> If you want a trace of the failed updates, write the details out to

a

> log. As an example of a log,
>
> create table errorlog (
> TransacID BigInt,
> Tstamp timestamp,
> Username varchar(32),
> RowID integer,
> Msg varchar(80),
> GDSError integer);
> commit;
>
> The following code does that logging:
>
> CREATE PROCEDURE SET_STATUS
> RETURNS (
> ERRORTEXT VARCHAR(40))
> AS
> DECLARE VARIABLE AID INTEGER;
> DECLARE VARIABLE ACODE VARCHAR(8);
> DECLARE VARIABLE ABEDRAG NUMERIC(15,2);
> DECLARE VARIABLE ASTATUS SMALLINT;
> DECLARE VARIABLE ERRORCODE CHAR(9);
> DECLARE VARIABLE ERRORCOUNT = 0;
> begin
> ERRORTEXT = 'No errors';
> FOR
> SELECT TSTID,TSTCODE,TSTBEDRAG,TSTSTATUS
> FROM TEST
> ORDER BY TSTID
> INTO :AID,:ACODE,:ABEDRAG,:ASTATUS
> DO
> BEGIN
> UPDATE TEST
> SET
> TSTSTATUS = 1
> WHERE
> TSTID = :AID;
>
> UPDATE TEST_CODE
> SET
> TCBEDRAG = TCBEDRAG + :ABEDRAG
> WHERE
> TCCODE = :ACODE;
> END
> WHEN ANY DO
> BEGIN
> ERRORCOUNT = ERRORCOUNT + 1;
> ERRORCODE = CAST(GDSCODE AS CHAR(9));
> INSERT INTO ERRORLOG VALUES(
> CURRENT_TRANSACTION,
> CAST('NOW' AS TIMESTAMP),
> USER,
> :AID,
> 'SKIPPED',
> :ERRORCODE);
> END
> END /* end of the FOR loop */
> IF (ERRORCOUNT > 0) THEN
> ERRORTEXT = CAST(ERRORCOUNT AS VARCHAR(9) ||
> ' updates skipped - see log for details.'
> end
>
> You can do smarter stuff than this - create custom exceptions so

you can

> write better info to the log (or back to the user, in cases where

you want

> an exception to cause execution to stop altogether); call a

WRITE_LOG

> procedure that can be called by all of your procedures; even keep

a table

> of custom messages for frequently occurring GDSCODEs to enable you

to write

> something meaningful in the log other than the gdscode

number....and so on.

>
> You can use nests of code and exception blocks to devise meaningful
> "savepoint logic" in your more complex SPs - sometimes known

as "nested

> transactions". From Firebird 1.5 onward, you can also re-raise an
> exception (see the 1.5 releasenotes for this.)
>
> If you want a log that will survive a failed execution, use an

external

> file, not a database table, for the log.
>
> ./heLen

Hi Helen,

Thanks alot for the detailed info regarding stored procedures. I hope
you don't mind, i just have a few more questions to ask to make
things clear and get me started :)

- 'EXECUTE PROCEDURE SET_STATUS_ID (:AID)' has to be enclaused in a
Begin/end block to act on exceptions occuring in the
execution 'block' and 'when' exception handeling must follow
immediatly after.
- When u have multiple 'when' clauses one after another they all act
on the same execution 'block' ('EXECUTE PROCEDURE SET_STATUS_ID
(:AID)')
- when u have created custome made exceptions (SKIP_ROW) how can i
handle the exception in a general way
WHEN EXCEPTION SKIP_ROW,EXCEPTION UNBALANCED_DOC DO
BEGIN
MyException = EXCEPTION.RDB$EXCEPTION_NAME;
MyExceptionText = EXCEPTION.RDB$MESSAGE;
END

Greetings,
Filip Moons

CREATE PROCEDURE SET_STATUS (
SESSIONID integer)
RETURNS (ERRORTEXT VARCHAR(40))
AS
DECLARE VARIABLE AID INTEGER;
DECLARE VARIABLE ACODE VARCHAR(8);
DECLARE VARIABLE ABEDRAG NUMERIC(15,2);
DECLARE VARIABLE ASTATUS SMALLINT;
DECLARE VARIABLE ERRORCOUNT INTEGER;

BEGIN
ERRORCOUNT = 0;
ERRORTEXT = 'No errors';
FOR
SELECT TSTID,TSTCODE,TSTBEDRAG,TSTSTATUS
FROM TEST
ORDER BY TSTID
INTO :AID,:ACODE,:ABEDRAG,:ASTATUS
DO
BEGIN
BEGIN
EXECUTE PROCEDURE SET_STATUS_ID (:AID);
END
WHEN GDSCODE DeadLock, GDSCODE lock_conflict DO
BEGIN
ERRORCOUNT = ERRORCOUNT + 1;
INSERT INTO TMPLOG
(TMPRESID,TMPREOID,TMPREUSERNAME,TMPREGDSERROR) VALUES
(:SESSIONID,:AID,USER,GDSCODE);
END
WHEN EXCEPTION SKIP_ROW DO
BEGIN
ERRORCOUNT = ERRORCOUNT + 1;
INSERT INTO TMPLOG
(TMPRESID,TMPREOID,TMPREUSERNAME,TMPREEXCEPTION) VALUES
(:SESSIONID,:AID,USER,'SKIP_ROW');
END
END
IF (ERRORCOUNT > 0) THEN
ERRORTEXT = CAST(ERRORCOUNT AS VARCHAR(9))||' updates skipped -
see log for details.';
END