Handling Exceptions in Firebird - Firebird

PSQL code can trap errors when they occur and hand them on to exception handler routines. If an exception is handled in your code —you provide a fix or a workaround for the error and allow execution to continue —then no exception message is returned to the client. Figure illustrates the logic of trapping and handling errors.

Error trapping and handling logic

Error trapping and handling logic

As before, the exception causes execution in the block to stop. Instead of letting execution pass to the final END statement, now the procedure searches through the layers of nested blocks, starting in the block where the error was detected and backing through the outer blocks, looking for some handler code that “knows” about this exception. It is looking for the first WHEN statement that can handle the error.

The WHEN Statement

A WHEN statement takes the form

WHEN <exception> DO <compound-statement>

Where <exception> can be any one of the following:

<exception-name> | GDSCODE code | SQLCODE code | ANY

<compound-statement> is one statement or a number of ordinary PSQL statements in a BEGIN ... END block.

Scope of Exception Types

The paradigm of exception types shown in the syntax pattern represents a scale of scope and granularity.

The custom exception can target any condition you choose, including rules that you may not be able to or choose not to express in constraints. WHEN statements and handler code that target custom exceptions are best placed within the same block where the error would occur.

Next in granularity is the GDSCODE. In v.1.0.x, it is a context variable of sorts, insofar as the procedure can read the code returned and compare it with the code specified in the WHEN predicate:

WHEN GDSCODE foreign _key DO BEGIN ... END

From v.1.5 on, GDSCODE is a full-blown context variable. As long as you read it inside the block where the exception is raised, you can capture its number code and store it in a log record.

Some errors detected by GDSCODE can be fixed inside the scope of the block where they occur. If so, the WHEN statement for handling the error can be included here; otherwise, it should go into an outer loop and be handled there.

SQLCODE is quite generic and it does not always reflect an error. SQL operations pass SQLCODE 0 for successful completion and SQLCODE 100 for end-of-file. A range of unused “slots” exists between 1 and 99 for warnings and information messages. The SQLERROR range is all sub-zero numbers greater than –1000. These SQLERROR SQLCODEs tend to be high-level groupings of several GDSCODEs.

Like a GDSCODE, a SQLCODE is only program readable in v.1.0.x but becomes a context variable in v.1.5 and later. You can capture and log it.

SQLCODEs are less granular and, in many cases, are the least likely to yield a condition that can be fixed inside the module. Look up the codes in Appendix X and notice that a single SQLCODE often groups many GDSCODEs. Usually, they are most useful in the outermost block of the module.

ANY is like “an exception of last resort.” It provides a hook for any internally defined exception that has not otherwise been handled. In v.1.5, with the ability to read the GDSCODE or the SQLCODE (provided they are in scope), ANY has better potential to provide a default handler than it does in lower versions.

Placement of WHEN Blocks

Always place your WHEN blocks immediately preceding the END statement that will close the block where you want the exception handled. Do not place any other statements—not even SUSPEND or EXIT—between the end of your handlers and the closing END statement. Refer back to Figure for a depiction of this flow.

When a procedure encounters an error in the cursor loop of a selectable procedure, the statements since the last are undone back to the previous SUSPEND. Any rows already output from previous calls to SUSPEND are unaffected and remain available to the client.

SUSPEND should not be used in executable procedures. Let your execution logic determine when blocks end and when errors should be thrown back as exceptions.

Nested Exceptions As Savepoints

The nested architecture of PSQL module execution blocks means, of course, that PSQL supports “nested” transactions. Every PSQL module’s activity is under the control of the transaction context from which it was invoked. The standard execution flow ensures that the work either completes as a whole or fails as a whole. In the case of stored procedures, an exception causes the entire invocation instance to fail. In the case of triggers, an exception causes the DML and all related events already performed to fail.

Handling exceptions provides the means to partition the execution into stages that can be undone back to specified points without necessarily discarding the entire task of the module. The level of “undoing” is determined by the point at which the error occurs and the proximity of the WHEN handler clause to the error point. The “savepoint”— equivalent to a named savepoint in a client-controlled transaction—is the start of the code block in which the WHEN handler code executes.

Handling the reassign_sales Exception

Now, back to our DELETE _EMPLOYEE procedure. In Chapter Stored Procedures, when the procedure bumped into a case where the departing employee had orders on file, it threw the custom exception reassign_sales and simply stopped, undid its work, and sent the exception message back for a human to deal with.

However, we can have the procedure handle it and allow the procedure to complete. For example, the handler could null out the SALES_REP key and send a message to another procedure that creates a log table record of each affected sales record.

We begin by creating the log table:

SETTERM ^;
CREATE TABLE EMPLOYEE_LOG (
EMP_NO SMALLINT,
TABLE_AFFECTED CHAR(31),
FIELD_AFFECTED CHAR(31),
FIELD_VALUE VARCHAR(20),
USER_NAME VARCHAR(31),
DATESTAMP TIMESTAMP) ^
COMMIT ^

Next, we need to create the procedure that will take care of the logging. It is quite generic, since we’ll assume that the same logging procedure might be wanted for other tasks in this system:

CREATE PROCEDURE LOG_ACTION (
EMP_NO SMALLINT,
TABLE_AFFECTED CHAR(31),
FIELD_AFFECTED CHAR(31),
FIELD_VALUE VARCHAR(20))
AS
BEGIN
INSERT INTO EMPLOYEE_LOG
VALUES (:EMP_NO, :TABLE_AFFECTED, :FIELD_AFFECTED,
:FIELD_VALUE, CURRENT_USER, CURRENT_TIMESTAMP);
END ^

The last thing left to do is to add the exception-handling code to our DELETE_EMPLOYEE procedure:

RECREATE PROCEDURE DELETE_EMPLOYEE (
:emp_num INTEGER)
AS
DECLARE VARIABLE PO_NUMBER CHAR(8);
BEGIN
IF (EXISTS(SELECT PO_NUMBER FROM SALES
WHERE SALES_REP = :emp_num)) THEN
EXCEPTION reassign_sales;

At this point, if the exception occurs, the following statements are bypassed and execution jumps to the first WHEN statement that can handle the exception.

UPDATE department ...
SET ...
...
...
DELETE FROM employee
WHERE emp_no = :emp_num;

Here is the handler block. First, it loops through the SALES table and sets the SALES_REP to null on all records in which our departed employee’s code appears. On each iteration of the loop, it calls the logging procedure, passing the employee’s code along with the details of the affected Sales record:

WHEN EXCEPTION REASSIGN_SALES DO
BEGIN
FOR SELECT PO_NUMBER FROM SALES
WHERE SALES_REP = :emp_num
INTO :PO_NUMBER
AS CURSOR C
DO
BEGIN
UPDATE SALES SET SALES_REP = NULL
WHERE CURRENT OF C;
EXECUTE PROCEDURE LOG_ACTION (
:emp_num, 'SALES', 'PO_NUMBER', :PO_NUMBER);
END

After the loop is finished, the main procedure calls itself once more, to complete the processing that was skipped previously because of the exception:

EXECUTE PROCEDURE DELETE_EMPLOYEE1 (:emp_num);
END
END^
COMMIT ^

Error Logs

If it is important to keep an error log, keep in mind that exceptions eventually raised to the client cause all of the work done in the module to be undone. If you are logging to a database table, the log records disappear along with the other undone work. For conditions where handlers fix or “swallow” every error, an internal log table will work just fine.

If you need a log that will survive an unhandled exception, use an external table.

SQLCODE and GDSCODE

In v.1.5 and higher, you can trap the numeric error code that is passed to an internally defined exception in the context variable SQLCODE or GDSCODE. This provides a very compact way to log the current exception as part of your exception-handling routine. Internally defined exceptions have both a SQLCODE and a GDSCODE. Your code can access one; the other will be unavailable.

The following code block framework ends with a series of exception handlers. The first two handle SQLCODE errors by handing them on to custom exceptions. These custom exceptions may be handled in an outer block, or their purpose may be to abort the procedure and return a useful message to the client.

If neither of the targeted exceptions occurs but some other, unpredicted exception does, the WHEN ANY statement picks it up. Its handler calls a stored procedure to write a log record, passing the SQLCODE, along with others taken from the context of the block, as input:

BEGIN
...
WHEN SQLCODE -802 DO
EXCEPTION E_EXCEPTION_1;
WHEN SQLCODE -803 DO
EXCEPTION E_EXCEPTION_2;
WHEN ANY DO
EXECUTE PROCEDURE P_ANY_EXCEPTION(SQLCODE, other inputs...);
END

Re-raising an Exception

Suppose you want to trap and log an unpredicted error to an external log table before allowing the exception to take its course and terminate the procedure or trigger. From v.1.5, you can re-raise an exception, meaning you can provide some handling for an exception and finish the handler with a bare EXCEPTION statement to raise it to the final END statement. Execution stops and control passes back to the client, with the exception code or name and the applicable message in the error status array.

In your handler, you pick up the GDSCODE or SQLCODE and some other context variables, write the log record, and then re-raise the exception:

BEGIN
...
WHEN ANY DO
BEGIN
EXECUTE PROCEDURE P_ANY_EXCEPTION(SQLCODE, other inputs...);
EXCEPTION;
END
END ^

Exceptions in Triggers

Custom exceptions in triggers have the power to enforce business rules. The example Employee database has a rule that customers who have had their credit stopped are flagged by column ON_HOLD, which is constrained to be either NULL or '*'. When a SALES record is inserted or an existing, unshipped one is updated for such a customer, the order is to be refused if the ON_HOLD flag is not null. Another rule says that an order that has already been shipped cannot be changed.

When inserting or updating sales order records, we can write BEFORE triggers that raise exceptions if the rules are violated and block the operation.

For either Firebird version, we can write two triggers to enforce these rules: a BEFORE INSERT and a BEFORE UPDATE.

We create exceptions for two conditions:

CREATE EXECPTION E_CANT_ACCEPT
'Operation refused. REASON: Customer is on hold.' ^
CREATE EXCEPTION E_CANT_EXTEND
'Operation refused. REASON: Order already shipped.' ^
COMMIT ^

These are the v.1.0.x triggers:

CREATE TRIGGER BI_SALES0 FOR SALES
ACTIVE BEFORE INSERT POSITION 0 AS
BEGIN
IF (EXISTS (SELECT 1 FROM CUSTOMER
WHERE CUST_NO = NEW.CUST_NO
AND ON_HOLD IS NOT NULL)) THEN
EXCEPTION E_CANT_ACCEPT;
END ^
/* */
CREATE TRIGGER BU_SALES0 FOR SALES
ACTIVE BEFORE UPDATE POSITION 0 AS
BEGIN
IF (OLD.ORDER_STATUS = 'shipped') THEN
EXCEPTION E_CANT_EXTEND;
ELSE
IF (EXISTS (SELECT 1 FROM CUSTOMER
WHERE CUST_NO = NEW.CUST_NO
AND ON_HOLD IS NOT NULL)) THEN
EXCEPTION E_CANT_ACCEPT;
END ^

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics