The WHENEVER Statement






The WHENEVER Statement

In Section 26.6, we stated that a value is assigned to SQLCODE after processing each SQL statement. However, this applies only to executable SQL statements, not to the nonexecutable statements. The possible values of SQLCODE can be divided into three groups:

  • The statement has been processed correctly.

  • During the statement, something went wrong. (The statement was probably not executed.)

  • During the statement, a warning appeared. (The statement was executed.)

Ideally, the value of the SQLCODE host variable should be checked after each SQL statement, for example, with an IF-THEN-ELSE statement. However, a large program can consist of hundreds of statements, and this would lead to many IF-THEN-ELSE statements. To avoid this, SQL supports the WHENEVER statement. With the WHENEVER statement, you specify where the program should proceed according to the value of the SQLCODE host variable.

<whenever statement> ::=
   WHENEVER <whenever condition> <whenever action>

<whenever condition> ::=
   SQLWARNING | SQLERROR | NOT FOUND

<whenever action> ::=
   CONTINUE | GOTO <label>


To show how this statement can be used, what it means, and how it actually works, we rewrite the PLAYERS_INDEX example from Section 26.3.

Figure. Develop a program that creates or drops the index on the PLAYERS table, depending on the user's choice.

In the original program, the SQLCODE host variable was not checked. Let us first change this example without using the WHENEVER statement.

PROGRAM PLAYERS_INDEX_2;
DECLARATIONS
   choice : CHAR(1);
BEGIN
   WRITE 'Do you want to create (C) or delete
          (D) the PLAY index ?';
   READ choice;
   # Depending on the choice, create or delete the index
   IF choice = 'C' THEN
      CREATE INDEX PLAY ON PLAYERS (PLAYERNO);
      IF sqlcode >= 0 THEN
         WRITE 'Index PLAY is created!';
      ELSE
         WRITE 'SQL statement is not processed';
         WRITE 'Reason is ', sqlcode;
      ENDIF;
   ELSE IF choice = 'D' THEN
      DROP INDEX PLAY;
      IF sqlcode => 0 THEN
         WRITE 'Index PLAY is deleted!';
      ELSE IF
         WRITE 'SQL statement is not processed';
         WRITE 'Reason is ', sqlcode;
      ENDIF;
   ELSE
      WRITE 'Unknown choice!';
   ENDIF;
END

The program has grown considerably. We now add a WHENEVER statement:

PROGRAM PLAYERS_INDEX_3;
DECLARATIONS
   choice : CHAR(1);
BEGIN
   WHENEVER SQLERROR GOTO STOP;
   WHENEVER SQLWARNING CONTINUE;
   WRITE 'Do you want to create (C) or delete
          (D) the PLAY index?';
   READ choice;
   # Depending on the choice, create or delete the index
   IF choice = 'C' THEN
      CREATE INDEX PLAY ON PLAYERS (PLAYERNO);
      WRITE 'Index PLAY is created!';
   ELSE IF choice = 'D' THEN
      DROP INDEX PLAY;
      WRITE 'Index PLAY is deleted!';
   ELSE
      WRITE 'Unknown choice!';
   ENDIF;

STOP:
   WRITE 'SQL statement is not processed';
   WRITE 'Reason is ', sqlcode;
END

Explanation The effect of the first WHENEVER statement is that when an error occurs during the processing of an SQL statement, the program automatically "jumps" to the label called STOP. This statement replaces the two IF-THEN-ELSE statements in the program PLAYERS_INDEX_2. The effect of the second WHENEVER statement is nil; with this statement, you specify that if the value of the SQLCODE host variable is greater than zero (SQLWARNING), the program should continue.

The WHENEVER statement is a nonexecutable statement, which means that the statement is processed by the precompiler. In other words, the precompiler converts this statement to statements of the host language. The precompiler generates an IF-THEN-ELSE statement for each SQL statement. For example, the precompiler generates the following IF-THEN-ELSE statement for the first WHENEVER statement:

IF sqlcode < 0 GOTO STOP

This IF-THEN-ELSE statement is placed directly behind each SQL statement. No IF-THEN-ELSE statements are generated for the other WHENEVER statement. This is not needed because CONTINUE has been specified.

If a program contains the following three WHENEVER statements:

WHENEVER SQLWARNING GOTO HELP
WHENEVER SQLERROR   GOTO STOP
WHENEVER NOT FOUND  GOTO AGAIN

the following statements are generated and placed behind each SQL statement:

IF sqlcode = 100 GOTO AGAIN
IF sqlcode > 0   GOTO HELP
IF sqlcode < 0   GOTO STOP

WHENEVER statements may be specified in more than one place in a program. A WHENEVER statement is applicable to all SQL statements that follow it, until the end of the program or the next WHENEVER statement.

In practice, some developers make the error of thinking that the precompiler follows the "flow" of the program. This is certainly not true. The precompilers consider a program to be a series of lines. If the line contains an SQL statement, something will be done with it. The precompiler cannot see the difference between, for example, an IF-THEN-ELSE and a WHILE-DO statement. In the following example, we show the kind of logical error that can be made:

BEGIN
   WHENEVER SQLERROR GOTO STOP1;
   :
   WHILE ... DO
      :
      WHENEVER SQLERROR GOTO STOP2;
      UPDATE PENALTIES SET AMOUNT = AMOUNT * 1.05;
      :
   ENDWHILE;
   :
   DELETE FROM TEAMS WHERE TEAMNO = 1;
   :
   STOP1:
   :
   STOP2:
   :
END;

An important question we should ask ourselves is, to which label will the program jump if the DELETE statement fails and the program has not executed the statements within the WHILE-DO statement? You might think that it will jump to label STOP1 because that is the only WHENEVER statement that has been processed. This is not true, however. A precompiler considers a program to be a series of statements without meaning. It is interested only in the SQL statements. The precompiler replaces each WHENEVER statement with IF-THEN-ELSE statements, resulting in the following program:

BEGIN
   :
   WHILE ... DO
      :
      UPDATE PENALTIES SET AMOUNT = AMOUNT * 1.05;
      IF sqlcode < 0 GOTO STOP2;
      :
   ENDWHILE;
   :
   DELETE FROM TEAMS WHERE TEAMNO = 1;
   IF sqlcode < 0 GOTO STOP2;
   :
END;

In other words, if the DELETE statement fails, the program jumps to the STOP2 label, even though the statements within the WHILE-DO statement have not been processed.



 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows