DOCUMENT #692
=======================================================================
TESTING FOR ERRORS
=======================================================================
PRODUCT: R:BASE VERSION : 3.1 or Higher
=======================================================================
AREA : Programming In R:BASE CATEGORY: Macros & Custom Code
=======================================================================
All well written programs need to be able to check and determine if key
lines are executed or the proper answers are given --- and, if not, then
display an error message or run alternative commands. R:BASE has three
different ways to check the code for proper execution. The three ways
you can process R:BASE errors in your application code are: the R:BASE
ERROR VARIABLE, SQLCODE , and the WHENEVER statement.
Error Variable
==============
The first, and most commonly used, is the ERROR VARIABLE. The ERROR
VARIABLE is a special variable defined to hold the error codes that
R:BASE returns. The first step in using the error variable is to define
the name of the variable. Use the command: SET ERROR VARIABLE errorvar,
where errorvar is the name of the variable that contains the error code.
You can name the error variable any name you want. Only one error
variable is used in an R:BASE session (an R:BASE session is from when
you start R:BASE until you EXIT). The error variable has a value after
every command in a program or R:BASE session. The value is either 0
(successful command) or a number indicating the error that occurred on
the last executed command.
Because the R:BASE error variable is set after every command, when
checking the errorcode the very next line after the command to be
checked must test the error variable or place its value into a holding
variable: SET VARIABLE vhold =.errorvar. The variable vhold is then
used to determine what happens next in the program. There is only one
error variable in a program (errorvar), but there can be many holding
variables (vhold). Holding variables are needed because the error
variable is set after every command. For example, PAUSE is a command
that always executes successfully, so the error variable is always set
to 0 after it.
To turn off an error variable or to use one with a different name you
must use the SET ERROR VARIABLE OFF command. When using an error
variable do not clear it with the CLEAR VAR command. When clearing
variables always use the CLEAR ALL VAR EXCEPT errorvar. You can generate
other errors in your application by clearing an error variable instead
of setting it off.
Error codes trapped by the error variable are not only for SQL commands,
but any command that can be used in R:BASE. This is different from the
other techniques as they only check the commands that are SQL specific.
A useful technique in identifying errors in the code is to place an IF
statement based on the holding variable to redirect the flow of the
code and to provide a message to the user. For example:
SET ERROR VAR verror
CONNECT concomp
SET VAR vhold1 = .verror
IF vhold1 <> 0 THEN
CLS FROM 24,1 TO 24,80 RED
WRITE 'Error connecting database.' AT 24,27 GRAY ON RED
PAUSE 2
RETURN
ENDIF
The command SHOW ERROR vhold displays the R:BASE message that
corresponds to the error number. Some codes and corresponding messages
are:
Error number Message
------------ ---------------------------------------------
6 Database exists
7 Unable to Connect database
9 Illegal database name
42 Not enough space on this disk
93 No database connected
2059 No rows exist or satisfy the specified clause.
To make your own message use WRITE commands or the technique described
in "Making Application Menus & Message More R:BASE Like" from the May/
June 1991 Exchange (document #307 on automated FAX server 206-649-2789).
The error variable is not always set after one of the DOS commands (DIR,
TYPE, REN, DEL etc.) used in R:BASE. One way to check for the existence
of a file is
FILLIN filename_to_test USING 'Enter file name: '
LABEL retest
OUTPUT temp_file
TYPE filename_to_test
SET VAR vhold = .verror
OUTPUT SCREEN
IF vhold = 0 THEN
DIALOG 'File exists. Enter a new name, RETURN to overwrite.' +
vresp,vkey,1
GOTO retest
ENDIF
The TYPE command sets the R:BASE error variable. Note that the error
variable value is transferred to the holding variable immediately after
the TYPE command and before the OUTPUT SCREEN. The OUTPUT SCREEN command
resets the error variable to 0. If the file does not exist, the program
continues with normal processing. If the file does not exist, you get
an error code and can warn the user of the missing file. This does not
work with all DOS commands. If the same syntax is used with a DIR
filename command, for example, it does not return an error code.
SQLCODE
=======
Similar to the R:BASE ERROR VARIABLE is SQLCODE. SQLCODE is the ANSI
SQL Level 2 defined error variable. It works like the R:BASE error
variable in that it is set after every command, but it is only set
after SQL commands, not after every R:BASE command as is the R:BASE
error variable. Also, SQLCODE has specific defined values. It is 0
if the command is successful, 100 if no rows are found, and <0 if the
command fails. When the command fails, SQLCODE is set to the negative
of the R:BASE error code value.
In R:BASE, SQLCODE is a system variable and does not to be defined. It
is always there and is always being set, even if the value is not being
checked by application code.
SQLCODE is used just like the R:BASE error variable. After the command
to be checked, the next line of code either tests SQLCODE or places the
value of SQLCODE into a holding variable to be checked later. In
addition, SQLCODE can be used with the WHENEVER command.
For example:
CONNECT concomp
IF SQLCODE < 0 THEN
CLS FROM 24,1 TO 24,80 RED
WRITE 'Error connecting database.' AT 24,27 GRAY ON RED
PAUSE 2
RETURN
ENDIF
WHENEVER
========
WHENEVER is like a gate; it checks SQLCODE and one continues through
or goes around. This command is similar to the error variable in that
it is placed once in the code and from there is used to check all
subsequent commands. But while the R:BASE error variable is used once
per R:BASE session, WHENEVER is used once per command file or block.
Each command file or block requires a separate WHENEVER command, but
they all share the same R:BASE error variable. There are two parts to
the WHENEVER command: first, determine if an error condition exists;
second, tell the program what to do.
The first part of the WHENEVER clause includes two choices: SQLERROR
which checks SQLCODE for all errors except "data not found" and NOT
FOUND which checks only for a "data not found" error (SQLCODE=100).
Since there can only be one WHENEVER per command file or block, it
checks for data not found errors or for all other errors. This doesn't
mean that you can't check SQLCODE directly in your code for data not
found errors. It means that automatic processing of error handling code
happens only for one or the other.
If the first part of WHENEVER is true, then the second part of the
WHENEVER command is executed. Like the first part, it has two options:
GOTO label or CONTINUE. GOTO label passes program control to the
specified label, usually an error handling routine that displays
appropriate messages and determines the next step in the program.
CONTINUE turns off a previously issued WHENEVER command within the
same command file or block, the GOTO is not processed and the program
continues with the next command line.
One way to use the WHENEVER clause is to place the command, WHENEVER
SQLERROR GOTO errorlab, at the beginning of each command file or block.
If an error occurs at any line in the code, control is transferred to
commands following the label, errorlab. It is not recommended to use
WHENEVER NOT FOUND with DECLARE CURSOR to trap the end of data for the
cursor. Often, cursors are used with UPDATE, INSERT and SELECT commands
that have WHERE clauses. If the WHERE clause returns a "No rows exist
or satisfy the specified clause", the WHENEVER NOT FOUND is triggered
and the GOTO executed even though there may be more rows to be found
with the cursor. Use WHILE SQLCODE <> 100 with DECLARE CURSOR.
SQLERROR does not trap or process NOT FOUND errors, i.e. SQLCODE=100,
it only deals with SQLCODE < 0.
An example:
WHENEVER SQLERROR GOTO errorlab
CONNECT concomp
..... more code here
LABEL errorlab
SWITCH (SQLCODE)
CASE '-7'
CASE '-9'
CLS FROM 24,1 TO 24,80 RED
WRITE 'Error connecting database.' AT 24,27 GRAY ON RED
PAUSE 2
RETURN
BREAK
CASE '-2045'
CLS FROM 24,1 TO 24,80 RED
WRITE 'Command did not execute due to synatx error.' +
AT 24,18 GRAY ON RED
PAUSE 2
GOTO start
BREAK
CASE 'DEFAULT
CLS FROM 24,1 TO 24,80 RED
WRITE 'An error has occurred. See your System Administrator.' +
AT 24,12 GRAY ON RED
PAUSE 2
RETURN
BREAK
ENDSW
WHENEVER is only triggered by errors on SQL commands. It is not
affected by non-SQL commands. Use the R:BASE error variable on non-SQL
commands.
This is not a comprehensive listing of what each error handling method
can do, but a beginner's guide to error checking, how it affects your
programs and some of the possible variations. If you are unsure as to
how to use these in your program, consider the areas or commands that
would cause the most pain and suffering. These areas would be considered
excellent candidates for some error checking. If there are areas that
are still unclear, the next best method of learning is trial and error.
Note that these error handling routines process errors returned by
R:BASE. They do not handle other errors such as:
<> answering NO instead of YES
<> wrong data values are returned
<> wrong data values are entered
<> ESC from menu or dialog box without making a selection
These errors need to be tested for using custom program code.