Chapter 3: Creating and Embedding SQL in COBOL

There are certain requirements your COBOL programs must meet before they can be compiled and debugged. This chapter outlines the processes for creating or modifying SQL code for COBOL programs. It also details the statements that all COBOL programs containing embedded SQL must contain.

Use of dynamic SQL in your program is described in the chapter Using Dynamic SQL in COBOL.

Creating COBOL Code with Embedded SQL

You can use any appropriate text editor to write your COBOL code and embed SQL in it. Be sure that your embedded SQL contains the elements listed below. If you are using code from DB2 on a mainframe, it will not have to be modified.

EXEC SQL ... END-EXEC Blocks. Every SQL statement in your program must be included within an EXEC SQL ... END-EXEC block as shown below:
```
EXEC SQL  SQL Statement END-EXEC 
```
For example:
```
EXEC SQL 
    DECLARE EMPCURS CURSOR 
      FOR SELECT LNAME, FNAME, PAYRATE, HOURS 
      FROM EMPLOYEE WHERE DEPT = :DEPT-NUM 
END-EXEC
```
Each EXEC SQL ... END-EXEC block must contain only one SQL statement. If the SQL command is not terminated by an END-EXEC operand, a COBOL compiler error will be generated.
Host Variables. Use host variables to store data that is accessed by both SQL and COBOL statements.
Copybooks. Use the EXEC SQL INCLUDE statement to include copybooks containing definitions for tables. You can use the SQL Option Declaration Generator to create a copybook for a table. See the SQL Option Declaration Generator User's Guide for specific procedures.
SQL Communications Area. Define an SQL Communications Area (SQLCA) in your COBOL program or include the SQLCA copybook, which contains the structure of the SQLCA. Create error-checking routines that use the SQLCA to control the flow of the program. For more information, see the section SQL Communications Area (SQLCA) .
SQL Descriptor Area. If your program uses dynamic SQL, you must have an SQL Descriptor Area (SQLDA) defined in your program. We recommend including the SQLDA copybook, provided with your COBOL system, which contains the structure of the SQLDA. Use host variables to get information stored in the various elements of the SQLDA. For more information about using the SQLDA, see the section SQL Descriptor Area (SQLDA) and the chapter Using Dynamic SQL in COBOL.

Using Copybooks

In a typical development effort, different COBOL programs access the same data tables. Copybooks allow host variable definitions for a table's columns to be inserted within your COBOL code, allowing data from those columns to be used in your program. If a modification is made to a table (such as a change of data type or column length), then it is only necessary to modify the copybook rather than the source code definition for that table in all the programs that use it.

Creating a Copybook

You can use the SQL Option Declaration Generator to create declarations of your tables. See the SQL Option Declaration Generator User's Guide for specific procedures.

Copybook Format

The format of a copybook is shown by the following example. This declaration defines the SQL Option EMPLOYEE table in the TUTORIAL location:

 01 EMP-TABLE.
     03 ENO PIC S9(4) COMP.   
     03 LNAME PIC X(10). 
     03 FNAME PIC X(10). 
     03 STREET PIC X(20).
     03 CITY PIC X(15).
     03 ST PIC XX.
     03 ZIP PIC X(5).
     03 DEPT PIC X(4).
     03 PAYRATE PIC S9(13)V99 COMP-3.
     03 COM PIC S9V99 COMP-3.

All sample tables are shown in the SQL Reference manual.

Including Copybooks in Programs

When including a copybook in your program, make sure that the Compiler can locate the copybook.

If a copybook contains embedded SQL or host variable declarations, include it using one of the following three methods:

EXEC SQL INCLUDE ... END-EXEC

For example:

EXEC-SQL 
    INCLUDE EMPREC 
END-EXEC

COPY

For example:

COPY EMPREC.

The file named after the COPY verb can contain any COBOL statements that a copybook can, including EXEC SQL statements.

The SQL Option preprocessor works like the mainframe DB2 preprocessor does. However with the SQL Option preprocessor, you need to specify the preprocessor directive COPY to allow host variables to be defined in COPY statements.

Note: If you specify EXEC SQL statements using the COPY verb, the SQL Option preprocessor will compile the program and will generate an error message for any host variable referenced from the copybook without the COPY directive being set. If you port the same program to the mainframe, the SQL code may not precompile unless your installation has a product that expands copybooks before passing them to the DB2 mainframe preprocessor.

-INC

For example:

-INC EMPREC

The file specified after the -INC verb can contain any COBOL statements that a copybook can, including EXEC SQL statements.

Host Variables

Host variables are used to hold data that is accessed by both COBOL and SQL commands. You define a host variable as a data structure that represents the column names and data types of the tables that are accessed by the COBOL program.

Host variables are usually defined in copybooks. Copybooks can be quickly created from any SQL Option or DB2 table using the SQL Option Declaration Generator utility. See the SQL Option Declaration Generator User's Guide for specific procedures.

There are two ways to use host variables:

To store information returned by the database engine as the result of an SQL statement.
To store information sent to the database engine and/or dynamic information that controls what is returned by a SELECT statement.

All host variables appearing inside an EXEC SQL ... END-EXEC block should be preceded by a colon, as shown for the host variable DEPT-NUM in the following example:

 EXEC SQL 
     DECLARE EMPCURS CURSOR FOR 
     SELECT LNAME, FNAME, PAYRATE, HOURS 
       FROM EMPLOYEE 
       WHERE DEPT = :DEPT-NUM 
 END-EXEC

DB2 V5 does not require all host variables to be preceded by a colon, but DB2 V6 does. If you plan to upgrade in the future, you should set your message level to WARNING in the build setting for every SQL programs in the project.

The use of host variables allows you to design more flexible queries, and to interactively define the criteria for data selection. In the preceding example, the host variable specifies the department for which data will be selected.

For information about null values in host variables, see the section Using Indicator Variables .

Representing Data

When you access data with SQL statements in COBOL programs, you must provide corresponding data definitions in your COBOL source code. This requirement is usually accomplished by stating the data definitions in a copybook and including that copybook in your COBOL source code.

The copybook EMPREC contains a COBOL representation of the column names and data types for the EMPLOYEE sample table. The Declaration Generator utility generates copybooks from tables, automatically creating a COBOL data definition for each table column. You can modify the generated copybook file as needed. See the SQL Option Declaration Generator User's Guide for more information about using the SQL Option Declaration Generator.

Caution: The SQL Option Precompiler does not accept all COBOL data types. If the COBOL data item you code is not consistent with those shown in the section Allowed Data Declarations, it will not be recognized as a host variable.

The following guidelines are offered to assist you in working with host variables and data declarations.

For data declarations of host variables use level number 01 to 77.
A varying-length character string data item must have group level 01. The group contains two, level 49 elementary items consisting of:
- Length Item.
  The first elementary item must be a 2-byte integer variable; for example, PICTURE S9(4) COMP. It represents the length of the character string.
- Value Item.
  The second elementary item must have the same description as a fixed-length character string; for example, PICTURE X(80), where 80 is the maximum length of the string. This item is used to contain the value of the character string. If you use the host variable to insert a character string into a table, the database manager inserts only as many characters as are indicated by the length item. Also, when data is placed into the field, only the number of characters defined in the length item is returned. This can cause previous information stored in this field not to be cleared.
The data item and elementary items can have any acceptable COBOL name. However, in SQL statements, refer to this host variable by its group name.
COBOL host variables used in SQL statements must be of a type compatible with the database columns with which they are to be used.
- Numeric data types are compatible with each other. A SMALLINT, INTEGER, DECIMAL, or FLOAT column is compatible with a COBOL host variable of COMP, COMP-2, COMP-3, COMP-4, or COMP-5.
- Character data types such as CHAR and VARCHAR are compatible with each other. The database manager automatically converts a fixed-length character string to a varying-length string, or vice versa, when necessary.
Be careful of overflow. For example, if you retrieve an INTEGER column value into a PICTURE S9(4) host variable and the column value is larger than 32767 or smaller than -32768, you will get an overflow error.
Be careful of truncation. For example, if you retrieve an 80-character CHAR column value into a PICTURE X(70) host variable, the rightmost 10 characters of the retrieved string will not be included.
Retrieving a floating-point or decimal column value into a COMP host variable removes any fractional value.
The COBOL declarations for SMALLINT and INTEGER data types are expressed as a number of decimal digits. The database manager uses the full size of the binary integers, and may place larger values than would be allowed in the specified number of digits in the COBOL declaration. This situation can cause data truncation. Ensure that the size of any numbers in your application is within the allowable number of digits.
The SQL Option preprocessor also supports using host variables defined with the SQL TYPE IS declaration. The actual data definition for the host variable is then determined by the preprocessor. For example, a host variable for a RESULT SET LOCATOR could be defined as:
```
01 my-locator usage SQL TYPE IS RESULT-SET-LOCATOR varying. 
```

Allowed Data Declarations

The following table shows the COBOL declarations that are allowed for defining host variables. All other types of COBOL declarations will be rejected. Allowed abbreviations are shown in the section Allowed Data Declaration Abbreviations.

Data Type	COBOL Data Declaration	Comments
char	01 identifier PIC X(n). 01 identifier PICTURE IS X(n).	n is a positive integer representing the length of the CHAR column value assigned to the host variable.
varchar	01 identifier 49 identifier PIC S9(ni) COMP. 49 identifier PIC X(nc).	ni is a positive integer from 1 to 4, representing the length of the numeric column value assigned to the variable containing the string length. nc is a positive integer equal to the length of the largest VARCHAR column value that can be assigned to the host variable.
smallint	01 identifier PIC S9(4) COMP. 01 identifier PIC S9(n) COMP. 01 identifier PIC S9(n) USAGE IS COMP.	n is a positive integer from 1 to 4, representing the variable length. No decimal points are allowed. Anything other than 9s in the Picture clause makes data item unacceptable as a host variable. PIC S and COMP are required. You may use COMP-4 or COMP-5 in place of COMP if necessary.
integer	01 identifier PIC S9(9) COMP. 01 identifier PIC S9(n) COMP. 01 identifier PIC S9(n) USAGE IS COMP.	n is a positive integer from 5 to 9. No decimal point is allowed. Anything other than 9s in the Picture clause will make the data item unacceptable as a host variable. PIC S and COMP are required. You may use COMP-4 or COMP-5 in place of COMP if necessary.
decimal	01 identifier PIC S9V COMP-3. 01 identifier PIC S9(n) V COMP-3. 01 identifier PIC S9(n) V9(m) COMP-3. 01 identifier PIC S9(n) V9(m) USAGE IS COMP-3.	n and m are positive integers; n + m cannot exceed 15. You must include a V to denote the decimal point. Anything other than 9s and a V in the Picture clause will make the data item unacceptable as a host variable. PIC S and COMP-3. are required.
float	01 identifier COMP-2. 01 identifier USAGE IS COMP-3.	COMP-2. or COMP-3. is required.
date	01 identifier PIC X(n). 01 identifier PICTURE IS X(n).	n must be an integer representing the length of the date string. For example, if the date string is in the format MM/DD/YY, n may equal 8. If in the format MM/DD/YYYY, n should equal 10. Make sure your date format is set correctly in the OptionsUser Profile utility. See the Option utility's online help for information about modifying the date/time representation.
time	01 identifier PIC X(n). 01 identifier PICTURE IS X(n).	n must be an integer representing the length of the time string. To include seconds, n must be at least 8. For example, a time string in the format HH:MM requires that n equal at least 5, while a time string in the format HH:MM:SS requires that n equal at least 8.
result set locator	01 identifier SQL TYPE IS RESULT-SET- LOCATOR.	A 4-byte value used by DB2 to uniquely identify a query result set returned by a stored procedure.

Allowed Data Declaration Abbreviations

The following table shows the COBOL abbreviations allowed for host variables. For a complete list of allowed data types, see the section Allowed Data Declarations.

Abbreviation	Can be used for...
PIC	PICTURE or PICTURE IS
COMP	COMPUTATIONAL or USAGE IS COMPUTATIONAL
PIC IS	PICTURE IS
USAGE	USAGE IS
S9(5)	S99999
X(10)	XXXXXXXXXX

Using Indicator Variables

An indicator variable is a 2-byte integer variable that can be used for either of the following purposes:

To indicate whether its associated host variable has been assigned a null value. If the value for the column you are retrieving is null, a negative value is placed in the indicator variable.
To verify that a retrieved character string value has not been truncated. If the indicator variable contains a positive integer, it specifies the original length of the string.

When a column's value is retrieved, you can test the indicator variable. If the indicator variable's value is less than zero, you know the retrieved column's value is null. When a null value is retrieved, nothing is put into the associated host variable that is used to contain the column's value; thus, the value of the host variable is unchanged.

Define indicator variables in the same manner as small-integer host variables. Specify an indicator variable, preceded by a colon, immediately after the host variable to which it relates. For example:

 EXEC SQL 
     SELECT PHONENO INTO :CBLPHONE :INDNULL FROM CORPDATA 
       WHERE EMPNO = "EMPID" 
 END-EXEC

You can then test INDNULL to see if it contains a negative value. If it does contain a negative value, then you know that a null value was retrieved for the row's PHONENO (that is, phone number) column, and you can disregard the contents of CBLPHONE.

When a column value is fetched using a cursor, you can use the same technique to determine whether the retrieved value is null or not.

You can also use an indicator variable to set a null value in a column. When processing UPDATE statements, the indicator variable is checked. If the indicator variable contains a negative value, set the column value to "null value." If the indicator variable is greater than -1, the associated host variable contains a value for the column.

Using Directives

Directives are compiler options that convey information to your compiler and the SQL Option Precompiler. For example, the DBRM directive is used to create a database request module during the compile.

See the section SQL Option Preprocessor Options for a list of directives that can be used.

SQL Communications Area (SQLCA)

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 your COBOL system. A complete description of the SQLCA structure is provided in the SQL Reference manual.

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. Use the debugger to view the SQLCA fields and examine its error codes and warning flags.
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:

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). These variables appear in italics in the error message descriptions listed in Error Messages in your Reference Help. For more information on SQLERRMC, see the SQL Reference manual.
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.

SQL Descriptor Area (SQLDA)

COBOL programs that include dynamic SQL must have an SQL Descriptor Area (SQLDA) defined. This definition is usually accomplished by including the SQLDA copybook provided with your COBOL system.

The default SQLDA copybook used by the SQL Option preprocessor is not mainframe DB2 compatible. Instead it is designed for easy compatibility if you are upgrading from older version of XDB or SQL Option software.

A mainframe compatible SQLDA is provided in the \mfe\source directory with filename of sqlda.new. If you want to use that copybook instead, just rename it. You then need to compile all programs that use this copybook or SQLDA's defined using the mainframe SQLDA structure with the SQL preprocessor directive SQLDA-VER=1 or the program will not map data properly and probably trap.

The SQLDA holds information about dynamic SQL queries, and is required for allocating the proper amount of space for the query.

For more information about dynamic SQL and the SQLDA, see the chapter Using Dynamic SQL in COBOL. For a complete description of the SQLDA structure, see the SQL Reference. Refer to your preprocessor documentation on the use of pointer variables and for additional information on dynamic SQL in COBOL programs.

Sample COBOL Program with Embedded SQL

The following program shows how SQL statements can be embedded in a COBOL program. Note that the copybook EMPREC has been included in the program using the INCLUDE statement.

This program declares a cursor, opens it, and then fetches data into a set of host variables. The host data is displayed. (Note the use of the indicator variable used with the commission variable COM.) When the end of data is reached, or when the user is finished viewing data, the loop is terminated, the cursor is closed, and the program ends.

 IDENTIFICATION DIVISION.

 PROGRAM-ID. TESTALL. 
 AUTHOR-NAME. ME. 

 ENVIRONMENT DIVISION. 

 CONFIGURATION SECTION. 
 SOURCE-COMPUTER. IBM-AT. 
 OBJECT-COMPUTER. IBM-AT. 

 INPUT-OUTPUT SECTION. 
 FILE-CONTROL. 

 DATA DIVISION.  

 FILE SECTION.  

 WORKING-STORAGE SECTION. 

 EXEC SQL 
   INCLUDE EMPREC 
 END-EXEC. 

 01 DISP-RATE PIC $$$,$$$,$$9.99. 
 01 DISP-COM PIC Z.99.  
 01 DISP-CODE PIC ----9. 
 01 FAKE-CHAR PIC X.  
 01 ANSS PIC X. 
 01 COM-NULL-IND PIC S9(4) COMP. 

 EXEC SQL 
   INCLUDE SQLCA 
 END-EXEC. 

 PROCEDURE DIVISION. 

 100-MAIN.
* declare cursor for select 
     EXEC SQL
         DECLARE EMPTBL CURSOR FOR
         SELECT * 
           FROM EMPLOYEE
         ORDER BY LNAME
     END-EXEC

* open cursor
     EXEC SQL
         OPEN EMPTBL
     END-EXEC 
     MOVE SQLCODE TO DISP-CODE
     DISPLAY 'open ' DISP-CODE

* fetch a data item 
     EXEC SQL
         FETCH EMPTBL INTO 
           :ENO,:LNAME,:FNAME,:STREET,:CITY, 
           :ST,:ZIP,:DEPT,:PAYRATE, 
           :COM :COM-NULL-IND 
     END-EXEC. 

 100-test. 
     MOVE SQLCODE TO DISP-CODE
     DISPLAY 'fetch ' DISP-CODE

* loop until no more data
     PERFORM UNTIL SQLCODE < 0 OR SQLCODE = 100

* display the record
     MOVE PAYRATE TO DISP-RATE
     MOVE COM TO DISP-COM
     DISPLAY 'department ' DEPT 
     DISPLAY 'last name ' LNAME 
     DISPLAY 'first name ' FNAME 
     DISPLAY 'street ' STREET 
     DISPLAY 'city ' CITY 
     DISPLAY 'state ' ST 
     DISPLAY 'zip code ' ZIP 
     DISPLAY 'payrate ' DISP-RATE 
     IF COM-NULL-IND < 0 
         DISPLAY 'commission is null' 
     ELSE 
         DISPLAY 'commission ' DISP-COM 
     END-IF 
     DISPLAY 'Do you want to see the next record? (y/n)' 
     ACCEPT ANSS 
     IF ANSS = 'Y' OR 'y' 
         EXEC SQL 
           FETCH EMPTBL INTO 
             :ENO,:LNAME,:FNAME,:STREET,:CITY, 
             :ST,:ZIP,:DEPT,:PAYRATE, 
             :COM :COM-NULL-IND 
         END-EXEC 
     ELSE 
         GO TO CLOSE-LOOP 
     END-IF 
     MOVE SQLCODE TO DISP-CODE 
     DISPLAY 'fetch ' DISP-CODE 
     END-PERFORM  

     DISPLAY 'All records in this table have been selected'. 

 CLOSE-LOOP.
* close the cursor 
     EXEC SQL 
         CLOSE EMPTBL 
     END-EXEC. 

 100-EXIT. 
     STOP RUN.