An Overview of Errors Based on the Possibilities of their Handling

Processing Errors and Exceptions in SQL

Diagnostic Tools

Errors and Transactions

Transactions join database operations into atomic units. Most errors affect transactions, an error may cause a transaction to terminate prematurely or cause a rollback.

If an error or an exception causes a transaction rollback to occur, further actions may be one of the following:

1. If the error/exception is "NOT FOUND" or a "handleable exception condition" and is processed by a handler, then the transaction is not affected (however the handler type determines if some statements are to be skipped or if the atomic block is to be rolled back).
2. If the error belongs to the "rollback exception condition" type and is processed by a handler, then the transaction is rolled back before the handler is executed. After the handler is terminated, the program continues in the place set by the handler type.
3. If the error or exception does not belong to a category processed by some handler, or no handler is found, the processing of SQL statements is terminated and the transaction is rolled back. The transaction termination depends on the client:
   • If the client did not explicitly start a transaction (therefore each client request is executed as an independent transaction), then this single transaction is rolled back.
   • If the client explicitly started a transaction and sends more requests to the server during the transaction, further statement execution depends on the compatibility flag SQLOPT_ERROR_STOPS_TRANS:
     • If the SQLOPT_ERROR_STOPS_TRANS flag is set, the transaction is terminated after the error, but before the server sends an error message to the client. The database changes that were to be done are not done since they are rolled back. Further client requests that belong to another transaction are executed regardless of the error.
     • If the SQLOPT_ERROR_STOPS_TRANS flag is not set, the transaction continues after the error, including all further client requests until the explicit COMMIT or ROLLBACK request is executed. Since the requests sent after the error occurs belong to the same transaction, they are rolled back and have no effect on the database.

Details of Exception Handling Using EXIT Handler

If an exception of the "rollback exception condition" type is raised, changes since the transaction beginning are rolled back, however, the commands after the block end - where the EXIT handler for this exception is declared - are executed. If such behavior is undesirable, the EXIT handler can be moved to the external block or the RESIGNAL command can be executed inside it.

Exception handling can be illustrated by the following example:

The Handling procedure listed bellow demonstrates exception handling using EXIT handler. The handlable parameter determines whether they are raised by "handlable exception condition" (true) or "rollback exception condition" (false). The resignal_it parameter determines whether the handler will execute the RESIGNAL command. If the resignal_it parameter is true, then the procedure returns an error, since no other handler for this error has been declared.

To raise "handlable exception condition", we will use user exception ABC. To raise "rollback exception condition", we will use a table with a unique key; the procedure will try to insert into it values causing key duplicity. The table is defined like follows:

CREATE TABLE RESTAB (VAL INTEGER, CONSTRAINT INDEX1 UNIQUE (VAL) NULL )

The table will simultaneously contain a result of procedure work. The procedure is:

PROCEDURE Handling(IN handlable Boolean, IN resignal_it Boolean);
BEGIN
  declare abc condition;
  delete from restab;
  insert into restab values(0);
  commit;
  BEGIN
    declare exit handler for sqlexception, abc
    BEGIN
      update restab set val=val+100;
      commit;
      IF resignal_it THEN resignal;  END IF;
    END;

   // make an update and commit it:
    update restab set val=val+1;   // sets [val] to 1
    commit;
   // make an uncommitted update:
    update restab set val=val+10;  // sets [val] to 11
   // raise the exception:
    IF handlable THEN
      signal abc; // raises the user-defined handlable exception
    ELSE
      insert into restab values(11); // raises the "key duplicity" rollback-type exception
    END IF;
    update restab set val=val+1000; // not done because the block had been exited
  END;
  update restab set val=val+10000;  // done unless the exception is resignalled
END

Execution of the Handling procedure has following result:

An Overview of Errors Based on the Possibilities of their Handling

Processing Errors and Exceptions in SQL

Diagnostic Tools