Wednesday, June 07, 2006

Defining SQL Communications Area in PL/I & COBOL

A PL/I program that contains SQL statements must include one or both of the following host variables:

*An SQLCODE variable, declared as BIN FIXED (31)
*An SQLSTATE variable, declared as CHARACTER(5)

*An SQLCODE variable declared as PIC S9(9) BINARY, PIC S9(9) COMP-4, PIC S9(9) COMP-5, or PICTURE S9(9) COMP
*An SQLSTATE variable declared as PICTURE X(5)

Alternatively, you can include an SQLCA, which contains the SQLCODE and SQLSTATE variables.

DB2 sets the SQLCODE and SQLSTATE values after each SQL statement executes. An application can check these values to determine whether the last SQL statement was successful. All SQL statements in the program must be within the scope of the declaration of the SQLCODE and SQLSTATE variables.
Whether you define the SQLCODE or SQLSTATE variable or an SQLCA in your program depends on whether you specify the precompiler option STDSQL(YES) to conform to SQL standard, or STDSQL(NO) to conform to DB2 rules.

If you specify STDSQL(YES):
When you use the precompiler option STDSQL(YES), do not define an SQLCA. If you do, DB2 ignores your SQLCA, and your SQLCA definition causes compile-time errors.
If you declare an SQLSTATE variable, it must not be an element of a structure. You must declare the host variables SQLCODE and SQLSTATE within the BEGIN DECLARE SECTION and END DECLARE SECTION statements in your program declarations.

If you specify STDSQL(NO):
When you use the precompiler option STDSQL(NO), include an SQLCA explicitly. You can code the SQLCA in a PL/I program, either directly or by using the SQL INCLUDE statement. The SQL INCLUDE statement requests a standard SQLCA declaration:

No comments: