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 TABLEandWHENEVER…NoteTo be verifiedCheck 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:
-eenables the ESQL processing (it is mandatory);-Sgenerates static calls to the runtime (recommended);-Igives the copybook directories: the one of the SQLCA and the ones of the program;-iand-ogive 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
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
);