COBOL: esqlOC

嵌入式 SQL（Embedded SQL）是一種將 SQL 語句直接寫入 C 語言、COBOL、FORTRAN 及 Ada 等程式語言原始碼中的方法。 藉此方法，可使得應用程式能夠存取以及處理資料。 esqlOC 是由 Sergey Kashyrin 開發 的 GnuCOBOL Embedded SQL (ESQL) 預編譯器與執行期程式庫，它依賴 ODBC 來連接多種 RDBMS。 Open Cobol ESQL (ocesql) 則是 另外一套開放原始碼嵌入式 SQL 預編譯器與執行期程式庫。 因為嵌入式 SQL 是 SQL 標準的一部份，因此理論上可以替換不同的預編譯器來轉譯原始碼。

在使用時首先要使用 esqlOC 程式將包含嵌入式 SQL 的程式碼轉譯為 COBOL 原始碼， 再使用 GnuCOBOL 編譯，並且需要連結 esqlOC 的 ocsql 函式庫。

Embedded SQL statements work like normal SQL statements with some minor changes. For example, the output of a query is directed to a predefined set of variables which are referred as Host Variables. An additional INTO clause is placed in the SELECT statement.

version.sqb

       IDENTIFICATION DIVISION.
       PROGRAM-ID. 01_select.

       DATA DIVISION.
       WORKING-STORAGE SECTION.
       EXEC SQL BEGIN DECLARE SECTION END-EXEC.
       01  BUFFER       PIC X(1024).
       01  HVERSION     PIC X(160).
       EXEC SQL END DECLARE SECTION END-EXEC.

       PROCEDURE DIVISION.
           STRING 'DRIVER=PSQL;'
                  'SERVER=localhost;'
                  'PORT=5432;'
                  'DATABASE=danilo;'
                  'USER=danilo;'
                  'PASSWORD=danilo;'
           INTO BUFFER.

           EXEC SQL 
               CONNECT TO :BUFFER
           END-EXEC.

           IF SQLCODE NOT = ZERO
               DISPLAY "Connection Failed"
               STOP RUN
           END-IF.

           EXEC SQL 
               SELECT version() INTO :HVERSION
           END-EXEC.

           IF SQLCODE = 0
               DISPLAY "Version: " HVERSION
           END-IF.

           EXEC SQL DISCONNECT ALL END-EXEC.
           STOP RUN.

如果要使用 ODBC DSN，可以改寫如下：

       IDENTIFICATION DIVISION.
       PROGRAM-ID. 01_select.

       DATA DIVISION.
       WORKING-STORAGE SECTION.
       EXEC SQL BEGIN DECLARE SECTION END-EXEC.
       01  BUFFER       PIC X(1024).
       01  HVERSION     PIC X(160).
       EXEC SQL END DECLARE SECTION END-EXEC.


       PROCEDURE DIVISION.
      * with DSN: 'youruser/yourpasswd@yourODBC_DSN'
           STRING 'danilo/danilo@PostgreSQL'
           INTO BUFFER.

           EXEC SQL 
               CONNECT TO :BUFFER
           END-EXEC.

           IF SQLCODE NOT = ZERO
               DISPLAY "Connection Failed"
               STOP RUN
           END-IF.

           EXEC SQL 
               SELECT version() INTO :HVERSION
           END-EXEC.

           IF SQLCODE = 0
               DISPLAY "Version: " HVERSION
           END-IF.

           EXEC SQL DISCONNECT ALL END-EXEC.
           STOP RUN.

使用 esqlOC 程式將包含嵌入式 SQL 的程式碼轉譯為 COBOL 原始碼：

esqlOC version.sqb

而後再使用 GnuCOBOL 編譯。

cobc -x version.cob -locsql

Cursors are used to handle multiple row selections at a time. They are data structures that hold all the results of a query.

所以程式可以改寫如下：

       IDENTIFICATION DIVISION.
       PROGRAM-ID. 01_select.

       DATA DIVISION.
       WORKING-STORAGE SECTION.
       EXEC SQL BEGIN DECLARE SECTION END-EXEC.
       01  BUFFER       PIC X(1024).
       01  HVERSION     PIC X(160).
       EXEC SQL END DECLARE SECTION END-EXEC.

       PROCEDURE DIVISION.
       MAIN-PARA.
           STRING 'DRIVER=PSQL;'
                  'SERVER=localhost;'
                  'PORT=5432;'
                  'DATABASE=danilo;'
                  'USER=danilo;'
                  'PASSWORD=danilo;'
           INTO BUFFER.

           EXEC SQL 
               CONNECT TO :BUFFER
           END-EXEC.

           IF SQLCODE NOT = ZERO
               DISPLAY "Connection Failed"
               STOP RUN
           END-IF.

           EXEC SQL
               DECLARE VERCUR CURSOR FOR
               SELECT version()
           END-EXEC

           EXEC SQL
               OPEN VERCUR
           END-EXEC

           PERFORM UNTIL SQLCODE = 100
               MOVE SPACES TO HVERSION
               EXEC SQL
                   FETCH VERCUR INTO :HVERSION
               END-EXEC

               DISPLAY HVERSION
           END-PERFORM

           EXEC SQL
               CLOSE VERCUR
           END-EXEC.

           EXEC SQL DISCONNECT ALL END-EXEC.
           STOP RUN.

參考資料

• esqlOC
• ESQL for ODBC for GnuCobol/OpenCOBOL