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.
Pingback: COBOL Sort Module in GnuCOBOL | Big Dan the Blogging Man