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
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 formWHEN <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 ^;
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 (
The last thing left to do is to add the exception-handling code to our DELETE_EMPLOYEE procedure:RECREATE PROCEDURE DELETE_EMPLOYEE (
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 ...
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
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);
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
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
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
These are the v.1.0.x triggers:CREATE TRIGGER BI_SALES0 FOR SALES
Firebird Related Interview Questions
|RDBMS Interview Questions||MySQL Interview Questions|
|Linux Interview Questions||Mac OS X Deployment Interview Questions|
|Windows Administration Interview Questions||Windows Server 2003 Interview Questions|
|SQL Interview Questions||NoSQL Interview Questions|
|Advanced C++ Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.