Every COBOL program containing embedded SQL must have an SQL Communications Area (SQLCA) or the field SQLCODE defined in its Working-Storage Section. This definition is normally accomplished by including the SQLCA copybook provided with
Enterprise Developer. A complete description of the SQLCA structure is provided in the
SQL Reference.
Important: Enterprise Developer does not default to using the XDB SQLCA. This can lead to problems using DSNTIAR. To ensure that
Enterprise Developer uses the XDB SQLCA, you must add the path to the provided XDB copybooks to the
in your project properties. By default, this is
%ProgramFiles(x86)%\Micro Focus\Enterprise Developer\cpylib\xdb
. To do this
The SQLCA holds information on the status of the SQL statement last executed. It is updated after the execution of each EXEC SQL ... END-EXEC block of code.
Information in the SQLCA can be used in many ways. For example:
- You can use it to debug your code.
- You can use it to control the flow of your program by testing its values. For example, when SQLCA indicates an error, you can branch to an error-processing routine. Similarly, you can use it to detect the end-of-data and exit a loop.
The following SQLCA codes are particularly useful:
Table 1.
| Field
|
Description
|
| SQLSTATE
|
The SQL Option Preprocessor provides limited support for DB2-compliant SQLSTATE return codes within COBOL application programs. This limited support is provided on top of already existing support for SQLCODE. SQL Option maps a subset of SQLSTATE values from equivalent SQLCODE return values. SQLSTATE support is limited to applications running against an SQL Option Server in DB2 mode.
If running against the SQL Option Link, support for the full range of SQLSTATE return codes is provided by DB2 directly. (Consult your DB2 documentation.) See the
SQL Option Link User's Guide for a complete listing of SQLSTATE values supported by SQL Option, plus a table mapping error message return codes.
SQLSTATE values provide COBOL application programs with a standardized set of return codes corresponding to common SQL statement error conditions. SQLSTATE values are structured so that application programs can test for specific errors or classes of errors. In an application program, the SQLSTATE is a status parameter (similar to SQLCODE) that indicates either that an SQL statement completed successfully or that an exception condition was raised during execution. Unlike the SQLCODE (which is an integer parameter), the SQLSTATE is a character string parameter which for COBOL language applications is of data type COBOL PICTURE X(5).
|
| SQLCODE
|
The SQLCODE element in SQLCA contains a return code relating to the last SQL statement executed:
- If an error occurred, the SQLCODE in the SQLCA will contain a negative number corresponding to the DB2. The equivalent SQL Option Server error code is returned in the SQLERRD (1) field. See
Error Messages in your
Reference Help for codes and meanings.
- If SQLCODE is 0, the statement executed successfully but may have had warnings (as indicated in the SQLWARN fields).
- A code of +100 indicates no more records.
The SQL Option DSNTIAR facility may be used to convert an SQL return code (from the SQLCODE field in SQLCA) to a character string. See the chapter
DSNTIAR Facility for more information.
|
| SQLERRD(3)
|
The SQLERRD (3) element in SQLCA holds the number of rows that were inserted, updated, or deleted by an SQL statement.
|
| SQLERRML
|
The SQLERRML element in SQLCA is the length of SQLERRMC. If zero, the value of SQLERRMC is not pertinent. For more information, see the description for SQLERRMC.
|
| SQLERRMC
|
The SQLERRMC element in SQLCA holds a character string that describes an error condition. If there is an error, it will contain one or more tokens, separated by X'FF'.
The tokens are substituted for the variables in the descriptions of error messages (for example, the name of a file or table).
|
| SQLWARN0
|
The SQLWARN0 element in SQLCA is a warning flag that indicates if any other SQLWARNn field is signaling a potential problem. If SQLWARN0 is blank, then all other SQLWARNn fields are blank (no warnings exist). If SQLWARN0 contains a W, then at least one of the other SQLWARNn variables has a W. For information on the meanings of the other SQLWARNn fields, see the
SQL Reference manual.
|
