The flow chart in Figure 8-4 summarizes the functionality
of program COBEX8. This program uses a cursor and the UPDATE WHERE
CURRENT command to update column RECEIVEDQTY in table
PURCHDB.ORDERITEMS. The runtime dialog for COBEX8
appears in Figure 8-5, and the source code in Figure 8-6.
Program COBEX8 performs paragraph
A200-CONNECT-DBENVIRONMENT 1 to establish a DBE session.
This paragraph executes the
CONNECT command 5 for the sample DBEnvironment, PartsDBE.
The program performs paragraph A550-DECLARE-CURSOR 2 , which
contains the DECLARE CURSOR command 9 . This
command is a preprocessor directive and is not executed at run time.
At run time, paragraph A550-DECLARE-CURSOR only displays the message
Declare Cursor. The DECLARE CURSOR command defines a cursor
named ORDERREVIEW. The cursor is associated with a SELECT
command that retrieves the following columns for all rows in
table PURCHDB.ORDERITEMS having a specific order number but
no null values in column VENDPARTNUMBER:
 |
ORDERNUMBER (defined NOT NULL)
ITEMNUMBER (defined NOT NULL)
VENDPARTNUMBER
RECEIVEDQTY
|
Cursor ORDERREVIEW has a FOR UPDATE clause naming column
RECEIVEDQTY to allow the user to change the value in this
column.
The program then performs paragraph B100-GET-DATA through
B100-EXIT until the DONE flag
is set 3 .
Paragraph B100-GET-DATA prompts for an order number or a
zero 10 . When the user enters a zero 11 , the DONE flag is
set and the program terminates. When the user enters
an order number, the program begins a transaction by performing
paragraph A300-BEGIN-TRANSACTION 12 , which executes the
BEGIN WORK command 6 .
Cursor ORDERREVIEW is then opened 13 and paragraph
B200-FETCH-ROW through B200-EXIT performed 14 to
retrieve a row at a time from the active set.
This paragraph is performed until the DONE-FETCH flag is
set; this flag is set when:
The FETCH command fails; this command fails when no rows
qualify for the active set 20 , when the last row has already been
fetched 21 , or when ALLBASE/SQL cannot execute this command for some
other reason 22 .
The program user wants to stop reviewing rows from the active
set 30 .
Note that in three of the above cases,
20 , 21 , 30 ,
the flag is set when B600-LAST-ROW is performed 31 .
The FETCH command 15 names an indicator variable for
RECEIVEDQTY, the only column in the query result that may contain
a null value. If the FETCH command is successful, the program
sets the FIRST-TIME flag to indicate that a row has been found 16 .
B300-DISPLAY-ROW 17 is performed to display the
current row, and B400-DISPLAY-UPDATE 18 is performed
to offer the option of updating the current row 26 .
In paragraph B300-DISPLAY-ROW 24 , if column RECEIVEDQTY in
the current row contains a null value, the message
ReceivedQty is NULL is displayed 25 .
Paragraph B400-DISPLAY-UPDATE prompts the user to indicate
whether the current RECEIVEDQTY value is to be updated 26 .
If so, and if the user enters a 0, a null value is assigned to
RECEIVEDQTY 27 . If a value other than 0 is entered,
that value is assigned to RECEIVEDQTY 28 .
Paragraph B500-MORE-ROWS is performed 19 , prompting the user
to indicate if another row is to be fetched 29 . If so, the
FETCH command is re-executed. If not, paragraph B600-LAST-ROW 30
asks whether the user wants to make permanent any updates he
may have made in the active set 32 . To keep any row
changes, the program performs paragraph A400-COMMIT-WORK 34 ,
which executes the COMMIT WORK command 7 . To undo any row
changes, the program performs paragraph A450-ROLLBACK-WORK 33 ,
which executes the ROLLBACK WORK command 8 .
The COMMIT WORK command is also executed when ALLBASE/SQL sets SQLCODE to
100 following execution of the FETCH command 20 21 .
SQLCODE is set to 100 when
no rows qualify for the active set.
If the FETCH command fails for some other
reason, the ROLLBACK WORK command is executed instead 23 .
Before any COMMIT WORK or ROLLBACK WORK command is executed,
cursor ORDERREVIEW is closed 35 .
Although the cursor is automatically closed whenever a
transaction is terminated, it is good programming practice to
use the CLOSE command to close open cursors prior to terminating
transactions.
When the program user enters a zero in response to the
order number prompt 10 , the program
terminates by performing paragraph A500-TERMINATE-PROGRAM 4 ,
which executes the RELEASE command.
Printable version
