Progress
Embedded SQL-92
Guide and Reference


Branching Under Exceptions

The main advantage of the WHENEVER statement is its capability to branch under exceptions. The syntax for the WHENEVER statement shows how you specify branching in response to an exception condition:

SYNTAX
EXEC SQL
   WHENEVER { NOT FOUND | SQLERROR | SQLWARNING }
   { { GOTO host_language_label | GO TO host_language_label
        | STOP | CONTINUE }  } ; 

These are the exception conditions for which you can specify branching:

NOT FOUND

The exception NOT FOUND is set when SQLCODE is set to SQL_NOT_FOUND.

SQLERROR

The exception SQLERROR is set when SQLCODE is set to negative.

SQLWARNING

The exception SQLWARNING is set when SQLWARN[0] is set to 'W'.

GOTO | GO TO host_language_label

Specifying the GOTO host_language_label or GO TO host_language_label causes control to pass to the statement at that label.

STOP

STOP terminates the program.

CONTINUE

CONTINUE ignores the SQLCA status and control passes to the next statement in the program.

EXAMPLE

The following code fragment uses the WHENEVER statement for exception handling in an ESQL program that deletes a row:

EXEC SQL CONNECT TO DEFAULT ; 
  
/* 
** At a SQLERROR or NOT FOUND exception, branch to label do_rollback 
*/ 
  
EXEC SQL WHENEVER SQLERROR GOTO do_rollback ;    
  
EXEC SQL WHENEVER NOT FOUND GOTO do_rollback ;     
  
/* 
** Assign a value to cust_no_v 
*/ 
cust_no_v = 1002 ; 
EXEC SQL 
     DELETE 
     FROM customer 
     WHERE cust_no = :cust_no_v ; 
  
/* 
** Successful delete; Commit work, disconnect, go home 
*/ 
EXEC SQL COMMIT WORK ; 
EXEC SQL DISCONNECT DEFAULT ; 
exit (0) ; 
  
do_rollback: 
if (sqlca.sqlcode == SQL_NOT_FOUND) 
     printf ("Customer number not found in table\n"); 
else 
     if (sqlca.sqlcode < 0) 
     { 
          strncpy (errmesg, sqlca.sqlerrm, sqlca.sqlerrml); 
          errmesg [sqlca.sqlerrml] = '\0' ; 
          printf ("Error : %s\n", errmesg); 
     } 
  
EXEC SQL WHENEVER SQLERROR CONTINUE ; 
  
EXEC SQL ROLLBACK WORK ; 
  
EXEC SQL DISCONNECT DEFAULT ; 
exit (1) ; 

You should specify CONTINUE in the WHENEVER statement in the exception handling code. This prevents the WHENEVER statement from passing control to the same label, which results in a program loop if any of the SQL statements within the label fail.

Use either CONTINUE or GOTO with the WHENEVER statement, rather than STOP. Although STOP is valid, using it terminates the program without any final reporting.

Handle the SQL_NOT_FOUND Condition

When you issue a FETCH operation and there are no more rows to be fetched, the database sets SQL_NOT_FOUND in the SQLCA. When your application evaluates the SQLCA and finds SQL_NOT_FOUND, your application should close the cursor.

An UPDATE or DELETE operation can return the SQL_NOT_FOUND when no rows are updated or deleted because there are no rows to satisfy the conditions in the WHERE clause.

Using WHENEVER with Explicit Error Checking

In combination with the WHENEVER statement, your application can explicitly evaluate the SQLCA.

EXAMPLE

The code fragment in the next example illustrates explicitly evaluating the SQLCA:

EXEC SQL 
     WHENEVER SQLERROR GOTO do_rollback ; 
/* 
** Fetch rows and return result values into host variables 
*/ 
for (;;) 
{ 
     EXEC SQL FETCH cust_cur INTO :cust_no_v, :name_v, :city_v ; 
     if (sqlca.sqlcode == SQL_NOT_FOUND) 
          break ; 
}    


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095