Error Handling Functions in Informix Programs

Published on April 1, 1996 by Lester Knutsen

First published in the Washington Area Informix User Group Newsletter
Volume 6, No. 2 - April 1996

This article defines some guidelines for error handling functions. An error handling function is the code that responds to a user or system generated error. There are two classes of errors: fatal errors and non-fatal errors. Events that prevent the continued operation of a program are fatal errors. An example can be a missing data file required for input. No processing may take place without this file and the program must abort. Events that can be corrected so that the program can continue are non-fatal. An example is a record locked by another user for update. The current process can wait or continue processing other records. This event does not require the process to abort.

Informix documents all events which can cause an error in the publication "Informix Error Messages". This is shipped with each product. Each error event is list in this document with its unique number and a description of the error.

Error Handling

Computer programs need a process for dealing with unexpected events that cause system failures. This process is the error handling function. There are two types or classes of errors; fatal errors and non-fatal alert messages. An error that prevents the continued processing of a program is a fatal error. An error that can be corrected so that the process can continue is a non-fatal error. Every program should have a logging process so that all errors are logged and all critical information can be reviewed at a later date.

1. Fatal Errors

When a fatal error occurs, a program needs to perform the following actions: inform the user or operator of the problem, perform any required post processing clean-up, log critical information about the error, and abort processing. There are also circumstances when a program may not be able to perform these actions. A power outage is an example of an error which causes all programs to completely stop without time for error handling to be performed. However, every program should have these error handling functions for errors that can be trapped and processed.

Operator Notification

The user or operator running the program needs to be notified that an error has occurred. The program should stop and require the user to read an error message before scrolling or clearing any messages off the screen. The error message should contain the Informix error number and a brief description of the error. Any information that the operator requires to correct the error should also be displayed. If the process is run in the background, a fatal error must be sent to a system assigned log.

Processing Clean-up

The program must halt all processing and, as much as possible, return the environment to the state before the program started. All temporary files or database tables must be removed. Any transactions that have not been committed must be rolled back.

Error Log

The program needs an error log. The log should contain some basic information about a successful run such as the date and time, the operator id, and any options selected by the operator. When an error occurs, the program should write an error message to the log. The error message needs to include the Informix error number and a one-line error text description. The log should contain the module name, line number of code, and any other information that is required to correct the error.

Abort Processing

A program needs to exit when it encounters an error event. The worst thing a program can do is to hang or continue to attempt to process in an endless loop.

Abort without Error Handling

There may be cases when a program cannot continue processing to perform an error handling function. A power or network outage where a program is killed will abort all error handling.

2. Non-Fatal Errors

Some error events are not fatal to a program. Every program needs an error handling processes to deal with non-fatal errors. The program requirements and design must identify what error events each program must be able to handle, and how it will handle each event. An example of a non-fatal error is a locked record. Before a program updates a record it must lock the record. If the record is already locked by another process, the new lock attempt will fail. This will cause an Informix SQL error. The program can wait for the record to be released or continue processing other records. The program must have a process to handle this type of error. The design specifications must describe what non-fatal errors should be trapped and how the program will respond to the error.

Partial List of Non-Fatal Informix Errors

The following is a partial list of Informix error events that are usually not fatal and can be planned for in the program design.

Error Number and Description
 100 Record Not Found.
-100 ISAM error: duplicate value for a record with unique key.
-107 ISAM error: record is locked.
-233 Cannot read record that is locked by another user.
-239 Could not insert new row - duplicate value in a UNIQUE INDEX column.
-250 Cannot read record from file for update.
-263 Could not lock row for UPDATE.
-271 Could not insert new row into the table.
-272 No SELECT permission.
-273 No UPDATE permission.
-274 No DELETE permission.
-275 No INSERT permission.
-288 Table table-name not locked by current user.
-289 Cannot lock table table-name in requested mode.
-329 Database not found or no system permission.
-378 Record currently locked by another user.
-387 No connect permission.
-908 Attempt to connect to database server failed.

Processing Non-fatal Errors

When an event occurs which is defined as a non-fatal error, the program should perform the following tasks: inform the operator of the event, perform any required event processing clean-up, log information about the event, and continue processing.

Informix SQL Errors

The Informix OnLine Database Engine maintains the status of every SQL statement in a data structure, SQLCA, that is available to programs. A program must check the result of SQLCA.SQLCODE after every database statement. A value of 0 indicates that there was no error. A value of 100 indicates that a record was not found. Negative numbers indicate an SQL error occurred.

Informix 4GL Error Handling

Informix 4GL maintains error information in two global variables: STATUS and SQLCA.SQLCODE. A negative number indicates that an error occurred. The 4GL program code will respond to an error depending on how the statement "WHENEVER ERROR" has been set in the program. The programmer must check these global variables after every critical statement to determine if an error event occurred and take the appropriate action.

The WHENEVER ERROR statement

The WHENEVER ERROR statement determines how a program will respond to an error. A program can have multiple WHENEVER ERROR statements so that different sections of code can trap and respond to errors differently. There are four options for this statement. The first option "WHENEVER ERROR GOTO" should never be used. The option "WHENEVER ERROR CALL function_name" should be used when one error handling function can meet all the requirements of a program. However, most programs require different error handling functions for different sections of code. The option "WHENEVER ERROR CONTINUE" is the recommended default for all programs. This can be defined once in each module. When this option is set the 4GL program must check for errors after every critical statement and take appropriate action. The 4GL default is "WHENEVER ERROR STOP". This causes the 4GL program to display an error message and abort. This is recommended for sections of code where error handling has not been defined, as it prevents the program from hanging without displaying any error message.

The Global Variables STATUS and SQLCA.SQLCODE

Informix 4GL sets the global variable STATUS to equal zero or the last error code after every statement. The SQLCA.SQLCODE is set after every database statement. Informix 4GL programs must check these global variables after every critical statement. Which variable is checked is very important. The SQLCA.SQLCODE variable must be checked after every database statement.

The STATUS variable is very dynamic and its value changes after each statement. This can cause mis-informed results. The successful display of a message after an earlier error will set status back to zero. This will lose the original error number. The following is an example:

1 OPEN FORM new_form FROM "new_form.frm"

When an error occurs, STATUS is set to a negative number. The "DISPLAY" statement in line three will reset status to zero if it is successful. Line four will display zero and not the original error number. Every program must check STATUS and save its value if it needs to be used later.

Displaying Error Messages in Informix 4GL

Informix 4GL provides two statements to display error messages. The "ERROR text" statement prints a programmer defined text to the bottom line of the screen and rings the terminal bell. This statement is used when the programmer has a planned error message. The "ERR_PRINT()" function takes the error number as an argument and prints the Informix error message text on the bottom line of the screen and rings the terminal bell. This function is used when an error has not been predetermined. The following are two examples:

Example 1:

ERROR "This is the programmers error message"
CALL error_clean_up()

Example 2:

CALL error_clean_up()

Informix 4GL Error Logging

Three levels of application error logging are required in Informix 4GL programming. The first level is to log what the operator is doing so that when a user calls with a problem, the support group can identify and review the functions. This is also a good way to find out what actually gets used in an application, and what does not get used. The second level is to log all errors within the program. The third is to log more detailed debugging information while a program is under development. The log could be to a file, to a network message system, or to a backup device. The log needs to contain the 4GL module name, the version, the line number in the source code that caused the error, and a message.

Logging Functions

Informix 4GL has two functions to create and write to a log file. The function startlog("logname") creates a log file. The function errorlog() writes to the log file. This can be used to save SQL error messages, informational messages about the application, and debugging messages.

WHENEVER ERROR Statement and Logging

Informix 4GL uses a source statement "whenever error" to determine how to handle a fatal error in a program. The default is "WHENEVER ERROR STOP", which causes the program to abort. This does not allow for any post processing clean-up. The Informix database will rollback any uncommitted transactions. The recommended mode is "WHENEVER ERROR CONTINUE". This allows the program to continue and perform any required clean-up functions, but places the responsibility of planning for error handling on the designer.

When the state of the "WHENEVER ERROR" option in a program is to stop, Informix 4GL will automatically write to an open error log the Informix error that causes a program to abort. The log will include the source code line number and module name. However, because the program aborts, it does not allow you to do any clean-up that may be required.

Informix 4GL Error Logging Example

This example Informix error logging function, includes the source code name, line number, error number and message. By setting the option "whenever error continue" in a program, this function allows the program to handle and recover from errors. The function uses features of the Unix Source Code Control System (SCCS) to write a module name, version and line number to the log file.
The following is a description of an Informix error logging function and some examples. When the error log function is called, a number (code), a string containing some SCCS information, and a programmer-supplied message is passed to it. The SCCS information includes the module name, SCCS version, and line number in the source file that generated the function call. Examples A and B show lines of Informix 4GL code calling the error logging function.

Example A:

Error logging function when checked out of SCCS for editing (get -e filename):
call errorlog( code, "MOD:%M% REL:%I% LINE:%C% :",message )

Example B:

Error logging function when checked out of SCCS for compiling (get filename):
call errorlog(code,"MOD:errorlog.4gl REL:1.5 LINE:52:",message)

The first argument is a variable named code which defines the type of message. This function has three types of messages.
The first type of message is a program error. If the number is negative, then it is assumed it is an Informix error message. By replacing the variable "code" in the above function with the SQL error code (sqlca.sqlcode) or status, an error message will always print in the log if there is an error, and nothing will print if there is no error. The second type of message is informational. The number 1000 is used for messages that need to be written to the log to indicate what is happening in the program. The third type of message is for debugging only. The number 0 is used to code messages that only get written to the log when debugging is turned on. To turn debugging on or off, one variable in the function, debug_flag, needs to be changed.

The second argument is a string containing the SCCS module name, version and line number. When the source code is checked out of SCCS using the get command, the %M% is replaced by the module file name, the %I% by the SCCS version, and the %C% by the line number within the file. This allows the programmer to quickly find the file and line number that caused an error. See the UNIX documentation for more information on SCCS.

The third argument is a programmer supplied message. This allows entry of messages like "preparing.." or "open form new.frm" in the log file to track what the program is doing. The following are some examples of how this function can be used.

Example C:

To start and end a program with a message to the log saying that the program
started or ended, use a function call like:
call errorlog(1000,"MOD:%M% REL:%I% LINE:%C% :","Starting Program")

Example D:

After every SQL statement, put a function call to check for errors in addition
to whatever error handling is in the program. If debugging is turned off, these
will only print if there is an error.
call errorlog(status,"MOD:%M% REL:%I% LINE:%C% :","SQL Error ")
call errorlog(sqlca.sqlcode,"MOD:%M%REL:%I% LINE:%C%:","SQL Error")

Example E:

For debugging, the following statement can be entered anywhere in the program
that a message would be helpful. It will print only when debugging is turned on.
call errorlog(0,"MOD:%M% REL:%I% LINE:%C% :","Debugging Message")

Example F: This is the Informix 4GL code for the function:

# Copyright 1993-1996 Advanced DataTools Corporation
# Module: %W% Date: %D%
# Author: Lester B. Knutsen
# Description: General Informix 4GL Logging function
function errorlog(code,relid,msg)
        code integer, # message type
        relid char(40), # SCCS filename, release and line number
        msg char(60), # application message passed to function
        msgline char(200),# message output to log
        debug_flag integer # set level of error logging

whenever error continue # keep going if there is an error

# set the level of debugging for messages to appear in the log
# one of the following must be uncommented

let debug_flag = true     # turn on debugging - all messages will appear in the log

# let debug_flag = false  # turn off debugging - only sql error
                          # messages or messages when code is 1000
                          # will appear in the log

        when ( code = 1000 ) # always write messages to the log
                let msgline = "MESSG: ",code using "------& ", relid ,
                msg clipped
                call errorlog(msgline)
         when ( code < 0 )  # always write errors to the log 
                let msgline = "ERROR: ",code using "------& ", relid , 
                msg clipped, "\n", err_get(code) 
                call errorlog(msgline) 
         when ( code >= 0 and debug_flag = true )  # only when debugging
                let msgline = "DEBUG: ",code using "------& ", relid ,
                msg clipped
                call errorlog(msgline)
end case
end function


Example G: This is an example Informix 4GL program showing how you could use the error logging functions.

    whenever error continue # keep going if there is an error
    call startlog("program.log") # start the error log

    # example that will always create a message to the log
    call errorlog(1000,"MOD:%M% REL:%I% LINE:%C% :",
    "Message 1 - Starting Program")

    # example that will only create a message if debugging is true
    call errorlog(0,"MOD:%M% REL:%I% LINE:%C% :",
    "Message 2 - Debugging Message")

    # example that will only create a message if their is a real
    # error or debugging is on
    call errorlog(status,"MOD:%M% REL:%I% LINE:%C% :",
    "Message 3 - Error ")
    # or
    call errorlog(sqlca.sqlcode,"MOD:%M% REL:%I% LINE:%C% :",
    "Message 3 - Error ")

    # example that will always create a message to the log
    call errorlog(1000,"MOD:%M% REL:%I% LINE:%C% :",
    "Message 4 - Tracking Message")
end main