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:
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.
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.
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.