In this post I’m going to modify the code I wrote in My Own Embedded SQL GnuCOBOL Program.
These days, it is almost certainly more efficient to allow the SQL to do the sort for you. When I was writing COBOL in the 80’s, the COBOL sort module was more efficient than any other method to sort large amounts of data for reporting.
I want to go old school and use the COBOL sort module. There are two methods of sorting in COBOL: sorting files (USING/GIVING) or using sort procedure (RELEASE/RETURN).
USING/GIVING is the simple way to sort. Given an input data file, it will create a new sorted data file. You create an SD (like an FD) of the input file describing the data, then you just use the sort verb:
SORT SORT-FILE
ON ASCENDING KEY MY-KEY,
USING INPUT-FILE,
GIVING OUTPUT-FILE.
While this is simple syntax, it really isn’t any faster than running any other stand-alone sort facility. The power of COBOL sort is in it’s INPUT/OUTPUT PROCEDUREs.
When using this form of sort, you supply the name of an INPUT and OUTPUT procedure that will be invoked during the sort. For example:
SORT SORT-FILE
ON ASCENDING KEY MY-KEY,
INPUT PROCEDURE IS IN-PROC,
OUTPUT PROCEDURE IS OUT-PROC.
When using sort in this manner, the sort is invoked and each time it needs a new record it calls IN-PROC. When it is ready to start outputting records it calls OUT-PROC for each record.
PROCEDURE DIVISION Sections
SORT INPUT/OUTPUT PROCEDURE requires section names rather than paragraph names. In the code I’ve posted so far I’ve not used section names.
Code Walk Through
The source code to my test program can be found at
http://www.xyfyx.com/files/sortedReport.cob
Like the program upon which this one was based, it will read records from the PostgreSQL dvdRental database and create a terminal based report of Rental History.
First, we need to setup a SELECT statement for the sort file which will just be a temp file in the current directory:
ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
SELECT SF-SORT-FILE,
ASSIGN TO "./SORTFILE.TMP",
FILE STATUS IS SF-STATUS.
SF-STATUS is a 2 byte variable that contains the IO status of the last IO verb applied to the file. If it is ’00’, then there was no error.
Next, you describe the format of the sort file. It must contain a field for every field that you will need to print.
FILE SECTION.
SD SF-SORT-FILE.
01 SF-SORT-REC.
03 SF-CUSTID PIC 9(9).
03 SF-LASTNAME PIC X(45).
03 SF-FIRSTNAME PIC X(45).
03 SF-FILMTITLE PIC X(45).
03 SF-RETURNDATE PIC 99999999.
In the MAIN module, we will CONNECT to the database, DECLARE the cursor, and OPEN the cursor just as we did in the original program. Once the cursor is open, then we invoke the sort:
SORT SF-SORT-FILE
ON ASCENDING KEY SF-LASTNAME,
ON ASCENDING KEY SF-FIRSTNAME,
ON ASCENDING KEY SF-RETURNDATE,
INPUT PROCEDURE IS A1000,
OUTPUT PROCEDURE IS A2000.
Here we specify the keys to sort on as well as the SECTION names for the INPUT and OUTPUT procedures.
Here is the INPUT PROCEDURE. Retrieve the first record:
A1000 SECTION.
A1000-SORT-INPUT.
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.
Move the fields from the database record into the sort record:
PERFORM UNTIL SQLCODE NOT = ZERO
INITIALIZE SF-SORT-REC;
MOVE DB-CUSTID TO SF-CUSTID;
MOVE DB-LASTNAME TO SF-LASTNAME;
MOVE DB-FIRSTNAME TO SF-FIRSTNAME;
MOVE DB-FILMTITLE TO SF-FILMTITLE;
MOVE DB-RETURNDATE TO SF-RETURNDATE;
Release the record to the sort procedure:
RELEASE SF-SORT-REC;
Get the next record:
EXEC SQL
FETCH C1 INTO
:DB-CUSTID,
:DB-LASTNAME,
:DB-FIRSTNAME,
:DB-FILMTITLE,
:DB-RETURNDATE
END-EXEC;
END-PERFORM.
Once the INPUT PROCEDURE exits, that is an indication to SORT there are no more records so it performs the SORT and then calls the OUTPUT PROCEDURE.
In the output procedure, RETURN is used to read records back in from the sort to be processed.
A2000 SECTION.
A2000-SORT-OUTPUT.
RETURN SF-SORT-FILE,
AT END,
NEXT SENTENCE.
IF SF-STATUS <> ZERO,
DISPLAY "NO DATA TO REPORT.";
STOP RUN.
DISPLAY HD-HDR1.
DISPLAY HD-HDR2.
Move data from the sort record to the detail print record:
PERFORM UNTIL SF-STATUS <> ZERO,
INITIALIZE DT-DETAIL1;
MOVE SF-CUSTID TO DT-CUSTID;
MOVE SF-LASTNAME TO DT-LASTNAME;
MOVE SF-FIRSTNAME TO DT-FIRSTNAME;
MOVE SF-FILMTITLE TO DT-FILMTITLE;
MOVE SF-RETURNDATE TO TF-DATE;
STRING TF-MM, "/", TF-DD, "/", TF-YY
INTO DT-RETURNDATE;
DISPLAY DT-DETAIL1;
RETURN SF-SORT-FILE,
AT END,
NEXT SENTENCE;
END-RETURN;
END-PERFORM.
A2099-EXIT.
EXIT.
Compile and Run
$export COBCPY=~/Open-COBOL-ESQL-1.2/copy $export COB_LDFLAGS=-Wl,--no-as-needed $ocesql sortedReport.cob sortedReport.tmp $cobc -locesql -x sortedReport.tmp $./sortedReport DOING CONNECT DOING DECLARE CURSOR DOING OPEN CURSOR ------------------------DVD RENTAL HISTORY----------------------- -ID- ------------NAME------------ -----FILM TITLE----- -RETURNED- 505 Abney Rafael Conversation Downhil 00/00/0000 505 Abney Rafael Sagebrush Clueless 05/29/2005 505 Abney Rafael Pocus Pulp 06/05/2005 505 Abney Rafael Legally Secretary 06/19/2005 505 Abney Rafael Nightmare Chill 06/20/2005 505 Abney Rafael Trading Pinocchio 06/28/2005 505 Abney Rafael Coneheads Smoochy 06/28/2005 505 Abney Rafael Wanda Chamber 07/12/2005 505 Abney Rafael Conquerer Nuts 07/14/2005 ...
Note the very first line of the report, the returned date is 00/00/0000. This video is not returned yet and has a value of NULL which becomes 00/00/0000.
Big Dan the Blogging Man 