If an error is detected by DB/2 (for example, a SQL syntax error), the first line of the error return contains a description of the error, and the second line contains the code returned by DB/2. These lines are followed by the prompt string. For example, an attempt to execute a SQL command on a database that has a timestamp conflict because the ESL local application has not been bound to that database produces the following error:

ERROR: SQL PREPARE failure ERROR: Return Code: -818 SQL> 

Both lines begin with the configured error string. (In these examples, the default error string "ERROR:" is used.) The application issues the prompt after returning the two error lines. The ESL program can use the prompt to detect whether one or two error messages are delivered. (If the verbose on configuration command has been issued, more than two lines of error information might be displayed.)

The "-818" in the previous errors example is the DB/2 return code. The meaning of each return code is listed in the IBM's  DB/2 reference documentation. Since there are many of these codes that can be returned, we recommend that certain common errors be intercepted by the ESL program.

Errors include the following:

 

Note: Timestamp conflict errors are not generated when the startdb command is issued. An actual database attempt must be made to trigger this error.

An error message is also generated if a column that has been marked "for bit data" appears in a select statement, and the query will not be processed.

The following code provides a framework for recognizing these errors. It assumes the default prompt and error strings, and simply displays the error messages in a textual region.

response to line col 1 "ERROR: " from SQL # respond to the first error line

 add to ErrorWindow

    insert input "\n"
 begin guarded
 # looking for second error line or a prompt

   response to line col 1 "ERROR: " from SQL

     extract from input
        skip to "Code:"
        skip by 5
        take number FailCode
 

 switch FailCode is
    case char -818 is
       copy "TIMESTAMP CONFILCT - PLEASE REBIND\n" to FailMessage 

    # If appropriate, a " binddb" command can be 

    # issued here. However, note that issuing 

    # additional commands to the local application 

    # will result in the application issuing additional 

    # prompts (or error lines) that would need to be 

    # recognized. Also, this message is not issued 

    # when the startdb is called; it occurs when the 

    # first SQL Command is sent.

    case char -1032 is
       copy "PLEASE START DB/2\n" to FailMessage 

    # DB/2 must be running before this 

    # program is started. You may want to exit here.

    case char -1 is 

       copy "BIND FAILED - IS DB/2 STARTED?\n" to FailMessage 

    # If you issue a binddb and DB/2 is not 

    # yet started, you will receive this message before 

    # the error ( -1032) indicating that DB/2

    # has not been started. You may want to 

    # exit here.

    case char -2 is 

       copy "BIND FAILED - PROBLEM WITH THE BINDFILE\n" to FailMessage 

    # Indicates a problem with the bindfile itself; 

    # you may want to call a standard error routine 

    # telling the user to contact the helpdesk.

    case char -31 is 

       copy "BIND FAILED - PROBLEM WITH THE BINDFILE\n" to FailMessage 

    # Indicates a problem with the bindfile itself; 

    # you may want to call a standard error routine 

    # telling the user to contact the helpdesk.

    default is

       copy " ErrorNumber: " FailCode "\n" to FailMessage

 end switch 

 add to ErrorWindow

   insert FailMessage

 begin guarded

   response to line col 1 "SQL> " from SQL

   # looking for the prompt after the second error line

   leave block
 end
 leave block

 response to line col 1 "SQL> " from SQL
 # looking for the prompt after the first error line

    leave block
 end
 make ErrorWindow visible