 |
» |
|
|
|
The flow chart in Figure 9-1 summarizes the functionality of
program COBEX9. This program creates orders in the sample
DBEnvironment, PartsDBE. Each order is placed with a
specific vendor, to obtain one or more parts supplied by that vendor.
The order header consists of data from
a row in table PURCHDB.ORDERS:
ORDERNUMBER (defined NOT NULL)
VENDORNUMBER
ORDERDATE
|
An order usually also consists of one or more line items, represented
by one or more rows in table PURCHDB.ORDERITEMS:
ORDERNUMBER (defined NOT NULL)
ITEMNUMBER (defined NOT NULL)
VENDPARTNUMBER
PURCHASEPRICE (defined NOT NULL)
ORDERQTY
ITEMDUEDATE
RECEIVEDQTY
|
Program COBEX9 uses a simple INSERT command to create the order
header and, optionally, a BULK INSERT command to insert
line items.
The runtime dialog for COBEX9 appears in Figure 9-2, and the
source code in Figure 9-3.
To establish a DBE session, COBEX9 performs paragraph
A200-CONNECT-DBENVIRONMENT 3 , which
executes the CONNECT command 6 .
The program then executes paragraph B100-CREATE-ORDER through
B100-EXIT
until the DONE-FLAG contains an X 4 .
Paragraph B100-CREATE-ORDER prompts for a vendor number or
a zero 11 . When the user enters a zero, an X is moved to
DONE-FLAG 12
and the program terminates. When the user enters
a vendor number, COBEX9: Validates the number entered. Creates an order header if the vendor number is valid. Optionally inserts line items if the order header has
been successfully created; the part number for each line item
is validated to ensure the vendor actually supplies the part. Displays the order created.
To validate the vendor number, paragraph
B200-VALIDATE-VENDOR is executed 13 .
Paragraph B200-VALIDATE-VENDOR
starts a transaction by performing paragraph A300-BEGIN-TRANSACTION
18 , which executes the BEGIN WORK command 7 .
Then a SELECT command 19 is processed to determine whether the
vendor number exists in column VENDORNUMBER of table
PURCHDB.VENDORS: If the number exists in table PURCHDB.VENDORS, the vendor number
is valid. A space is moved to VENDOR-FLAG,
and the transaction is terminated
by performing paragraph A400-COMMIT-WORK 20 . Paragraph
A400-COMMIT-WORK executes the COMMIT WORK command 8 . If the vendor number is not found, A400-COMMIT-WORK is performed and
a message displayed to inform the user that the number entered is
invalid 21 . Several flags are set to X so that when control
returns to paragraph B100-CREATE-ORDER, the user is again
prompted for a vendor number. If the SELECT command fails, paragraph S100-SQL-STATUS-CHECK is
performed 22 to display any error messages 55 before
the transaction is terminated and the appropriate flags set.
If the vendor number is valid, COBEX9 performs paragraph
B300-CREATE-HEADER to create the order header 14 . The order header
consists of a row containing
the vendor number entered, plus two values computed
by the program: ORDERNUMBER and ORDERDATE.
Paragraph B300-CREATE-HEADER starts a transaction 23 , then obtains
an exclusive lock on table PURCHDB.ORDERS 24 . Exclusive
access to this table ensures that when the row is inserted, no
row having the same number will have been inserted by another
transaction. The unique index that exists on column ORDERNUMBER prevents
duplicate order numbers in table PURCHDB.ORDERS. Therefore an INSERT
operation fails if it attempts to insert a row having an order number
with a value already in column ORDERNUMBER.
In this case, the exclusive lock does not threaten concurrency. No
operations conducted between the time the lock is obtained and the time
it is released involve operator intervention: Paragraph B300-CREATE-HEADER
executes a SELECT command to
retrieve the highest order number in PURCHDB.ORDERS 25 .
The number retrieved is incremented by one 26 to assign a number to
the order. Paragraph B300-CREATE-HEADER then moves the contents of
special register DATE to variable TODAY 27 .
This variable is declared as an array 2
containing elements that can be concatenated to the YYYYMMDD
format 28 in which ORDERDATE values are stored. Paragraph B300-CREATE-HEADER then executes a simple INSERT
command 29
to insert a row into PURCHDB.ORDERS. If the INSERT command succeeds,
the transaction is terminated with a COMMIT WORK command, and a
space is moved to HEADER-FLAG 30 .
If the INSERT command fails, the transaction is terminated with COMMIT WORK,
but an X is moved to HEADER-FLAG 31
so that the user is prompted for another
vendor number when control returns to paragraph B300-CREATE-ORDER.
To create line items, B100-CREATE-ORDER
performs B400-CREATE-ORDER-ITEMS
until the DONE-ITEMS-FLAG contains an X 15 .
B400-CREATE-ORDER-ITEMS asks the user whether
line items are to be specified 32 . If the user wants to create line items, B400-CREATE-ORDER-ITEMS performs
paragraph C100-ITEM-ENTRY through C100-EXIT until
the DONE-ITEMS-FLAG contains an X 34 ,
then performs paragraph C200-BULK-INSERT 35 : C100-ITEM-ENTRY assigns values to host variable
array ORDERITEMS 1 ; each
row in the array corresponds to one line item, or row in table
PURCHDB.ORDERITEMS.
C100-ITEM-ENTRY then prompts for
a vendor part number 37 , which is validated by performing paragraph
D100-VALIDATE-PART 38 . D100-VALIDATE-PART starts a transaction 51 . Then it executes
a select
command 52 to determine whether the part number entered matches any
part number known to be supplied by the vendor.
If the part number is valid, the COMMIT WORK command is executed 53
and a space moved to PART-FLAG.
If the part number is invalid, COMMIT WORK is executed 54 , and
the user informed that the vendor does not supply any part having
the number specified; then an X is moved to PART-FLAG
so that the user is prompted for another part number when control
returns to paragraph C100-ITEM-ENTRY.
If the part number is valid, paragraph
C100-ITEM-ENTRY computes the row number of the array and
the item number 36 .
It prompts for values to assign to columns PURCHASEPRICE, ORDERQTY, and
ITEMDUEDATE 39 . The paragraph then assigns a negative value
to the indicator variable for column RECEIVEDQTY 40
in preparation for inserting a null value into this column.
C100-ITEM-ENTRY terminates when the host variable array is full 41
or when the user indicates that no more line items 42 43
are to be entered.
Paragraph C200-BULK-INSERT starts a transaction 44 , then
executes the BULK INSERT command 46 . The
line items in array ORDERITEMS are inserted into table
PURCHDB.ORDERITEMS, starting with the first row in the array and
continuing for as many rows as there were line items
specified 45 . If the BULK INSERT command succeeds, the
COMMIT WORK command is executed 47 and a space moved to
ITEMS-FLAG.
If the BULK INSERT command fails,
paragraph A450-ROLLBACK-WORK is executed 48 to process
the ROLLBACK WORK
command 9 so that any rows inserted prior
to the failure are rolled back.
If the user does not want to create line items,
paragraph B400-CREATE-ORDER-ITEMS displays the order header by
performing paragraph C300-DISPLAY-HEADER 33 .
C300-DISPLAY-HEADER displays the row
inserted earlier in PURCHDB.ORDERS 49 .
If line items were inserted into PURCHDB.ORDERITEMS,
paragraph
C300-DISPLAY-HEADER is performed 16 to display the order
header.
Then C400-DISPLAY-ITEMS 17 is performed
to display each row inserted into PURCHDB.ORDERITEMS.
C400-DISPLAY-ITEMS displays values from array ORDERITEMS 50 .
When the program user enters a zero in response to the vendor
number prompt, the program terminates by performing paragraph
TERMINATE-PROGRAM 5 , which executes
the RELEASE command 10 . |
Printable version
