Precompiler concepts

Embedded SQL in a COBOL program

An embedded SQL statement starts with EXEC SQL and ends with END-EXEC, followed by a period when the statement ends a COBOL sentence:

           EXEC SQL
               UPDATE EMP SET SAL = SAL * 1.1 WHERE DEPTNO = :DEPT-NO
           END-EXEC.

Inside the statement, COBOL data items, called host variables, are prefixed by a colon (:DEPT-NO). The precompiler passes their address, type and size to the runtime, which converts their contents to and from the database types (see Host variables and datatypes).

Embedded SQL statements can appear:

  • in the DATA DIVISION: INCLUDE, BEGIN/END DECLARE SECTION, DECLARE ... CURSOR, VAR, DECLARE TABLE and WHENEVER…

    NoteTo be verified

    Check where each statement is accepted by gixpp.

  • in the PROCEDURE DIVISION: all the executable statements.

The declare section

Pro*COBOL and the SQL standard require the host variables to be declared between BEGIN DECLARE SECTION and END DECLARE SECTION:

           EXEC SQL BEGIN DECLARE SECTION END-EXEC.
       01  EMP-NAME    PIC X(20).
       01  SALARY      PIC S9(7)V99 COMP-3.
           EXEC SQL END DECLARE SECTION END-EXEC.

GixSQL accepts these statements, but does not require them: any data item of the WORKING-STORAGE, LOCAL-STORAGE or LINKAGE SECTION can be used as a host variable.

Including files

EXEC SQL INCLUDE name END-EXEC includes a copybook, like a COBOL COPY statement, but the included file is also processed by the precompiler. It is mostly used for the SQLCA:

           EXEC SQL INCLUDE SQLCA END-EXEC.

The copybooks are searched in the directories given with -I / --copypath, with the extensions given with -E / --esql-copy-exts. COBOL COPY statements are processed by the precompiler only with -p / --esql-preprocess-copy (see The gixpp precompiler).

Precompiling, compiling and linking

The full sequence to build a program prog.pco is:

$ gixpp -e -S -I /opt/gixsql/share/gixsql/copy -I . \
        -i prog.pco -o prog.cob
$ cobc -x prog.cob -L /opt/gixsql/lib -lgixsql

where:

  • -e enables the ESQL processing (it is mandatory);
  • -S generates static calls to the runtime (recommended);
  • -I gives the copybook directories: the one of the SQLCA and the ones of the program;
  • -i and -o give the input and output files.

A first program

The following program connects to a database whose connection string is in the environment variable DATASRC, counts the rows of a table, and displays the result.

       IDENTIFICATION DIVISION.
       PROGRAM-ID. FIRST.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
       01  DATASRC  PIC X(64).
       01  DBUSR    PIC X(64).
       01  DBPWD    PIC X(64).
       01  NB-ROWS  PIC 9(6) VALUE 0.
           EXEC SQL INCLUDE SQLCA END-EXEC.
       PROCEDURE DIVISION.
           ACCEPT DATASRC FROM ENVIRONMENT "DATASRC".
           ACCEPT DBUSR   FROM ENVIRONMENT "DATASRC_USR".
           ACCEPT DBPWD   FROM ENVIRONMENT "DATASRC_PWD".

           EXEC SQL
               CONNECT TO :DATASRC USER :DBUSR USING :DBPWD
           END-EXEC.
           IF SQLCODE NOT = 0
               DISPLAY "CONNECT failed: " SQLCODE " " SQLERRMC
               STOP RUN
           END-IF.

           EXEC SQL
               SELECT COUNT(*) INTO :NB-ROWS FROM EMP
           END-EXEC.
           DISPLAY "EMP contains " NB-ROWS " rows".

           EXEC SQL CONNECT RESET END-EXEC.
           STOP RUN.

Build and run it against a PostgreSQL database:

$ gixpp -e -S -I /opt/gixsql/share/gixsql/copy -i first.pco -o first.cob
$ cobc -x first.cob -L /opt/gixsql/lib -lgixsql
$ export LD_LIBRARY_PATH=/opt/gixsql/lib
$ export DATASRC=pgsql://localhost:5432/demo
$ export DATASRC_USR=demo DATASRC_PWD=secret
$ ./first
EMP contains 000014 rows
NoteTo be verified

Run this example against the test databases and paste the actual output.

Sample tables

The examples of this manual use the classic DEPT and EMP tables:

CREATE TABLE DEPT (
  DEPTNO  NUMERIC(2) PRIMARY KEY,
  DNAME   VARCHAR(14),
  LOC     VARCHAR(13)
);
CREATE TABLE EMP (
  EMPNO    NUMERIC(4) PRIMARY KEY,
  ENAME    VARCHAR(10),
  JOB      VARCHAR(9),
  MGR      NUMERIC(4),
  HIREDATE DATE,
  SAL      NUMERIC(7,2),
  COMM     NUMERIC(7,2),
  DEPTNO   NUMERIC(2) REFERENCES DEPT
);