Runtime Errors and Diagnostics

Diagnostic information for SQL errors can be obtained in several ways in CitOESQL.

• The most common method in most embedded SQL applications is via a SQLCA data structure. The SQLCA is supplied by including exec sql include sqlca endexec, where SQLCODE contains a numeric error code and SQLSTATE a 5-character string, both of which identify the error condition. SQLERRMC contains a brief error message and SQLERRML contains the length of the message.

• An application may simply declare SQLCODE and/or SQLSTATE without using an SQLCA if it only requires the type of error.

• SQLERRMC is limited to 70 bytes. A longer and more complete error can be obtained by declaring a PIC X(n) field named MFSQLMESSAGETEXT where ‘n’ can be of any size, but 256 characters is generally sufficient to obtain the complete error message.

  Note

  ODBC error messages start with one or more component names in square brackets followed by the message text. These identify where the error message was detected, for example, by the ODBC Driver Manager or the ODBC driver, or the database server. CitOESQL removes any text in square brackets from SQLERRMC, but not from MFSQLMESSAGETEXT.

• Finally, complete diagnostic information, including the possibility of a SQL error returning more than one error, can be obtained via a GET DIAGNOSTICS statement.

All these methods are demonstrated by the errref.cbl sample application.

• Build the application in the sql samples directory via the following

  command: cobc -x -g -conf=citoesql.conf errref.cbl
  

• Execute the sample via:

  errref (Windows) or ./errref (Linux and Unix)
  

  Note

  errref generates the following common warning and error conditions:

  • No data found or returned.
  • Too many rows returned for SELECT INTO.
  • Character data truncation.
  • Attempt to insert a row with a duplicate key.

Open the erref.cbl file and inspect the code to see how the output was generated using the methods described above.

You will need the output of the application for the next tutorial

Diagnostic mapping for database migration

When migrating an application from one database to another you will need to address differences in SQL syntax between the two systems.

It may also be required to address cases where application logic or operation procedures have dependencies on the error diagnostics returned by the database. CitOESQL can assist this process by allowing diagnostics returned by the new database for a given error condition to be mapped to the diagnostics returned by the old database system, thus avoiding the need to make changes to the source code. This is achieved by use of an error mapping file. Errors can be mapped for an application via the ERRORMAP precompiler directive, or for the current connection by executing a SET ERRORMAP statement. These methods are demonstrated by the errmap.cbl sample.

• Navigate to the sql samples directory.

• Open login.cpy in a text editor of your choice and edit the svr and usrpass fields to match your ODBC DSN, User ID and Password. If using Operating System authentication usrpass should be SPACES. Save the file.

• Open errmap.dir and note the directive errmap=test. This means that at runtime errmap will load mapping information from a file named test.emap.

• Open test.emap. This is set up to map diagnostics returned by PostgreSQL. You can use the output from errref to update this file for other databases.

  Note

  Error mapping can match diagnostic information to be mapped by any combination of SQLCODE, SQLSTATE and a substring within the error message text. The mapping process will change SQLCODE and SQLSTATE to new values; specify the original values to leave them unchanged. The error message text can be:

  • Left unchanged by omitting it from the mapping
  • Replaced by new text
  • supressed (i.e set to spaces by specifying a single ~ character as the replacement text)

• Replacement error messages start with [test]. This will appear in message text returned in CITSQLMESSAGETEXT and by GET DIAGNOSTICS, but not in SQLERRMC. It is there as a simple visual check that an error has been mapped successfully.

• Open conn.emap. This is very similar to test.emap, but will be used with a SET ERRORMAP statement rather than the ERRORMAP directive. Replacement error messages start with [conn].

• Set the environment variable CIT_ERRORMAP_PATH to the current directory. This can be ‘.’. This will cause CitOESQL to look for the mapping files in the current directory and is useful for testing new mapping files. If not set, the default location of %COBOLITDIR%\etc (Windows) or \$COBOLITDIR/etc (Linux) will be used.

• Compile and run errormap.

  cobc -g -x -conf=citoesql.conf errmap.cbl
  errmap
  

• Inspect the output and verify that errors have been mapped.

• Open errmap.cbl and inspect the code.

To deploy error mapping files for general use:

• Edit the files and remove any unwanted leading text in square brackets at the start of replacement error messages.

• Copy the files to the default location: %COBOLITDIR%\etc (Windows) or $COBOLITDIR/etc (Linux)

• Unset environment variable CIT_ERRORMAP_PATH (if set).

COBOL-IT CitOESQL files and locations

Performance and Diagnostic Aids

CitOESQL provides two execution tracing capabilities that may help you in diagnosing bugs and performance issues. Both are controlled by directives.

Setting ODBCTRACE=ALWAYS will enable ODBC tracing in the ODBC driver manager. This can also be done via the ODBC Administrator (Windows) or by editing the ODBC configuration files odbc.ini and odbcinst.ini (Linux), however when developing or debugging an application you may find the precompiler directive more convenient. Trace information is appended to the trace file if it already exists, so you must remember to delete or clear the trace file between runs. ODBCTRACE traces ODBC entry to and exit from ODBC API calls along with diagnostic error messages. ODBCTRACE writes all trace records to disk immediately and consequently this can have a significant impact on performance.

TRACELEVEL traces the calls an application makes to the CitOESQL’s runtime library. The resultant traces can be used to analyse performance issues. TRACELEVEL offers several levels of detail and records directive settings and tuning statistics, such as the number of rows read by a cursor. TRACELEVEL has significantly less performance impact than ODBCTRACE but if an application terminates abnormally not all trace events may be recorded.