HP 3000 Manuals HP 3000 Manuals
Sample Program Using BULK Processing
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
CONNECT-DBENVIRONMENT 3 , which executes the CONNECT command 52 .
The program then executes paragraph CREATE-ORDER through
CREATE-ORDER-EXIT until the DONE-FLAG contains an X 4 .
Paragraph CREATE-ORDER prompts for a vendor number or a zero 7 . When
the user enters a zero, an X is moved to DONE-FLAG 8 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 VALIDATE-VENDOR is executed 9 .
Paragraph VALIDATE-VENDOR starts a transaction by performing paragraph
BEGIN-TRANSACTION 38 , which executes the BEGIN WORK command 53 . Then
a SELECT command 39 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 COMMIT-WORK 40 . Paragraph
COMMIT-WORK executes the COMMIT WORK command 54 .
* If the vendor number is not found, COMMIT WORK is executed and a
message displayed to inform the user that the number entered is
invalid 41 . Several flags are set to X so that when control
returns to paragraph CREATE-ORDER, the user is again prompted for
a vendor number.
* If the SELECT command fails, paragraph SQL-STATUS-CHECK is
performed 42 to display any error messages 51 before the
transaction is terminated and the appropriate flags set.
If the vendor number is valid, COBEX9 performs paragraph CREATE-HEADER to
create the order header 10 . The order header consists of a row
containing the vendor number entered, plus two values computed by the
program: ORDERNUMBER and ORDERDATE.
Paragraph CREATE-HEADER starts a transaction 13 , then obtains an
exclusive lock on table PURCHDB.ORDERS 14 . 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 CREATE-HEADER executes a SELECT command to retrieve the
highest order number in PURCHDB.ORDERS 15 . The number retrieved
is incremented by one 16 to assign a number to the order.
* Paragraph CREATE-HEADER then moves the special register word
CURRENT-DATE to variable TODAY 17 . This variable is declared as
an array 2 containing elements that can be concatenated to the
YYYYMMDD format 18 in which ORDERDATE values are stored.
* Paragraph CREATE-HEADER then executes a simple INSERT command 19
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 20 . If the INSERT
command fails, the transaction is terminated with COMMIT WORK, but
an X is moved to HEADER-FLAG 21 so that the user is prompted for
another vendor number when control returns to paragraph
CREATE-ORDER.
To create line items, paragraph CREATE-ORDER performs paragraph
CREATE-ORDER-ITEMS until the DONE-ITEMS-FLAG contains an X 11 .
CREATE-ORDER-ITEMS asks the user whether she wants to specify line items
22 .
If the user wants to create line items, CREATE-ORDER-ITEMS performs
paragraph ITEM-ENTRY through ITEM-ENTRY-EXIT until the DONE-ITEMS-FLAG
contains an X 24 , then performs paragraph BULK-INSERT 25 :
* 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. The paragraph first assigns the order
number and a line number to each row 26 , beginning at one.
ITEM-ENTRY then prompts for a vendor part number 27 , which is
validated by performing paragraph VALIDATE-PART 28 .
VALIDATE-PART starts a transaction 43 . Then it executes a
SELECT command 44 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 45
and a space moved to PART-FLAG. If the part number is invalid,
COMMIT WORK is executed 46 , 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 ITEM-ENTRY.
If the part number is valid, paragraph ITEM-ENTRY completes the
line item. It prompts for values to assign to columns
PURCHASEPRICE, ORDERQTY, and ITEMDUEDATE 29 . The paragraph then
assigns a negative value to the indicator variable for column
RECEIVEDQTY 30 in preparation for inserting a null value into
this column.
ITEM-ENTRY terminates when the user indicates that she does not
want to specify any more line items 32 or when the host variable
array is full 31 .
* Paragraph BULK-INSERT starts a transaction 33 , then executes the
BULK INSERT command 35 . 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 34 . If the BULK INSERT command succeeds,
the COMMIT WORK command is executed 36 and a space moved to
ITEMS-FLAG. If the BULK INSERT command fails, paragraph
ROLLBACK-WORK is executed 37 to process the ROLLBACK WORK
command 55 so that any rows inserted prior to the failure are
rolled back.
If the user does not want to create line items, paragraph
CREATE-ORDER-ITEMS displays the order header by performing paragraph
DISPLAY-HEADER 23 . DISPLAY-HEADER displays the row inserted earlier in
PURCHDB.ORDERS 49 .
If line items were inserted into PURCHDB.ORDERITEMS, paragraph
DISPLAY-ORDER is performed 12 to display the order created.
DISPLAY-ORDER performs paragraph DISPLAY-HEADER 47 to display the order
header. Then it performs paragraph DISPLAY-ITEMS 48 to display each
row inserted into PURCHDB.ORDERITEMS. 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 6 .
![[]](../../pic3k/M011907/FFN973c50.gif)
Figure 9-1. Flow Chart of Program COBEX9
_________________________________________________________
| |
| :RUN COBEX9P |
| 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 |
_________________________________________________________
Figure 9-2. Execution of Program COBEX9
____________________________________________________________
| |
| Begin Work |
| Validating VendPartNumber |
| Commit Work |
| |
| The vendor has no part with the number you specified.|
| |
| 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: 19870603 |
| |
| 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-2. Execution of Program COBEX9 (page 2 of 2)
_____________________________________________________________________________
| |
| |
| * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * |
| * This program illustrates the use of BULK INSERT * |
| * to insert multiple rows at a time. * |
| * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * |
| IDENTIFICATION DIVISION. |
| PROGRAM-ID. COBEX9. |
| AUTHOR. JIM FRANCIS AND KAREN THOMAS. |
| INSTALLATION. HP. |
| DATE-WRITTEN. 20 MAY 1987. |
| DATE-COMPILED. 20 MAY 1987. |
| REMARKS. ILLUSTRATES BULK INSERT. |
| ENVIRONMENT DIVISION. |
| CONFIGURATION SECTION. |
| SOURCE-COMPUTER. HP-3000. |
| OBJECT-COMPUTER. HP-3000. |
| SPECIAL-NAMES. CONSOLE IS TERMINAL-INPUT. |
| INPUT-OUTPUT SECTION. |
| FILE-CONTROL. |
| SELECT CRT ASSIGN TO "$STDLIST". |
| DATA DIVISION. |
| FILE SECTION. |
| FD CRT. |
| 01 PROMPT PIC X(33). |
| 01 PROMPT1 PIC X(42). |
| 01 PROMPT2 PIC X(17). |
| 01 PROMPT3 PIC X(16). |
| 01 PROMPT4 PIC X(11). |
| 01 PROMPT5 PIC X(25). |
| 01 PROMPT6 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). |
_____________________________________________________________________________
Figure 9-3. Program COBEX9: Using BULK INSERT
__________________________________________________________________________
| |
| 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 * * * * * * *|
| $PAGE |
| 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. |
| 01 J PIC S9(4) COMP. |
| 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. |
__________________________________________________________________________
Figure 9-3. Program COBEX9: Using BULK INSERT (page 2 of 10)
_____________________________________________________________________________
| |
| 01 ITEMNUMFORMAT PIC ZZZZZ9. |
| 01 QTYNUMFORMAT PIC ZZZZZ9. |
| 01 DOLLARS PIC $$$,$$$,$$$.99. |
| 01 TODAY. 2 |
| 05 TMONTH PIC X(2). |
| 05 FILLER PIC X(1). |
| 05 TDAY PIC X(2). |
| 05 FILLER PIC X(1). |
| 05 TYEAR PIC X(2). |
| $PAGE |
| PROCEDURE DIVISION. |
| |
| BEGIN. |
| |
| 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 CRT. |
| |
| PERFORM CONNECT-DBENVIRONMENT. 3 |
| |
| PERFORM CREATE-ORDER THRU CREATE-ORDER-EXIT UNTIL DONE. 4 |
| |
| PERFORM TERMINATE-PROGRAM. 5 |
| |
| TERMINATE-PROGRAM. |
| |
| EXEC SQL RELEASE END-EXEC. 6 |
| |
| STOP RUN. |
| |
| CREATE-ORDER. |
| |
| MOVE SPACES TO DONE-ITEMS-FLAG. |
| MOVE "Enter VendorNumber or 0 to STOP> " TO PROMPT. 7 |
_____________________________________________________________________________
Figure 9-3. Program COBEX9: Using BULK INSERT (page 3 of 10)
______________________________________________________________________________
| |
| WRITE PROMPT AFTER ADVANCING 1 LINE. |
| ACCEPT RESPONSE FREE. |
| IF RESPONSE IS ZERO THEN 8 |
| MOVE "X" TO DONE-FLAG |
| GO TO CREATE-ORDER-EXIT |
| ELSE |
| MOVE RESPONSE TO VENDORNUMBER. |
| |
| PERFORM VALIDATE-VENDOR. 9 |
| |
| IF VENDOR-OK THEN PERFORM CREATE-HEADER. 10 |
| |
| IF HEADER-OK THEN |
| PERFORM CREATE-ORDER-ITEMS UNTIL DONE-ITEMS. 11 |
| |
| IF ITEMS-OK THEN PERFORM DISPLAY-ORDER. 12 |
| |
| CREATE-ORDER-EXIT. |
| |
| EXIT. |
| |
| CREATE-HEADER. |
| |
| DISPLAY " ". |
| DISPLAY "Begin Work". |
| |
| PERFORM BEGIN-TRANSACTION. 13 |
| |
| EXEC SQL LOCK TABLE PURCHDB.ORDERS 14 |
| IN EXCLUSIVE MODE |
| END-EXEC. |
| |
| IF SQLCODE NOT = OK THEN |
| PERFORM SQL-STATUS-CHECK |
| PERFORM COMMIT-WORK |
| MOVE "X" TO HEADER-FLAG |
| GO TO CREATE-HEADER-EXIT. |
| |
| EXEC SQL SELECT MAX(ORDERNUMBER) 15 |
| INTO :MAXORDERNUMBER |
| FROM PURCHDB.ORDERS |
| END-EXEC. |
| |
| IF SQLCODE NOT = OK THEN |
| PERFORM SQL-STATUS-CHECK |
| PERFORM COMMIT-WORK |
| MOVE "X" TO HEADER-FLAG |
| GO TO CREATE-HEADER-EXIT. |
______________________________________________________________________________
Figure 9-3. Program COBEX9: Using BULK INSERT (page 4 of 10)
______________________________________________________________________________
| |
| DISPLAY "Calculating OrderNumber". |
| |
| COMPUTE ORDERNUMBER1 = MAXORDERNUMBER + 1. 16 |
| DISPLAY "Calculating OrderDate". |
| MOVE CURRENT-DATE TO TODAY. 17 |
| |
| STRING "19", TYEAR, TMONTH, TDAY 18 |
| DELIMITED BY SIZE INTO ORDERDATE. |
| |
| DISPLAY "INSERT INTO PurchDB.Orders". |
| EXEC SQL INSERT INTO PURCHDB.ORDERS 19 |
| ( ORDERNUMBER, |
| VENDORNUMBER, |
| ORDERDATE ) |
| VALUES (:ORDERNUMBER1, |
| :VENDORNUMBER, |
| :ORDERDATE ) |
| END-EXEC. |
| |
| IF SQLCODE = OK THEN 20 |
| PERFORM COMMIT-WORK |
| MOVE SPACE TO HEADER-FLAG |
| ELSE |
| PERFORM SQL-STATUS-CHECK |
| PERFORM COMMIT-WORK |
| MOVE "X" TO HEADER-FLAG. 21 |
| |
| CREATE-HEADER-EXIT. |
| EXIT. |
| |
| CREATE-ORDER-ITEMS. |
| |
| MOVE "Do you want to specify line items (Y/N)?> " 22 |
| TO PROMPT1. |
| MOVE SPACE TO RESPONSE1. |
| WRITE PROMPT1 AFTER ADVANCING 1 LINE. |
| ACCEPT RESPONSE1. |
| |
| IF RESPONSE1 = "N" OR "n" THEN |
| MOVE "X" TO DONE-ITEMS-FLAG |
| MOVE "X" TO ITEMS-FLAG |
| PERFORM DISPLAY-HEADER 23 |
| ELSE |
| MOVE 1 TO I |
| PERFORM ITEM-ENTRY THRU ITEM-ENTRY-EXIT 24 |
| UNTIL DONE-ITEMS |
| PERFORM BULK-INSERT. 25 |
| |
______________________________________________________________________________
Figure 9-3. Program COBEX9: Using BULK INSERT (page 5 of 10)
_______________________________________________________________________________
| |
| ITEM-ENTRY. |
| MOVE ORDERNUMBER1 TO ORDERNUMBER2(I). 26 |
| MOVE I TO ITEMNUMBER(I). |
| MOVE I TO ITEMNUMFORMAT. |
| DISPLAY " ". |
| DISPLAY "You can specify as many as 25 line items.". |
| DISPLAY " ". |
| DISPLAY "Enter data for ItemNumber " ITEMNUMFORMAT ":". |
| |
| MOVE " VendPartNumber> " TO PROMPT2. 27 |
| WRITE PROMPT2 AFTER ADVANCING 1 LINE. |
| MOVE SPACES TO VENDPARTNUMBER(I). |
| ACCEPT VENDPARTNUMBER(I). |
| |
| PERFORM VALIDATE-PART. 28 |
| |
| IF PART-OK THEN |
| |
| MOVE " PurchasePrice> " TO PROMPT3 29 |
| WRITE PROMPT3 AFTER ADVANCING 1 LINE |
| ACCEPT PURCHASEPRICE(I) FREE |
| |
| MOVE " OrderQty> " TO PROMPT4 |
| WRITE PROMPT4 AFTER ADVANCING 0 LINES |
| ACCEPT ORDERQTY(I) FREE |
| |
| MOVE " ItemDueDate (YYYYMMDD)> " TO PROMPT5 |
| WRITE PROMPT5 AFTER ADVANCING 0 LINES |
| MOVE SPACES TO ITEMDUEDATE(I) |
| ACCEPT ITEMDUEDATE(I) |
| |
| MOVE -1 TO RECEIVEDQTYIND(I) 30 |
| |
| IF I = 25 THEN 31 |
| MOVE "X" TO DONE-ITEMS-FLAG |
| GO TO ITEM-ENTRY-EXIT |
| ELSE |
| DISPLAY " " |
| MOVE "Do you want to specify another line item (Y/N)?> "|
| TO PROMPT6 |
| MOVE SPACE TO RESPONSE1 |
| WRITE PROMPT6 AFTER ADVANCING 1 LINE |
| ACCEPT RESPONSE1 |
| |
| IF RESPONSE1 = "N" OR "n" THEN 32 |
| MOVE "X" TO DONE-ITEMS-FLAG |
| ELSE |
| COMPUTE I = I + 1. |
_______________________________________________________________________________
Figure 9-3. Program COBEX9: Using BULK INSERT (page 6 of 10)
______________________________________________________________________________
| |
| ITEM-ENTRY-EXIT. |
| |
| EXIT. |
| |
| BULK-INSERT. |
| DISPLAY " ". |
| DISPLAY "Begin Work". |
| PERFORM BEGIN-TRANSACTION. 33 |
| |
| MOVE I TO NUMBEROFROWS. 34 |
| MOVE 1 TO STARTINDEX. |
| |
| DISPLAY "BULK INSERT INTO PurchDB.OrderItems". |
| EXEC SQL BULK INSERT INTO PURCHDB.ORDERITEMS 35 |
| ( ORDERNUMBER, |
| ITEMNUMBER, |
| VENDPARTNUMBER, |
| PURCHASEPRICE, |
| ORDERQTY, |
| ITEMDUEDATE, |
| RECEIVEDQTY ) |
| VALUES (:ORDERITEMS, |
| :STARTINDEX, |
| :NUMBEROFROWS) |
| END-EXEC. |
| |
| IF SQLCODE = OK THEN 36 |
| PERFORM COMMIT-WORK |
| MOVE SPACE TO ITEMS-FLAG |
| ELSE |
| PERFORM SQL-STATUS-CHECK |
| PERFORM ROLLBACK-WORK 37 |
| MOVE "X" TO ITEMS-FLAG. |
| |
| |
| VALIDATE-VENDOR. |
| DISPLAY " ". |
| DISPLAY "Begin Work". |
| DISPLAY "Validating VendorNumber". |
| |
| PERFORM BEGIN-TRANSACTION. 38 |
| |
| EXEC SQL SELECT VENDORNUMBER 39 |
| INTO :VENDORNUMBER |
| FROM PURCHDB.VENDORS |
| WHERE VENDORNUMBER = :VENDORNUMBER |
| END-EXEC. |
| |
______________________________________________________________________________
Figure 9-3. Program COBEX9: Using BULK INSERT (page 7 of 10)
______________________________________________________________________________
| |
| IF SQLCODE = OK THEN 40 |
| PERFORM COMMIT-WORK |
| MOVE SPACE TO VENDOR-FLAG |
| ELSE |
| IF SQLCODE = NOTFOUND 41 |
| PERFORM COMMIT-WORK |
| 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 SQL-STATUS-CHECK 42 |
| PERFORM COMMIT-WORK |
| MOVE "X" TO VENDOR-FLAG |
| MOVE "X" TO HEADER-FLAG |
| MOVE "X" TO ITEMS-FLAG. |
| |
| VALIDATE-PART. |
| DISPLAY " ". |
| DISPLAY "Begin Work". |
| DISPLAY "Validating VendPartNumber". |
| |
| PERFORM BEGIN-TRANSACTION. 43 |
| |
| MOVE VENDPARTNUMBER(I) TO PARTSPECIFIED. |
| EXEC SQL SELECT VENDPARTNUMBER 44 |
| INTO :PARTSPECIFIED |
| FROM PURCHDB.SUPPLYPRICE |
| WHERE VENDORNUMBER = :VENDORNUMBER |
| AND VENDPARTNUMBER = :PARTSPECIFIED |
| END-EXEC. |
| |
| IF SQLCODE = OK THEN 45 |
| PERFORM COMMIT-WORK |
| MOVE SPACE TO PART-FLAG |
| ELSE |
| IF SQLCODE = NOTFOUND 46 |
| PERFORM COMMIT-WORK |
| DISPLAY " " |
| DISPLAY "The vendor has no part " |
| "with the number you specified." |
| MOVE "X" TO PART-FLAG |
| ELSE |
| PERFORM SQL-STATUS-CHECK |
| PERFORM COMMIT-WORK |
| MOVE "X" TO PART-FLAG. |
| |
______________________________________________________________________________
Figure 9-3. Program COBEX9: Using BULK INSERT (page 8 of 10)
______________________________________________________________________________
| |
| DISPLAY-ORDER. |
| |
| PERFORM DISPLAY-HEADER. 47 |
| DISPLAY " ". |
| PERFORM DISPLAY-ITEMS VARYING J FROM 1 BY 1 UNTIL J > I. 48 |
| |
| 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. |
| |
| |
| 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". |
| |
| |
| SQL-STATUS-CHECK. 51 |
| |
| IF SQLCODE < DEADLOCK THEN |
| MOVE "X" TO ABORT-FLAG. |
| |
| PERFORM SQLEXPLAIN UNTIL SQLCODE = 0. |
| |
| IF ABORT THEN PERFORM TERMINATE-PROGRAM. |
| |
| SQL-STATUS-CHECK-EXIT. |
| |
| EXIT. |
______________________________________________________________________________
Figure 9-3. Program COBEX9: Using BULK INSERT (page 9 of 10)
______________________________________________________________________________
| |
| SQLEXPLAIN. |
| |
| EXEC SQL SQLEXPLAIN :SQLMESSAGE END-EXEC. |
| |
| DISPLAY SQLMESSAGE. |
| CONNECT-DBENVIRONMENT. |
| |
| DISPLAY "Connect to PartsDBE". |
| |
| EXEC SQL CONNECT TO "PartsDBE" END-EXEC. 52 |
| |
| IF SQLCODE NOT = OK THEN |
| PERFORM SQL-STATUS-CHECK |
| PERFORM TERMINATE-PROGRAM. |
| |
| BEGIN-TRANSACTION. |
| |
| EXEC SQL BEGIN WORK END-EXEC. 53 |
| |
| IF SQLCODE NOT = OK THEN |
| PERFORM SQL-STATUS-CHECK |
| PERFORM TERMINATE-PROGRAM. |
| |
| COMMIT-WORK. |
| |
| DISPLAY "Commit Work". |
| |
| EXEC SQL COMMIT WORK END-EXEC. 54 |
| |
| IF SQLCODE NOT = OK THEN |
| PERFORM SQL-STATUS-CHECK |
| PERFORM TERMINATE-PROGRAM. |
| |
| ROLLBACK-WORK. |
| |
| DISPLAY "Rollback Work". |
| |
| EXEC SQL ROLLBACK WORK END-EXEC. 55 |
| |
| IF SQLCODE NOT = OK THEN |
| PERFORM SQL-STATUS-CHECK |
| PERFORM TERMINATE-PROGRAM. |
______________________________________________________________________________
Figure 9-3. Program COBEX9: Using BULK INSERT (page 10 of 10)