 |
» |
|
|
|
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 . Figure 9-1 Flow Chart of Program COBEX9
![[Flow Chart of Program COBEX9]](img/x0701.gif)
Figure 9-2 Runtime Dialog of Program COBEX9
Program to Create an Order - COBEX9
Event List:
Connect to PartsDBE
Prompt for VendorNumber
Validate VendorNumber
INSERT a row into PurchDB.Orders
Prompt for line items
Validate VendPartNumber for each line item
BULK INSERT rows into PurchDB.OrderItems
Repeat the above six steps until user enters 0
Release PartsDBE
Connect to PartsDBE
Enter VendorNumber or 0 to STOP> 9015
Begin Work
Validating VendorNumber
Commit Work
Begin Work
Calculating OrderNumber
Calculating OrderDate
INSERT INTO PurchDB.Orders
Commit Work
Do you want to specify line items (Y/N)?> y
You can specify as many as 25 line items.
Enter data for ItemNumber 1:
VendPartNumber> 9040
Begin Work
Validating VendPartNumber
Commit Work
PurchasePrice> 1500
OrderQty> 5
ItemDueDate (YYYYMMDD)> 19870630
Do you want to specify another line item (Y/N)?> y
You can specify as many as 25 line items.
Enter data for ItemNumber 2:
VendPartNumber> 9055
Begin Work
Validating VendPartNumber
Commit Work
The vendor has no part with the number you specified.
Do you want to specify another line item (Y/N)?> y
You can specify as many as 25 line items.
Enter data for ItemNumber 2:
VendPartNumber> 9050
Begin Work
Validating VendPartNumber
Commit Work
PurchasePrice> 345
OrderQty> 2
ItemDueDate (YYYYMMDD)> 19870801
Do you want to specify another line item (Y/N)?> n
Begin Work
BULK INSERT INTO PurchDB.OrderItems
Commit Work
The following order has been created:
OrderNumber: 30538
VendorNumber: 9015
OrderDate: Today's Date
ItemNumber: 1
VendPartNumber: 9040
PurchasePrice: $1,500.00
OrderQty: 5
ItemDueDate: 19870630
ReceivedQty: NULL
ItemNumber: 2
VendPartNumber: 9050
PurchasePrice: $345.00
OrderQty: 2
ItemDueDate: 19870801
ReceivedQty: NULL
Enter VendorNumber or 0 to STOP> 0
END OF PROGRAM
|
Figure 9-3 Program COBEX9: Using Bulk Insert
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* This program illustrates the use of BULK INSERT *
* to insert multiple rows at a time. *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
IDENTIFICATION DIVISION.
PROGRAM-ID. COBEX9.
AUTHOR. JIM FRANCIS, KAREN THOMAS, JOANN GRAY
INSTALLATION. HP.
DATE-WRITTEN. 20 OCT 1987.
DATE-COMPILED. 20 OCT 1987.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SOURCE-COMPUTER. HP-9000.
OBJECT-COMPUTER. HP-9000.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
SELECT TERM ASSIGN TO ":CO:".
DATA DIVISION.
FILE SECTION.
FD TERM.
01 PROMPT-USER PIC X(49).
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
* * * * * * BEGIN HOST VARIABLE DECLARATIONS * * * * * * *
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 ORDERNUMBER1 PIC S9(9) COMP.
01 VENDORNUMBER PIC S9(9) COMP.
01 ORDERDATE PIC X(8).
01 PARTSPECIFIED PIC X(16).
01 MAXORDERNUMBER PIC S9(9) COMP.
01 ORDERITEMS. 1
05 EACH-ROW OCCURS 25 TIMES.
10 ORDERNUMBER2 PIC S9(9) COMP.
10 ITEMNUMBER PIC S9(9) COMP.
10 VENDPARTNUMBER PIC X(16).
10 PURCHASEPRICE PIC S9(8)V99 COMP-3.
10 ORDERQTY PIC S9(4) COMP.
10 ITEMDUEDATE PIC X(8).
10 RECEIVEDQTY PIC S9(4) COMP.
10 RECEIVEDQTYIND SQLIND.
01 STARTINDEX PIC S9(4) COMP.
01 NUMBEROFROWS PIC S9(4) COMP.
01 SQLMESSAGE PIC X(132).
EXEC SQL END DECLARE SECTION END-EXEC.
* * * * * * END OF HOST VARIABLE DECLARATIONS * * * * * * *
77 DONE-FLAG PIC X VALUE SPACE.
88 NOT-DONE VALUE SPACE.
88 DONE VALUE 'X'.
77 DONE-ITEMS-FLAG PIC X VALUE SPACE.
88 NOT-DONE-ITEMS VALUE SPACE.
88 DONE-ITEMS VALUE 'X'.
77 VENDOR-FLAG PIC X VALUE SPACE.
88 VENDOR-OK VALUE SPACE.
88 VENDOR-NOT-OK VALUE 'X'.
77 HEADER-FLAG PIC X VALUE SPACE.
88 HEADER-OK VALUE SPACE.
88 HEADER-NOT-OK VALUE 'X'.
77 PART-FLAG PIC X VALUE SPACE.
88 PART-OK VALUE SPACE.
88 PART-NOT-OK VALUE 'X'.
77 ITEMS-FLAG PIC X VALUE SPACE.
88 ITEMS-OK VALUE SPACE.
88 ITEMS-NOT-OK VALUE 'X'.
77 ABORT-FLAG PIC X VALUE SPACE.
88 NOT-STOP VALUE SPACE.
88 ABORT VALUE 'X'.
01 I PIC S9(4) COMP VALUE 0.
01 J PIC S9(4) COMP VALUE 0.
01 OK PIC S9(9) COMP VALUE 0.
01 NOTFOUND PIC S9(9) COMP VALUE 100.
01 DEADLOCK PIC S9(9) COMP VALUE -14024.
01 RESPONSE PIC S9(9) COMP VALUE 0.
01 RESPONSE1 PIC X(4) VALUE SPACE.
01 ORDERNUMFORMAT PIC ZZZZZ9.
01 VENDORNUMFORMAT PIC ZZZZZ9.
01 ITEMNUMFORMAT PIC ZZZZZ9.
01 QTYNUMFORMAT PIC ZZZZZ9.
01 DOLLARS PIC $$$,$$$,$$$.99.
01 TODAY. 2
05 TYEAR PIC X(2).
05 TMONTH PIC X(2).
05 TDAY PIC X(2).
PROCEDURE DIVISION.
A100-MAIN.
ACCEPT PROMPT-USER.
DISPLAY "Program to Create an Order - COBEX9".
DISPLAY " ".
DISPLAY "Event List:".
DISPLAY " Connect to PartsDBE".
DISPLAY " Prompt for VendorNumber".
DISPLAY " Validate VendorNumber".
DISPLAY " INSERT a row into PurchDB.Orders".
DISPLAY " Prompt for line items".
DISPLAY " Validate VendPartNumber for each line item".
DISPLAY " BULK INSERT rows into PurchDB.OrderItems".
DISPLAY " Repeat the above six steps until "
"user enters 0".
DISPLAY " Release PartsDBE".
DISPLAY " ".
OPEN OUTPUT TERM.
PERFORM A200-CONNECT-DBENVIRONMENT THRU A200-EXIT. 3
PERFORM B100-CREATE-ORDER THRU B100-EXIT 4
UNTIL DONE.
PERFORM A500-TERMINATE-PROGRAM THRU A500-EXIT. 5
A100-EXIT.
EXIT.
A200-CONNECT-DBENVIRONMENT.
DISPLAY "Connect to ../sampledb/PartsDBE".
EXEC SQL
CONNECT TO 'sampledb/PartsDBE' 6
END-EXEC.
IF SQLCODE NOT = OK
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT
PERFORM A500-TERMINATE-PROGRAM THRU A500-EXIT.
A200-EXIT.
EXIT.
A300-BEGIN-TRANSACTION.
EXEC SQL
BEGIN WORK 7
END-EXEC.
IF SQLCODE NOT = OK
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT
PERFORM A500-TERMINATE-PROGRAM THRU A500-EXIT.
A300-EXIT.
EXIT.
A400-COMMIT-WORK.
DISPLAY "Commit Work".
EXEC SQL
COMMIT WORK 8
END-EXEC.
IF SQLCODE NOT = OK
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT
PERFORM A500-TERMINATE-PROGRAM THRU A500-EXIT.
A400-EXIT.
EXIT.
A450-ROLLBACK-WORK.
DISPLAY "Rollback Work".
EXEC SQL
ROLLBACK WORK 9
END-EXEC.
IF SQLCODE NOT = OK
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT
PERFORM A500-TERMINATE-PROGRAM THRU A500-EXIT.
A450-EXIT.
EXIT.
A500-TERMINATE-PROGRAM.
EXEC SQL
RELEASE 10
END-EXEC.
STOP RUN.
A500-EXIT.
EXIT.
B100-CREATE-ORDER.
MOVE 0 TO I.
MOVE 0 TO J.
MOVE SPACES TO DONE-ITEMS-FLAG.
MOVE "Enter VendorNumber or 0 to STOP> " TO PROMPT-USER. 11
DISPLAY " ".
WRITE PROMPT-USER.
ACCEPT RESPONSE.
IF RESPONSE = ZERO
MOVE "X" TO DONE-FLAG 12
GO TO B100-EXIT
ELSE
MOVE RESPONSE TO VENDORNUMBER.
PERFORM B200-VALIDATE-VENDOR THRU B200-EXIT. 13
IF VENDOR-OK
PERFORM B300-CREATE-HEADER THRU B300-EXIT. 14
IF HEADER-OK
PERFORM B400-CREATE-ORDER-ITEMS THRU B400-EXIT 15
UNTIL DONE-ITEMS.
IF ITEMS-OK
PERFORM C300-DISPLAY-HEADER THRU C300-EXIT 16
DISPLAY ' '
PERFORM C400-DISPLAY-ITEMS 17
VARYING J FROM 1 BY 1 UNTIL J > I.
B100-EXIT.
EXIT.
B200-VALIDATE-VENDOR.
DISPLAY ' '.
DISPLAY 'Begin Work'.
DISPLAY 'Validating VendorNumber'.
PERFORM A300-BEGIN-TRANSACTION THRU A300-EXIT. 18
EXEC SQL SELECT VENDORNUMBER 19
INTO :VENDORNUMBER
FROM PURCHDB.VENDORS
WHERE VENDORNUMBER = :VENDORNUMBER
END-EXEC.
IF SQLCODE = OK
PERFORM A400-COMMIT-WORK THRU A400-EXIT 20
MOVE SPACE TO VENDOR-FLAG
ELSE
IF SQLCODE = NOTFOUND
PERFORM A400-COMMIT-WORK THRU A400-EXIT 21
DISPLAY ' '
DISPLAY 'No vendor has the VendorNumber you specified.'
MOVE "X" TO VENDOR-FLAG
MOVE "X" TO HEADER-FLAG
MOVE "X" TO ITEMS-FLAG
ELSE
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT 22
PERFORM A400-COMMIT-WORK THRU A400-EXIT
MOVE "X" TO VENDOR-FLAG
MOVE "X" TO HEADER-FLAG
MOVE "X" TO ITEMS-FLAG.
B200-EXIT.
EXIT.
B300-CREATE-HEADER.
DISPLAY ' '.
DISPLAY 'Begin Work'.
PERFORM A300-BEGIN-TRANSACTION THRU A300-EXIT. 23
EXEC SQL
LOCK TABLE PURCHDB.ORDERS 24
IN EXCLUSIVE MODE
END-EXEC.
IF SQLCODE NOT = OK
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT
PERFORM A400-COMMIT-WORK THRU A400-EXIT
MOVE "X" TO HEADER-FLAG
GO TO B300-EXIT.
EXEC SQL SELECT MAX(ORDERNUMBER) 25
INTO :MAXORDERNUMBER
FROM PURCHDB.ORDERS
END-EXEC.
IF SQLCODE NOT = OK
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT
PERFORM A400-COMMIT-WORK THRU A400-EXIT
MOVE "X" TO HEADER-FLAG
GO TO B300-EXIT.
DISPLAY 'Calculating OrderNumber'.
COMPUTE ORDERNUMBER1 = MAXORDERNUMBER + 1. 26
DISPLAY 'Calculating OrderDate'.
ACCEPT TODAY FROM DATE. 27
STRING "19", TYEAR, TMONTH, TDAY 28
DELIMITED BY SIZE INTO ORDERDATE.
DISPLAY 'INSERT INTO PurchDB.Orders'.
EXEC SQL INSERT INTO PURCHDB.ORDERS 29
( ORDERNUMBER,
VENDORNUMBER,
ORDERDATE )
VALUES (:ORDERNUMBER1,
:VENDORNUMBER,
:ORDERDATE )
END-EXEC.
IF SQLCODE = OK
PERFORM A400-COMMIT-WORK THRU A400-EXIT 30
MOVE SPACE TO HEADER-FLAG
ELSE
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT
PERFORM A400-COMMIT-WORK THRU A400-EXIT
MOVE "X" TO HEADER-FLAG. 31
B300-EXIT.
EXIT.
B400-CREATE-ORDER-ITEMS.
MOVE 'Do you want to specify line items (Y/N)?> ' 32
TO PROMPT-USER.
MOVE SPACE TO RESPONSE1.
DISPLAY " ".
WRITE PROMPT-USER.
ACCEPT RESPONSE1.
IF RESPONSE1 NOT = "Y"
AND RESPONSE1 NOT = "y"
MOVE "X" TO DONE-ITEMS-FLAG
MOVE "X" TO ITEMS-FLAG
PERFORM C300-DISPLAY-HEADER THRU C300-EXIT 33
ELSE
MOVE 0 TO I
MOVE SPACES TO ORDERITEMS
MOVE 1 TO ITEMNUMFORMAT
PERFORM C100-ITEM-ENTRY THRU C100-EXIT 34
UNTIL DONE-ITEMS
IF I > 0
PERFORM C200-BULK-INSERT THRU C200-EXIT. 35
B400-EXIT.
EXIT.
C100-ITEM-ENTRY.
DISPLAY ' '.
DISPLAY 'You can specify as many as 25 line items.'.
COMPUTE I = I + 1. 36
MOVE I TO ITEMNUMFORMAT.
DISPLAY ' '.
DISPLAY 'Enter data for ItemNumber ' ITEMNUMFORMAT ':'. 37
MOVE ' VendPartNumber> ' TO PROMPT-USER.
DISPLAY " ".
WRITE PROMPT-USER.
MOVE SPACES TO PARTSPECIFIED.
ACCEPT PARTSPECIFIED.
PERFORM D100-VALIDATE-PART THRU D100-EXIT. 38
IF PART-OK
MOVE ORDERNUMBER1 TO ORDERNUMBER2(I)
MOVE I TO ITEMNUMBER(I)
MOVE I TO ITEMNUMFORMAT
MOVE PARTSPECIFIED TO VENDPARTNUMBER(I) 39
MOVE ' PurchasePrice> ' TO PROMPT-USER
DISPLAY " "
WRITE PROMPT-USER
ACCEPT DOLLARS
MOVE DOLLARS TO PURCHASEPRICE(I)
MOVE ' OrderQty> ' TO PROMPT-USER
DISPLAY " "
WRITE PROMPT-USER
ACCEPT QTYNUMFORMAT
MOVE QTYNUMFORMAT TO ORDERQTY(I)
MOVE ' ItemDueDate (YYYYMMDD)> ' TO PROMPT-USER
DISPLAY " "
WRITE PROMPT-USER
MOVE SPACES TO ITEMDUEDATE(I)
ACCEPT ITEMDUEDATE(I)
MOVE -1 TO RECEIVEDQTYIND(I) 40
IF I = 25
MOVE "X" TO DONE-ITEMS-FLAG 41
GO TO C100-EXIT
ELSE
PERFORM D200-MORE-LINES THRU D200-EXIT
IF RESPONSE1 = "N" OR "n" 42
MOVE "X" TO DONE-ITEMS-FLAG
ELSE
NEXT SENTENCE
* IF THE PART NUMBER WAS NOT GOOD, MORE LINES CAN BE ENTERED *
* FOR THE SAME VENDOR. *
ELSE
PERFORM D200-MORE-LINES THRU D200-EXIT
IF RESPONSE1 = "N" OR "n" 43
MOVE "X" TO DONE-ITEMS-FLAG
COMPUTE I = I - 1
ELSE
COMPUTE I = I - 1.
C100-EXIT.
EXIT.
C200-BULK-INSERT.
DISPLAY ' '.
DISPLAY 'Begin Work'.
PERFORM A300-BEGIN-TRANSACTION THRU A300-EXIT. 44
MOVE I TO NUMBEROFROWS. 45
MOVE 1 TO STARTINDEX.
DISPLAY 'BULK INSERT INTO PurchDB.OrderItems'. 46
EXEC SQL BULK INSERT INTO PURCHDB.ORDERITEMS
( ORDERNUMBER,
ITEMNUMBER,
VENDPARTNUMBER,
PURCHASEPRICE,
ORDERQTY,
ITEMDUEDATE,
RECEIVEDQTY )
VALUES (:ORDERITEMS,
:STARTINDEX,
:NUMBEROFROWS)
END-EXEC.
IF SQLCODE = OK
PERFORM A400-COMMIT-WORK THRU A400-EXIT 47
MOVE SPACE TO ITEMS-FLAG
ELSE
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT
PERFORM A450-ROLLBACK-WORK THRU A450-EXIT 48
MOVE "X" TO ITEMS-FLAG.
C200-EXIT.
EXIT.
C300-DISPLAY-HEADER. 49
DISPLAY ' '.
DISPLAY 'The following order has been created:'
DISPLAY ' '.
MOVE ORDERNUMBER1 TO ORDERNUMFORMAT
DISPLAY ' OrderNumber: ' ORDERNUMFORMAT.
MOVE VENDORNUMBER TO VENDORNUMFORMAT.
DISPLAY ' VendorNumber: ' VENDORNUMFORMAT.
DISPLAY ' OrderDate: ' ORDERDATE.
C300-EXIT.
EXIT.
C400-DISPLAY-ITEMS. 50
DISPLAY ' '.
MOVE ITEMNUMBER(J) TO ITEMNUMFORMAT.
DISPLAY ' ItemNumber: ' ITEMNUMFORMAT.
DISPLAY ' VendPartNumber: ' VENDPARTNUMBER(J).
MOVE PURCHASEPRICE(J) TO DOLLARS.
DISPLAY ' PurchasePrice: ' DOLLARS.
MOVE ORDERQTY(J) TO QTYNUMFORMAT.
DISPLAY ' OrderQty: ' QTYNUMFORMAT.
DISPLAY ' ItemDueDate: ' ITEMDUEDATE(J).
DISPLAY ' ReceivedQty: NULL'.
C400-EXIT.
EXIT.
D100-VALIDATE-PART.
DISPLAY ' '.
DISPLAY 'Begin Work'.
DISPLAY 'Validating VendPartNumber'.
PERFORM A300-BEGIN-TRANSACTION THRU A300-EXIT. 51
EXEC SQL SELECT VENDPARTNUMBER 52
INTO :PARTSPECIFIED
FROM PURCHDB.SUPPLYPRICE
WHERE VENDORNUMBER = :VENDORNUMBER
AND VENDPARTNUMBER = :PARTSPECIFIED
END-EXEC.
IF SQLCODE = OK
PERFORM A400-COMMIT-WORK THRU A400-EXIT 53
MOVE SPACE TO PART-FLAG
ELSE
IF SQLCODE = NOTFOUND
PERFORM A400-COMMIT-WORK THRU A400-EXIT 54
DISPLAY ' '
DISPLAY 'The vendor has no part '
'with the number you specified.'
MOVE "X" TO PART-FLAG
ELSE
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT
PERFORM A400-COMMIT-WORK THRU A400-EXIT
MOVE "X" TO PART-FLAG.
D100-EXIT.
EXIT.
D200-MORE-LINES.
DISPLAY ' '
MOVE 'Do you want to specify another line item (Y/N)?> '
TO PROMPT-USER
MOVE SPACE TO RESPONSE1
DISPLAY " "
WRITE PROMPT-USER
ACCEPT RESPONSE1.
D200-EXIT.
EXIT.
S100-SQL-STATUS-CHECK.
IF SQLCODE < DEADLOCK
MOVE 'X' TO ABORT-FLAG.
PERFORM S200-SQLEXPLAIN 55
UNTIL SQLCODE = 0.
IF ABORT
PERFORM A500-TERMINATE-PROGRAM THRU A500-EXIT.
S100-EXIT.
EXIT.
S200-SQLEXPLAIN.
EXEC SQL
SQLEXPLAIN :SQLMESSAGE
END-EXEC.
DISPLAY SQLMESSAGE.
S200-EXIT.
EXIT.
|
|
