Handling InterBase Error Codes
Procedures can also handle InterBase error codes. For example, suppose a statement in a procedure attempts to update a row already updated by another transaction, but not yet committed. In this case, the procedure might receive an InterBase error code, isc_lock_conflict. Perhaps if the procedure retries its update, the other transaction may have rolled back its changes and released its locks. By using a WHEN GDSCODE statement, the procedure can handle lock conflict errors and retry its operation.
Example: For example, if a procedure attempts to insert a duplicate value into a column defined as a
PRIMARY KEY, InterBase will return
SQLCODE -803. This error can be handled in a procedure with the following statement:
WHEN SQLCODE -803 DO BEGIN . . .
For example, the following procedure, from an
isql script, includes a
WHEN statement to handle errors that may occur as the procedure runs. If an error occurs and
SQLCODE is as expected, the procedure continues with the new value of B. If not, the procedure cannot handle the error, and rolls back all actions of the procedure, returning the active
CREATE PROCEDURE NUMBERPROC (A INTEGER) RETURNS (B INTEGER) AS BEGIN B = 0; BEGIN UPDATE R SET F1 = F1 + :A; UPDATE R SET F2 = F2 * F2; UPDATE R SET F1 = F1 + :A; WHEN SQLCODE -803 DO B = 1; END EXIT; END;
For more information about InterBase error codes and
SQLCODE values, see Error Codes and Messages.