How to Handle SQL Conditions

You can have your program look at SQLSTATE after the execution of every SQL statement. There are several possibilities for what you may want to do next. What do you do with the knowledge that you gain?

  • If you find a class code of 00, you probably don’t want to do anything. You want execution to proceed as you originally planned.

  • If you find a class code of 01 or 02, you may want to take special action. If you expected the “Warning” or “Not Found” indication, you probably want to let execution proceed. If you didn’t expect either of these class codes, you probably want to have execution branch to a procedure that is specifically designed to handle the unexpected, but not totally unanticipated, warning or not found result.

  • If you receive any other class code, something is wrong. You should branch to an exception-handling procedure. Which procedure you choose to branch to depends on the contents of the three subclass characters, as well as the two class characters of SQLSTATE. If multiple different exceptions are possible, there should be an exception-handling procedure for each one because different exceptions often require different responses.

    You may be able to correct some errors or find workarounds. Other errors may be fatal; no one will die, but you may end up having to terminate the application.

Handler declarations

You can put a condition handler within a compound statement. To create a condition handler, you must first declare the condition that it will handle. The condition declared can be some sort of exception, or it can just be something that’s true. Here are some possible conditions.

Condition Description
SQLSTATE VALUE ‘xxyyy’ Specific SQLSTATE value
SQLEXCEPTION SQLSTATE class other than 00, 01, or 02
SQLWARNING SQLSTATE class 01
NOT FOUND SQLSTATE class 02

The following is an example of a condition declaration:

BEGIN
 DECLARE constraint_violation CONDITION
   FOR SQLSTATE VALUE '23000' ;
END ;

This example is not realistic, because typically the SQL statement that may cause the condition to occur — as well as the handler that would be invoked if the condition did occur — would also be enclosed within the BEGIN…END structure.

Handler actions and handler effects

If a condition occurs that invokes a handler, the action specified by the handler executes. This action is an SQL statement, which can be a compound statement. If the handler action completes successfully, then the handler effect executes. The following is a list of the three possible handler effects:

  • CONTINUE: Continue execution immediately after the statement that caused the handler to be invoked.

  • EXIT: Continue execution after the compound statement that contains the handler.

  • UNDO: Undo the work of the previous statements in the compound statement and then continue execution after the statement that contains the handler.

If the handler can correct whatever problem invoked the handler, then the CONTINUE effect may be appropriate. The EXIT effect may be appropriate if the handler didn’t fix the problem, but the changes made to the compound statement do not need to be undone. The UNDO effect is appropriate if you want to return the database to the state it was in before the compound statement started execution.

Consider the following example:

BEGIN ATOMIC
 DECLARE constraint_violation CONDITION
  FOR SQLSTATE VALUE '23000' ;
 DECLARE UNDO HANDLER
  FOR constraint_violation
RESIGNAL ;
 INSERT INTO students (StudentID, Fname, Lname)
  VALUES (:sid, :sfname, :slname) ;
 INSERT INTO roster (ClassID, Class, StudentID)
  VALUES (:cid, :cname, :sid) ;
END ;

If either of the INSERT statements causes a constraint violation, such as trying to add a record with a primary key that duplicates a primary key already in the table, SQLSTATE assumes a value of ‘23000’, thus setting the constraint_violation condition to a true value.

This action causes the handler to UNDO any changes that have been made to any tables by either INSERT command. The RESIGNAL statement transfers control back to the procedure that called the currently executing procedure.

If both INSERT statements execute successfully, execution continues with the statement following the END keyword.

The ATOMIC keyword is mandatory whenever a handler’s effect is UNDO. This is not the case for handlers whose effect is either CONTINUE or EXIT.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.