SQL Communications Area (SQLCA)

Restriction: This topic applies to Windows environments only.

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 Linked Resources list in your project properties. By default, this is %ProgramFiles(x86)%\Micro Focus\Enterprise Developer\cpylib\xdb (Windows) or $COBDIR/copylib/xdb (UNIX). To do this, see the section To link to a folder containing copybooks in the topic To link to copybooks or sources outside the project. Be sure to specify the path to the XDB copybook files, and be sure this path appears at the top of the list of linked resources.

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 (applies to Windows environments only). (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.