My Own Embedded SQL GnuCOBOL Program

Having successfully compiled and executed the OCESQL supplied test program, I want to write my own program.

My plan is to read data from the dvdrental database that I installed during the Installing PostgreSQL (for GnuCOBOL) post.

The sample database looks like this:

I would like to create a Rental history report that shows each customer and all of the DVD titles he has rented.

The query to provide the data I want looks like this:

select customer.customer_id, customer.last_name, customer.first_name,
    film.title, rental.return_date
from customer
    inner join rental    on customer.customer_id = rental.customer_id
    left  join inventory on rental.inventory_id  = inventory.inventory_id
    left  join film      on inventory.film_id    = film.film_id
order by customer.last_name, customer.first_name, film.title

Which provides data like this:

Using ocesql’s FETCHTBL.COB as an example I began writing my own program.

Immediately I found some unexpected issues.

First, you CANNOT use free format code. You must use the original COBOL standard where divisions/sections/paragraphs are in area A (columns 8-11) and the rest is in area B (columns 12-72). Ugh! I don’t know how I wrote so much code with a 72 column limit. It’s really annoying now.

Further, if you accidentally go past column 72, it will be the ocesql precompiler that catches it with a rather vague message:

precompile start: dvdRentalReport.cob
=======================================================
              LIST OF CALLED DB Library API            
=======================================================
000049:syntax error
=======================================================
translate error

The precompiler indicates the error is at line 49, but it wasn’t – it was at line 44. So watch for this!

I also found that I would have various problems if I tried to use lowercase characters in the program. This was really inconsistent. Somethings worked, some didn’t. The answer is to just keep everything upper case.

Due to these limitations, the COBOL being written for ocesql definitely looks old school:

           EXEC SQL DECLARE C1 CURSOR FOR
               SELECT 
                   CUSTOMER.CUSTOMER_ID,
                   CUSTOMER.LAST_NAME,
                   CUSTOMER.FIRST_NAME,
                   FILM.TITLE,
                   TO_CHAR(RENTAL.RETURN_DATE,'YYYYMMDD')
               FROM CUSTOMER
               INNER JOIN RENTAL    ON 
                   CUSTOMER.CUSTOMER_ID = RENTAL.CUSTOMER_ID
               LEFT  JOIN INVENTORY ON 
                   RENTAL.INVENTORY_ID  = INVENTORY.INVENTORY_ID
               LEFT  JOIN FILM      ON 
                   INVENTORY.FILM_ID    = FILM.FILM_ID
           END-EXEC.

Code Walk Through

Any variable that will be passed to SQL in the EXEC SQL statement must be defined in a EXEC SQL BEGIN DECLARE SECTION / END DECLARE SECTION block. Further, any variable used must be an elementary item (not a group item).

I declare these as:

       EXEC SQL BEGIN DECLARE SECTION END-EXEC.
       01  DB-CONTROL.
           03  DB-NAME                 PIC X(30),          VALUE SPACES.
           03  DB-USER                 PIC X(30),          VALUE SPACES.
           03  DB-PASS                 PIC X(10),          VALUE SPACES.
       01  DB-RECCOUNT                 PIC 9(9).
       01  DB-REC.
           03  DB-CUSTID               PIC 9(9).
           03  DB-LASTNAME             PIC X(45).
           03  DB-FIRSTNAME            PIC X(45).
           03  DB-FILMTITLE            PIC X(45).
           03  DB-RETURNDATE           PIC 99999999.
       EXEC SQL END DECLARE SECTION END-EXEC.

I used some groups such as DB-CONTROL and DB-REC, but these are NOT passed to SQL.

DB-RETURNDATE is a great example of not using a group. The SQL will return the date as YYYYMMDD, but if I declare DB-RETURNDATE as:

           03  DB-RETURNDATE.
               05  DB-YY              PIC 9999.
               05  DB-MM              PIC 99.
               05  DB-DD              PIC 99.

Then the query would fail. So I resorted to moving DB-RETURN-DATE to TF-DATE which is defined as

       01  TF-TEMP-FIELDS.
           03  TF-DATE.
               05  TF-YY               PIC 9999.
               05  TF-MM               PIC 99.
               05  TF-DD               PIC 99.

I can then STRING the date together into the format I want.

      * *** DEFINE SQL CONTROL AREA

       EXEC SQL INCLUDE SQLCA END-EXEC.

This is the SQL control area which contains various error information.

To start, the database is opened with CONNECT and the credentials need to be set before the call.

           MOVE "dvdrental@chiefdude10"     TO DB-NAME.
           MOVE "postgres"                  TO DB-USER.
           MOVE "mypass"                    TO DB-PASS.
      
           DISPLAY "DOING CONNECT".
           EXEC SQL
               CONNECT :DB-USER IDENTIFIED BY :DB-PASS USING :DB-NAME;
           END-EXEC.
           
           IF SQLCODE <> ZERO,
               PERFORM Z1000-DB-ERROR  THRU Z1099-EXIT;
               STOP RUN.

If an error occurs, the DB error code is called. This should be done after every EXEC SQL.

Next, a cursor is declared using the SQL statement that will retrieve all of the data:

      * *** DECLARE CURSOR
      
           DISPLAY "DOING DECLARE CURSOR".

           EXEC SQL DECLARE C1 CURSOR FOR
               SELECT 
                   CUSTOMER.CUSTOMER_ID,
                   CUSTOMER.LAST_NAME,
                   CUSTOMER.FIRST_NAME,
                   FILM.TITLE,
                   TO_CHAR(RENTAL.RETURN_DATE,'YYYYMMDD')
               FROM CUSTOMER
               INNER JOIN RENTAL    ON 
                   CUSTOMER.CUSTOMER_ID = RENTAL.CUSTOMER_ID
               LEFT  JOIN INVENTORY ON 
                   RENTAL.INVENTORY_ID  = INVENTORY.INVENTORY_ID
               LEFT  JOIN FILM      ON 
                   INVENTORY.FILM_ID    = FILM.FILM_ID
           END-EXEC.
           IF SQLCODE <> ZERO,
               PERFORM Z1000-DB-ERROR  THRU Z1099-EXIT;
               STOP RUN.

This is the same SQL query I mentioned at the top of this post EXCEPT date must be formatted into something COBOL can handle, so I used

TO_CHAR(RENTAL.RETURN_DATE,'YYYYMMDD')

The rest of the program retrieves each record of data and prints it until there is no more. Retrieving is done with SQL EXEC FETCH:

           EXEC SQL 
               FETCH C1 INTO 
                   :DB-CUSTID,
                   :DB-LASTNAME,
                   :DB-FIRSTNAME,
                   :DB-FILMTITLE,
                   :DB-RETURNDATE
           END-EXEC.
           IF SQLCODE <> ZERO,
               PERFORM Z1000-DB-ERROR  THRU Z1099-EXIT;
               STOP RUN.

Each field of the record is retrieved into a COBOL variable. Then that data is formatted into a line to print:

               MOVE SPACES             TO DT-DETAIL1;
               MOVE DB-CUSTID          TO DT-CUSTID;
               MOVE DB-LASTNAME        TO DT-LASTNAME;
               MOVE DB-FIRSTNAME       TO DT-FIRSTNAME;
               MOVE DB-FILMTITLE       TO DT-FILMTITLE;
               
      * *** GROUP ITEMS CANNOT BE USED IN SQL STATEMENTS, SO MOVE 
      * *** DB-RETURNDATE TO TF-RETURNDATE SO IT CAN BE REFORMATTED.
      
               MOVE DB-RETURNDATE      TO TF-DATE;
               STRING TF-MM, "/", TF-DD, "/", TF-YY
                   INTO                DT-RETURNDATE;
                  
               DISPLAY DT-DETAIL1;

The full program can be found at:

http://www.xyfyx.com/files/dvdRentalReport.cob

Compiling the Program

Don’t forget you need to set a couple of environment variables:

    export COBCPY=~/Open-COBOL-ESQL-1.2/copy
    export COB_LDFLAGS=-Wl,--no-as-needed

With those set, now run the precompiler:

ocesql dvdRentalReport.cob dvdRentalReport.tmp
precompile start: dvdRentalReport.cob
=======================================================
              LIST OF CALLED DB Library API            
=======================================================
;
;
;
;
;
;
Generate:OCESQLConnect
Generate:OCESQLCursorDeclare
Generate:OCESQLCursorOpen
Generate:OCESQLCursorFetchOne
Generate:OCESQLCursorFetchOne
Generate:OCESQLCursorClose
Generate:OCESQLDisconnect
Generate:ROLLBACK
=======================================================

To compile:

cobc -locesql -x dvdRentalReport.tmp

and then run with:

./dvdRentalReport
DOING CONNECT
DOING DECLARE CURSOR
DOING OPEN CURSOR
------------------------DVD RENTAL HISTORY-----------------------
-ID- ------------NAME------------ -----FILM TITLE----- -RETURNED-
 459 Collazo         Tommy        Freaky Pocus         05/28/2005
 408 Murrell         Manuel       Graduate Lord        06/01/2005
 333 Purdy           Andrew       Love Suicides        06/03/2005
 222 Hansen          Delores      Idols Snatchers      06/02/2005
 549 Christenson     Nelson       Mystic Truman        05/27/2005
 269 Walters         Cassandra    Swarm Gold           05/29/2005
 239 Romero          Minnie       Lawless Vision       05/27/2005
 126 Simpson         Ellen        Matrix Snowman       05/28/2005
 399 Isom            Danny        Hanging Deep         05/31/2005
 142 Burns           April        Whale Bikini         06/02/2005
 261 Byrd            Deanna       Games Bowfinger      05/30/2005
 334 Mcwhorter       Raymond      King Evolution       05/30/2005
 446 Culp            Theodore     Monterey Labyrinth   05/26/2005
 319 Weiner          Ronald       Pelican Comforts     06/03/2005
 316 Curley          Steven       Boogie Amelie        05/26/2005
 575 Oglesby         Isaac        Contact Anonymous    05/27/2005
  19 Martinez        Ruth         Roman Punk           05/31/2005

VoilĂ ! A COBOL report that looks like it is straight out of 1983!

Now that I can retrieve the data, the next step will be to use the COBOL Sort module to sort and the Report Writer to produce the actual report.

This entry was posted in c-gnuCOBOL and tagged , . Bookmark the permalink.

1 Response to My Own Embedded SQL GnuCOBOL Program

  1. Pingback: COBOL Sort Module in GnuCOBOL | Big Dan the Blogging Man

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.