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