Embedded SQL in COBOL

Introduction

Embedded SQL means that you can use SQL statements through a host programming language, C/C++, COBOL, or FORTRAN. COBOL is used as an example in this article.

You include SQL statements as part of the source code for an application. The source code is compiled and linked with the appropriate language-specific facilities. The SQL statements are executed in the context of the application.

Mimer SQL supports embedded SQL for COBOL according to the ISO standard.

Function

The ESQL preprocessor processes SQL statements embedded in the host language. SQL statements are identified by the leading delimiter “EXEC SQL” and terminated by “END-EXEC”, e.g. “EXEC SQL DELETE FROM HOTEL END-EXEC”.

You must declare host variables used in SQL statements within the SQL DECLARE SECTION, delimited by the statements BEGIN DECLARE SECTION and END DECLARE SECTION.

You can find detailed information on Embedded SQL in the Programmer’s Manual of the Mimer SQL Documentation set.

Example

The following is an example that connects to the database defined as the default. All rows are then fetched from the table INFORMATION_SCHEMA.TABLES.

The example is stored in a file called simple.eco:

 IDENTIFICATION DIVISION.
 PROGRAM-ID. SIMPLE.

 DATA DIVISION.
 WORKING-STORAGE SECTION.
     EXEC SQL BEGIN DECLARE SECTION END-EXEC.
 01  SQLSTATE PIC X(5).
 01  TABLE-SCHEMA PIC X(128).
 01  TABLE-NAME PIC X(128).
 01  TABLE-TYPE PIC X(20).
     EXEC SQL END DECLARE SECTION END-EXEC.

 PROCEDURE DIVISION.
 MAIN SECTION.

 DO-IT.
     EXEC SQL DECLARE MYCURSOR CURSOR FOR
              SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
              FROM INFORMATION_SCHEMA.TABLES END-EXEC.

*    Connect to default database
     EXEC SQL CONNECT TO ''
              USER 'SYSADM' USING 'SYSADM' END-EXEC.

     EXEC SQL OPEN MYCURSOR END-EXEC.

     PERFORM FETCH-AND-DISPLAY UNTIL SQLSTATE = "02000".

     EXEC SQL CLOSE MYCURSOR END-EXEC.
     EXEC SQL COMMIT END-EXEC.
     EXEC SQL DISCONNECT DEFAULT END-EXEC.
     STOP RUN.

 FETCH-AND-DISPLAY.
     EXEC SQL FETCH MYCURSOR INTO :TABLE-SCHEMA,
                                  :TABLE-NAME,
                                  :TABLE-TYPE END-EXEC.
     IF SQLSTATE NOT = "02000",
         DISPLAY TABLE-SCHEMA, TABLE-NAME, TABLE-TYPE.

 END PROGRAM SIMPLE.

The program is preprocessed as follows:

esql –cob simple.eco

The resulting file, ‘simple.cob’, is compiled using a COBOL-compiler and then linked.

When executing the resulting ‘simple’ program, a list of the items selected will be displayed.

You can find a more complete example in distributed example program example.eco.

Benefits

Embedded SQL is a straightforward way of writing applications that access databases.

Links

Program examples: simple.eco, example.eco

See the section about Embedded SQL in the Programmer’s Manual of the Mimer SQL Documentation set.