HP 3000 Manuals HP 3000 Manuals
Sample Program COBEX7 Using Simple DML Commands
The flow chart shown in Figure 7-1 summarizes the functionality of
program COBEX7. This program uses the four simple data manipulation
commands to operate on the PURCHDB.VENDORS table. COBEX7 uses a function
menu to determine whether to execute one or more SELECT, UPDATE, DELETE,
or INSERT operations. Each execution of a simple data manipulation
command is done in a separate transaction.
The runtime dialog for program COBEX7 appears in Figure 7-2, and the
source code in Figure 7-3.
Paragraph A200-CONNECT-DBENVIRONMENT starts a DBE session 1 . This
paragraph executes the CONNECT command 3 for the sample DBEnvironment,
PartsDBE.
The operation performed next depends on the number entered when a
function menu is displayed 7 :
* The program terminates if 0 is entered.
* Paragraph C100-SELECT-DATA is executed if 1 is entered.
* Paragraph C200-UPDATE-DATA is executed if 2 is entered.
* Paragraph C300-DELETE-DATA is executed if 3 is entered.
* Paragraph C400-INSERT-DATE is executed if 4 is entered.
Paragraph C100-SELECT-DATA 8 prompts for a vendor number or a 0 9 .
If a 0 is entered, the function menu is re-displayed. If a vendor number
is entered, paragraph A300-BEGIN-TRANSACTION is executed 10 to issue
the BEGIN WORK command 4 . Then paragraph D200-SQL-SELECT is performed
11 to retrieve all data for the specified vendor from PURCHDB.VENDORS
50 . The SQLCODE returned is examined to determine the next action:
* If no rows qualify for the SELECT operation, a message 13 is
displayed and the transaction is terminated 15 . Paragraph
A400-COMMIT-WORK terminates the transaction by executing the
COMMIT WORK command 5 . The user is then re-prompted for a
vendor number or a 0.
* If the SELECT command execution results in an error condition,
paragraph S100-SQL-STATUS-CHECK is executed 14 . This paragraph
executes SQLEXPLAIN 51 to display all error messages. Then the
transaction is terminated 15 and the user re-prompted for a
vendor number or a 0.
* If the SELECT command can be successfully executed, paragraph
D100-DISPLAY-ROW 12 is executed to display the row. This
paragraph examines the null indicators for each of the three
potentially null columns (CONTACTNAME, PHONENUMBER, and
VENDORREMARKS). If any null indicator contains a value less than 0
49 , a message indicating that the value is null is displayed.
After the row is completely displayed, the transaction is
terminated 15 and the user re-prompted for a vendor number or
Paragraph C200-UPDATE-DATA 16 lets the user UPDATE the value of a
column only if it contains a null value. The paragraph prompts for
a vendor number or a 0 17 . If a 0 is entered, the function
menu is re-displayed. If a vendor number is entered, paragraph
A300-BEGIN-TRANSACTION is executed 18 . Then a SELECT command is
executed to retrieve data from PURCHDB.VENDORS for the vendor specified
19 .
The SQLCODE returned is examined to determine the next action:
* If no rows qualify for the SELECT operation, a message 21 is
displayed and the transaction is terminated 23 . The user is
then re-prompted for a vendor number or a 0.
* If the SELECT command execution results in an error condition,
paragraph S100-SQL-STATUS-CHECK is executed 22 . Then the
transaction is terminated 23 and the user re-prompted for vendor
number or a 0.
* If the SELECT command can be successfully executed, paragraph
C250-DISPLAY-UPDATE 20 is executed. This paragraph executes
paragraph D100-DISPLAY-ROW to display the row retrieved 24 . The
paragraph then determines whether the row contains any null
values. This is the case if any of the three potentially null
columns contains a non-zero value 25 .
If no null values exist, a message is displayed 26 and the
transaction is terminated 23 ; the user is then re-prompted for a
vendor number or a 0.
If there are any null values, the null indicators are examined to
determine which of them contain a negative value 27 . A negative
null indicator means the column contains a null value, and the
user is prompted for a new value 28 . If the user enters a 0,
the program assigns a -1 to the null indicator 29 so that when
the UPDATE command 30 is executed, a null value is assigned to
that column. If a non-zero value is entered, the program assigns
a 0 to the null indicator so that the value specified is assigned
to that column. After the UPDATE 30 command is executed, the
transaction is terminated 23 and the user re-prompted for a
vendor number or a 0.
Paragraph C300-DELETE-DATA 31 lets the user DELETE one row. The
paragraph prompts for a vendor number or a 0 32 . If a 0 is entered,
the function menu is re-displayed. If a vendor number is entered,
paragraph A300-BEGIN-TRANSACTION is executed 33 . Then a SELECT command
is executed to retrieve all data for the vendor specified from
PURCHDB.VENDORS 34 The SQLCODE returned is examined to determine the
next action:
* If no rows qualify for the SELECT operation, a message 36 is
displayed and the transaction is terminated 38 . The user is
then re-prompted for a vendor number or a 0.
* If the SELECT command execution results in an error condition,
paragraph S100-SQL-STATUS-CHECK is executed 37 . Then the
transaction is terminated 38 and the user re-prompted for vendor
number or a 0.
* If the SELECT command can be successfully executed, paragraph
C350-DISPLAY-DELETE 35 is executed. This paragraph executes
paragraph D100-DISPLAY-ROW to display the row retrieved 39 .
Then the user is asked whether the row is to be deleted 40 . If
not, the transaction is terminated 38 and the user re-prompted
for a vendor number or a 0. If so, the DELETE command 41 is
executed before the transaction is terminated 38 and the user
re-prompted.
Paragraph C400-INSERT-DATA 42 lets the user INSERT one row. The
paragraph prompts for a vendor number or a 0 43 . If a 0 is entered,
the function menu is re-displayed. If a vendor number is entered, the
user is prompted for values for each column. The user can enter a 0 to
specify a null value for potentially null columns 44 ; to assign a null
value, the program assigns a -1 to the appropriate null indicator 45 .
After a transaction is started 46 , an INSERT command 47 is used to
insert a row containing the specified values. After the INSERT
operation, the transaction is terminated 48 , and the user re-prompted
for a vendor number or a 0.
When the user enters a 0 in response to the function menu display, the
program terminates by executing paragraph A500-TERMINATE-PROGRAM 2 .
This paragraph executes the RELEASE command 6 .
![[]](../../pic3k/M011907/FFN653c50.gif)
Figure 7-1. Flow Chart of Program COBEX7
![[]](../../pic3k/M011907/FFN663c50.gif)
Figure 7-1. Flow Chart of Program COBEX7 (page 2 of 2)
_________________________________________________________________________
| |
| :RUN COBEX7P |
| Program for Simple Data Manipulation of Vendors Table - COBEX7 |
| |
| Connect to PartsDBE |
| |
| 1 . . . . SELECT rows from PurchDB.Vendors table |
| 2 . . . . UPDATE rows with null values in PurchDB.Vendors table|
| 3 . . . . DELETE rows from PuchDB.Vendors table |
| 4 . . . . INSERT rows into PurchDB.Vendors table |
| |
| Enter choice or 0 to stop> 4 |
| |
| *** Procedure to INSERT rows into PurchDB.Vendors *** |
| |
| Enter Vendor Number or 0 for MENU> 9016 |
| |
| Enter Vendor Name> Wolfe Works |
| |
| Enter Contact Name (0 for null)> Stanley Wolfe |
| |
| Enter Phone Number (0 for null)> 408 975 6061 |
| |
| Enter Vendor Street> 7614 Canine Way |
| |
| Enter Vendor City> San Jose |
| |
| Enter Vendor State> CA |
| |
| Enter Vendor Zip Code> 90016 |
| |
| Enter Vendor Remarks (0 for null)> 0 |
| |
| Begin Work |
| INSERT row into PurchDB.Vendors |
| Commit Work |
| |
| Enter Vendor Number or 0 for MENU> 0 |
| |
| 1 . . . SELECT rows from PurchDB.Vendors table |
| 2 . . . UPDATE rows with null values in PurchDB.Vendors table |
| 3 . . . DELETE rows from PurchDB.Vendors table |
| 4 . . . INSERT rows into PurchDB.Vendors table |
| |
| Enter choice or 0 to STOP> 1 |
_________________________________________________________________________
Figure 7-2. Runtime Dialog of Program COBEX7
_______________________________________________________________________
| |
| *** Procedure to SELECT rows from PurchDB.Vendors *** |
| |
| Enter Vendor Number or 0 for MENU> 9016 |
| |
| Begin Work |
| SELECT * from PurchDB.Vendors |
| |
| VendorNumber: 9016 |
| VendorName: Wolfe Works |
| ContactName: Stanley Wolfe |
| PhoneNumber: 408 975 6061 |
| VendorStreet: 7614 Canine Way |
| VendorCity: San Jose |
| VendorState: CA |
| VendorZipCode: 90016 |
| VendorRemarks is NULL |
| |
| Commit Work |
| |
| Enter Vendor Number or 0 for MENU> 0 |
| |
| 1 . . . SELECT rows from PurchDB.Vendors table |
| 2 . . . UPDATE rows with null values in PurchDB.Vendors table|
| 3 . . . DELETE rows from PurchDB.Vendors table |
| 4 . . . INSERT rows into PurchDB.Vendors table |
| |
| Enter choice or 0 to STOP> 2 |
| |
| *** Procedure to UPDATE rows in PurchDB.Vendors *** |
| |
| Enter Vendor Number or 0 for MENU> 9016 |
| |
| Begin Work |
| SELECT * from PurchDB.Vendors |
| |
| VendorNumber: 9016 |
| VendorName: Wolfe Works |
| ContactName: Stanley Wolfe |
| PhoneNumber: 408 975 6061 |
| VendorStreet: 7614 Canine Way |
| VendorCity: San Jose |
| VendorState: CA |
| VendorZipCode: 90016 |
| VendorRemarks is NULL |
| |
| Enter new VendorRemarks (0 for null)> can expedite shipments |
| Commit Work |
_______________________________________________________________________
Figure 7-2. Runtime Dialog of Program COBEX7 (page 2 of 3)
_______________________________________________________________________
| |
| Enter Vendor Number or 0 for MENU> 0 |
| |
| 1 . . . SELECT rows from PurchDB.Vendors table |
| 2 . . . UPDATE rows with null values in PurchDB.Vendors table|
| 3 . . . DELETE rows from PurchDB.Vendors table |
| 4 . . . INSERT rows into PurchDB.Vendors table |
| |
| Enter choice or 0 to STOP> 3 |
| |
| *** Procedure to DELETE rows from PurchDB.Vendors *** |
| |
| Enter Vendor Number or 0 for MENU> 9016 |
| |
| Begin Work |
| SELECT * from PurchDB.Vendors |
| |
| VendorNumber: 9016 |
| VendorName: Wolfe Works |
| ContactName: Stanley Wolfe |
| PhoneNumber: 408 975 6061 |
| VendorStreet: 7614 Canine Way |
| VendorCity: San Jose |
| VendorState: CA |
| VendorZipCode: 90016 |
| VendorRemarks: can expedite shipments |
| |
| Is it OK to DELETE this row (N/Y)? > Y |
| |
| DELETE row from PurchDB.Vendors |
| |
| Commit Work |
| |
| Enter Vendor Number or 0 for MENU> 0 |
| |
| 1 . . . SELECT rows from PurchDB.Vendors table |
| 2 . . . UPDATE rows with null values in PurchDB.Vendors table|
| 3 . . . DELETE rows from PurchDB.Vendors table |
| 4 . . . INSERT rows into PurchDB.Vendors table |
| |
| Enter choice or 0 to STOP> 0 |
| |
| END OF PROGRAM |
| |
| : |
_______________________________________________________________________
Figure 7-2. Runtime Dialog of Program COBEX7 (page 3 of 3)
_________________________________________________________________________
| |
| |
| |
| |
| * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *|
| * This program illustrates simple data manipulation. It uses *|
| * the UPDATE command with indicator variables to update any *|
| * row in the Vendors table that contains null values. It *|
| * also uses indicator variables in conjunction with SELECT *|
| * and INSERT. The DELETE command is also illustrated. *|
| * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *|
| IDENTIFICATION DIVISION. |
| PROGRAM-ID. COBEX7. |
| AUTHOR. JIM FRANCIS, KAREN THOMAS, JOANN GRAY |
| INSTALLATION. HP. |
| DATE-WRITTEN. 14 OCT 1987. |
| DATE-COMPILED. 14 OCT 1987. |
| |
| ENVIRONMENT DIVISION. |
| CONFIGURATION SECTION. |
| SOURCE-COMPUTER. HP-3000. |
| OBJECT-COMPUTER. HP-3000. |
| |
| INPUT-OUTPUT SECTION. |
| |
| FILE-CONTROL. |
| SELECT CRT ASSIGN TO "$STDLIST". |
| |
| DATA DIVISION. |
| |
| FILE SECTION. |
| FD CRT. |
| 01 PROMPT PIC X(40). |
| |
| WORKING-STORAGE SECTION. |
| |
| EXEC SQL INCLUDE SQLCA END-EXEC. |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
_________________________________________________________________________
Figure 7-3. Using INSERT, UPDATE, SELECT and DELETE
_______________________________________________________________________
| |
| |
| |
| * * * * * * BEGIN HOST VARIABLE DECLARATIONS * * * * * * *|
| EXEC SQL BEGIN DECLARE SECTION END-EXEC. |
| 01 VENDORNUMBER PIC S9(4) COMP. |
| 01 VENDORNAME PIC X(30). |
| 01 CONTACTNAME PIC X(30). |
| 01 CONTACTNAMEIND SQLIND. |
| 01 PHONENUMBER PIC X(15). |
| 01 PHONENUMBERIND SQLIND. |
| 01 VENDORSTREET PIC X(30). |
| 01 VENDORCITY PIC X(20). |
| 01 VENDORSTATE PIC X(2). |
| 01 VENDORZIPCODE PIC X(10). |
| 01 VENDORREMARKS. |
| 49 REMARKSLENGTH PIC S9(9) COMP. |
| 49 REMARKS-DATA PIC X(60). |
| 01 VENDORREMARKSIND SQLIND. |
| |
| 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 FUNC-DONE-FLAG PIC X VALUE SPACE. |
| 88 FUNC-NOT-DONE VALUE SPACE. |
| 88 FUNC-DONE VALUE 'X'. |
| |
| 77 ABORT-FLAG PIC X VALUE SPACE. |
| 88 NOT-STOP VALUE SPACE. |
| 88 ABORT VALUE 'X'. |
| 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. |
| 05 RESPONSE-PREFIX PIC X(1) VALUE SPACE. |
| 05 RESPONSE-SUFFIX PIC X(15) VALUE SPACES. |
| 01 RESPONSE1 PIC S9(9) COMP. |
| 01 COUNTER PIC S9(4) COMP. |
| 01 NUMFORMAT PIC ZZZZZ9. |
| |
| PROCEDURE DIVISION. |
_______________________________________________________________________
Figure 7-3. Using INSERT, UPDATE, SELECT and DELETE (page 2 of 14)
______________________________________________________________________________
| |
| A100-MAIN. |
| |
| DISPLAY "Program for Simple Data Manipulation of Vendors Tabl |
| - "e - COBEX7" |
| DISPLAY " ". |
| |
| OPEN OUTPUT CRT. |
| |
| PERFORM A200-CONNECT-DBENVIRONMENT THRU A200-EXIT. 1 |
| |
| PERFORM B100-DISPLAY-MENU THRU B100-EXIT |
| UNTIL DONE. |
| |
| PERFORM A500-TERMINATE-PROGRAM THRU A500-EXIT. 2 |
| |
| A100-EXIT. |
| EXIT. |
| |
| A200-CONNECT-DBENVIRONMENT. |
| |
| DISPLAY "Connect to PartsDBE". |
| EXEC SQL |
| CONNECT TO 'PartsDBE' 3 |
| 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. |
| |
| DISPLAY " ". |
| DISPLAY "Begin Work". |
| EXEC SQL |
| BEGIN WORK 4 |
| END-EXEC. |
| |
| IF SQLCODE NOT = OK |
| PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT |
| PERFORM A500-TERMINATE-PROGRAM THRU A500-EXIT. |
| |
| |
| |
______________________________________________________________________________
Figure 7-3. Using INSERT, UPDATE, SELECT and DELETE (page 3 of 14)
______________________________________________________________________________
| |
| |
| |
| |
| |
| A300-EXIT. |
| EXIT. |
| |
| A400-COMMIT-WORK. |
| |
| DISPLAY " ". |
| DISPLAY "Commit Work". |
| EXEC SQL |
| COMMIT WORK 5 |
| END-EXEC. |
| |
| IF SQLCODE NOT = OK |
| PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT |
| PERFORM A500-TERMINATE-PROGRAM THRU A500-EXIT. |
| |
| A400-EXIT. |
| EXIT. |
| |
| A500-TERMINATE-PROGRAM. |
| |
| EXEC SQL |
| RELEASE 6 |
| END-EXEC. |
| |
| STOP RUN. |
| |
| A500-EXIT. |
| EXIT. |
| |
| B100-DISPLAY-MENU. |
| |
| DISPLAY " 1 . . . SELECT rows from PurchDB.Vendors table ". |
| DISPLAY " 2 . . . UPDATE rows with null values " |
| "in PurchDB.Vendors table ". |
| DISPLAY " 3 . . . DELETE rows from PurchDB.Vendors table". |
| DISPLAY " 4 . . . INSERT rows into PurchDB.Vendors table". |
| MOVE "Enter choice or 0 to STOP > " TO PROMPT. |
| |
| |
| |
| |
| |
______________________________________________________________________________
Figure 7-3. Using INSERT, UPDATE, SELECT and DELETE (page 4 of 14)
______________________________________________________________________________
| |
| |
| |
| |
| |
| |
| WRITE PROMPT AFTER ADVANCING 1 LINE. |
| ACCEPT RESPONSE1 FREE. 7 |
| IF RESPONSE1 = ZERO |
| MOVE "X" TO DONE-FLAG |
| GO TO B100-EXIT. |
| MOVE SPACES TO FUNC-DONE-FLAG. |
| |
| IF RESPONSE1 = 1 |
| DISPLAY " " |
| DISPLAY " *** Procedure to SELECT rows from PurchDB.Vendo |
| - "rs *** " |
| DISPLAY " " |
| PERFORM C100-SELECT-DATA THRU C100-EXIT |
| UNTIL FUNC-DONE. |
| |
| IF RESPONSE1 = 2 |
| DISPLAY " " |
| DISPLAY " *** Procedure to UPDATE rows in PurchDB.Vendors |
| - " *** " |
| DISPLAY " " |
| PERFORM C200-UPDATE-DATA THRU C200-EXIT |
| UNTIL FUNC-DONE. |
| |
| IF RESPONSE1 = 3 |
| DISPLAY " " |
| DISPLAY " *** Procedure to DELETE rows from PurchDB.Vendo |
| - "rs *** " |
| DISPLAY " " |
| PERFORM C300-DELETE-DATA THRU C300-EXIT |
| UNTIL FUNC-DONE. |
| |
| IF RESPONSE1 = 4 |
| DISPLAY " *** Procedure to INSERT rows into PurchDB.Vendo |
| - "rs *** " |
| PERFORM C400-INSERT-DATA THRU C400-EXIT |
| UNTIL FUNC-DONE. |
| |
| |
| |
| |
| |
______________________________________________________________________________
Figure 7-3. Using INSERT, UPDATE, SELECT and DELETE (page 5 of 14)
______________________________________________________________________________
| |
| |
| |
| |
| IF RESPONSE1 NOT = 0 |
| AND RESPONSE1 NOT = 1 |
| AND RESPONSE1 NOT = 2 |
| AND RESPONSE1 NOT = 3 |
| AND RESPONSE1 NOT = 4 |
| |
| DISPLAY "Enter 0-4 only, please". |
| |
| B100-EXIT. |
| |
| C100-SELECT-DATA. 8 |
| |
| MOVE "Enter VendorNumber or 0 for MENU> " TO PROMPT. 9 |
| WRITE PROMPT. |
| ACCEPT RESPONSE1 FREE. |
| IF RESPONSE1 = ZERO |
| MOVE "X" TO FUNC-DONE-FLAG |
| GO TO C100-EXIT |
| ELSE |
| MOVE RESPONSE1 TO VENDORNUMBER. |
| |
| PERFORM A300-BEGIN-TRANSACTION THRU A300-EXIT. 10 |
| |
| DISPLAY "SELECT * from PurchDB.Vendors". |
| |
| PERFORM D200-SQL-SELECT THRU D200-EXIT. 11 |
| |
| IF SQLCODE = OK |
| PERFORM D100-DISPLAY-ROW THRU D100-EXIT 12 |
| ELSE |
| IF SQLCODE = NOTFOUND |
| DISPLAY "Row not found!" 13 |
| ELSE |
| PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT. 14 |
| |
| PERFORM A400-COMMIT-WORK THRU A400-EXIT. 15 |
| |
| C100-EXIT. |
| EXIT. |
| |
| |
| |
| |
______________________________________________________________________________
Figure 7-3. Using INSERT, UPDATE, SELECT and DELETE (page 6 of 14)
______________________________________________________________________________
| |
| |
| |
| C200-UPDATE-DATA. 16 |
| |
| MOVE "Enter VendorNumber or 0 for MENU> " TO PROMPT. |
| DISPLAY " ". |
| WRITE PROMPT. |
| ACCEPT RESPONSE1 FREE. |
| IF RESPONSE1 = ZERO 17 |
| MOVE "X" TO FUNC-DONE-FLAG |
| GO TO C200-EXIT |
| ELSE |
| MOVE RESPONSE1 TO VENDORNUMBER. |
| |
| PERFORM A300-BEGIN-TRANSACTION THRU A300-EXIT. 18 |
| |
| DISPLAY "SELECT * from PurchDB.Vendors". |
| |
| PERFORM D200-SQL-SELECT THRU D200-EXIT. 19 |
| |
| IF SQLCODE = OK |
| PERFORM C250-DISPLAY-UPDATE THRU C250-EXIT 20 |
| ELSE |
| IF SQLCODE = NOTFOUND 21 |
| DISPLAY "Row not found!" |
| ELSE |
| PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT. 22 |
| |
| PERFORM A400-COMMIT-WORK THRU A400-EXIT. 23 |
| |
| C200-EXIT. |
| EXIT. |
| |
| C250-DISPLAY-UPDATE. |
| |
| PERFORM D100-DISPLAY-ROW THRU D100-EXIT. 24 |
| |
| IF CONTACTNAMEIND = 0 25 |
| AND PHONENUMBERIND = 0 |
| AND VENDORREMARKSIND = 0 |
| |
| DISPLAY " No null values exist for this vendor." 26 |
| GO TO C250-EXIT. |
| |
| |
| |
______________________________________________________________________________
Figure 7-3. Using INSERT, UPDATE, SELECT and DELETE (page 7 of 14)
______________________________________________________________________________
| |
| |
| IF CONTACTNAMEIND < 0 27 |
| MOVE SPACES TO CONTACTNAME |
| MOVE "Enter New ContactName (0 for NULL)> " TO PROMPT 28 |
| WRITE PROMPT |
| ACCEPT CONTACTNAME FREE. |
| |
| IF PHONENUMBERIND < 0 |
| MOVE SPACES TO PHONENUMBER |
| MOVE "Enter New PhoneNumber (0 for NULL)> " TO PROMPT |
| WRITE PROMPT |
| ACCEPT PHONENUMBER FREE. |
| |
| IF VENDORREMARKSIND < 0 |
| MOVE SPACES TO VENDORREMARKS |
| MOVE "Enter New VendorRemarks (0 for NULL)> " TO PROMPT |
| WRITE PROMPT |
| ACCEPT REMARKS FREE. |
| |
| IF CONTACTNAME = 0 29 |
| MOVE -1 TO CONTACTNAMEIND |
| ELSE |
| MOVE 0 TO CONTACTNAMEIND. |
| |
| IF PHONENUMBER = 0 |
| MOVE -1 TO PHONENUMBERIND |
| ELSE |
| MOVE 0 TO PHONENUMBERIND. |
| |
| IF VENDORREMARKS = 0 |
| MOVE -1 TO VENDORREMARKSIND |
| ELSE |
| MOVE 0 TO VENDORREMARKSIND. |
| |
| EXEC SQL UPDATE PURCHDB.VENDORS 30 |
| SET CONTACTNAME = :CONTACTNAME |
| :CONTACTNAMEIND, |
| PHONENUMBER = :PHONENUMBER |
| :PHONENUMBERIND, |
| VENDORREMARKS = :VENDORREMARKS |
| :VENDORREMARKSIND |
| WHERE VENDORNUMBER = :VENDORNUMBER |
| END-EXEC. |
| |
| |
| |
______________________________________________________________________________
Figure 7-3. Using INSERT, UPDATE, SELECT and DELETE (page 8 of 14)
______________________________________________________________________________
| |
| IF SQLCODE NOT = OK |
| PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT. |
| |
| C250-EXIT. |
| EXIT. |
| |
| C300-DELETE-DATA. 31 |
| |
| MOVE "Enter VendorNumber or 0 for MENU> " TO PROMPT. 32 |
| WRITE PROMPT. |
| ACCEPT RESPONSE1 FREE. |
| IF RESPONSE1 = ZERO |
| MOVE "X" TO FUNC-DONE-FLAG |
| GO TO C300-EXIT |
| ELSE |
| MOVE RESPONSE1 TO VENDORNUMBER. |
| |
| PERFORM A300-BEGIN-TRANSACTION THRU A300-EXIT. 33 |
| |
| DISPLAY "SELECT * from PurchDB.Vendors". |
| |
| PERFORM D200-SQL-SELECT THRU D200-EXIT. 34 |
| |
| IF SQLCODE = OK |
| PERFORM C350-DISPLAY-DELETE THRU C350-EXIT 35 |
| ELSE |
| IF SQLCODE = NOTFOUND |
| DISPLAY " " |
| DISPLAY "Row not found!" 36 |
| ELSE |
| PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT. 37 |
| |
| PERFORM A400-COMMIT-WORK THRU A400-EXIT. 38 |
| |
| C300-EXIT. |
| EXIT. |
| |
| C350-DISPLAY-DELETE. |
| |
| PERFORM D100-DISPLAY-ROW THRU D100-EXIT. 39 |
| |
| MOVE "Is it OK to DELETE this row (N/Y) ? > " 40 |
| TO PROMPT. |
| WRITE PROMPT. |
| ACCEPT RESPONSE FREE. |
| |
______________________________________________________________________________
Figure 7-3. Using INSERT, UPDATE, SELECT and DELETE (page 9 of 14)
______________________________________________________________________________
| |
| |
| |
| |
| IF RESPONSE-PREFIX = "Y" |
| OR RESPONSE-PREFIX = "y" |
| DISPLAY "DELETE row from PurchDB.Vendors" |
| EXEC SQL |
| DELETE FROM PURCHDB.VENDORS 41 |
| WHERE VENDORNUMBER = :VENDORNUMBER |
| END-EXEC. |
| |
| IF SQLCODE NOT = OK |
| PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT. |
| |
| C350-EXIT. |
| |
| C400-INSERT-DATA. 42 |
| |
| MOVE "Enter Vendor Number or 0 for MENU> " TO PROMPT. 43 |
| WRITE PROMPT. |
| ACCEPT RESPONSE1 FREE. |
| IF RESPONSE1 = ZERO |
| MOVE "X" TO FUNC-DONE-FLAG |
| GO TO C400-EXIT |
| ELSE |
| MOVE RESPONSE1 TO VENDORNUMBER. |
| |
| MOVE "Enter Vendor Name> " TO PROMPT. |
| MOVE SPACES TO VENDORNAME. |
| DISPLAY " ". |
| WRITE PROMPT. |
| ACCEPT VENDORNAME FREE. |
| |
| MOVE "Enter Contact Name (0 for null)> " TO PROMPT. 44 |
| MOVE SPACES TO CONTACTNAME. |
| DISPLAY " ". |
| WRITE PROMPT. |
| ACCEPT CONTACTNAME FREE. |
| IF CONTACTNAME = 0 45 |
| MOVE -1 TO CONTACTNAMEIND |
| ELSE |
| MOVE 0 TO CONTACTNAMEIND. |
| |
| |
| |
| |
______________________________________________________________________________
Figure 7-3. Using INSERT, UPDATE, SELECT and DELETE (page 10 of 14)
______________________________________________________________________
| |
| |
| |
| |
| |
| MOVE "Enter Phone Number (0 for null)> " TO PROMPT. |
| MOVE SPACES TO PHONENUMBER. |
| WRITE PROMPT. |
| ACCEPT PHONENUMBER FREE. |
| IF PHONENUMBER = 0 |
| MOVE -1 TO PHONENUMBERIND |
| ELSE |
| MOVE 0 TO PHONENUMBERIND. |
| |
| MOVE "Enter Vendor Street> " TO PROMPT. |
| MOVE SPACES TO VENDORSTREET. |
| WRITE PROMPT. |
| ACCEPT VENDORSTREET FREE. |
| |
| MOVE "Enter Vendor City> " TO PROMPT. |
| MOVE SPACES TO VENDORCITY. |
| WRITE PROMPT. |
| ACCEPT VENDORCITY FREE. |
| |
| MOVE "Enter Vendor State> " TO PROMPT. |
| MOVE SPACES TO VENDORSTATE. |
| WRITE PROMPT. |
| ACCEPT VENDORSTATE FREE. |
| |
| MOVE "Enter Vendor Zip Code> " TO PROMPT. |
| MOVE SPACES TO VENDORZIPCODE. |
| WRITE PROMPT. |
| ACCEPT VENDORZIPCODE FREE. |
| |
| MOVE "Enter Vendor Remarks (0 for null)> " TO PROMPT.|
| MOVE SPACES TO REMARKS. |
| WRITE PROMPT. |
| ACCEPT REMARKS FREE. |
| IF VENDORREMARKS = 0 |
| MOVE -1 TO VENDORREMARKSIND |
| ELSE |
| MOVE 0 TO VENDORREMARKSIND. |
| |
| |
| |
| |
| |
______________________________________________________________________
Figure 7-3. Using INSERT, UPDATE, SELECT and DELETE (page 11 of 14)
______________________________________________________________________________
| |
| IF VENDORREMARKSIND = 0 |
| MOVE 0 TO COUNTER |
| INSPECT VENDORREMARKS TALLYING COUNTER |
| FOR CHARACTERS BEFORE INITIAL " " |
| MOVE COUNTER TO REMARKSLENGTH. |
| |
| PERFORM A300-BEGIN-TRANSACTION THRU A300-EXIT. 46 |
| |
| DISPLAY "INSERT row into PurchDB.Vendors". |
| |
| EXEC SQL INSERT 47 |
| INTO PURCHDB.VENDORS |
| (VENDORNUMBER, |
| VENDORNAME, |
| CONTACTNAME, |
| PHONENUMBER, |
| VENDORSTREET, |
| VENDORCITY, |
| VENDORSTATE, |
| VENDORZIPCODE, |
| VENDORREMARKS) |
| VALUES(:VENDORNUMBER, |
| :VENDORNAME, |
| :CONTACTNAME :CONTACTNAMEIND, |
| :PHONENUMBER :PHONENUMBERIND, |
| :VENDORSTREET, |
| :VENDORCITY, |
| :VENDORSTATE, |
| :VENDORZIPCODE, |
| :VENDORREMARKS :VENDORREMARKSIND) |
| END-EXEC. |
| |
| IF SQLCODE NOT = OK |
| PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT. |
| |
| PERFORM A400-COMMIT-WORK THRU A400-EXIT. 48 |
| |
| C400-EXIT. |
| EXIT. |
| |
| D100-DISPLAY-ROW. |
| |
| MOVE VENDORNUMBER TO NUMFORMAT. |
| DISPLAY " VendorNumber: " NUMFORMAT. |
| DISPLAY " VendorName: " VENDORNAME. |
| |
______________________________________________________________________________
Figure 7-3. Using INSERT, UPDATE, SELECT and DELETE (page 12 of 14)
______________________________________________________________________________
| |
| IF CONTACTNAMEIND < 0 49 |
| DISPLAY " ContactName is NULL" |
| ELSE |
| DISPLAY " ContactName: " CONTACTNAME. |
| IF PHONENUMBERIND < 0 |
| DISPLAY " PhoneNumber is NULL" |
| ELSE |
| DISPLAY " PhoneNumber: " PHONENUMBER. |
| DISPLAY " VendorStreet: " VENDORSTREET. |
| DISPLAY " VendorCity: " VENDORCITY. |
| DISPLAY " VendorState: " VENDORSTATE. |
| DISPLAY " VendorZipCode: " VENDORZIPCODE. |
| IF VENDORREMARKSIND < 0 |
| DISPLAY " VendorRemarks is NULL" |
| ELSE |
| DISPLAY " VendorRemarks: " REMARKS. |
| |
| D100-EXIT. |
| EXIT. |
| |
| D200-SQL-SELECT. 50 |
| |
| EXEC SQL SELECT VENDORNUMBER, |
| VENDORNAME, |
| CONTACTNAME, |
| PHONENUMBER, |
| VENDORSTREET, |
| VENDORCITY, |
| VENDORSTATE, |
| VENDORZIPCODE, |
| VENDORREMARKS |
| INTO :VENDORNUMBER, |
| :VENDORNAME, |
| :CONTACTNAME :CONTACTNAMEIND, |
| :PHONENUMBER :PHONENUMBERIND, |
| :VENDORSTREET, |
| :VENDORCITY, |
| :VENDORSTATE, |
| :VENDORZIPCODE, |
| :VENDORREMARKS :VENDORREMARKSIND |
| FROM PURCHDB.VENDORS |
| WHERE VENDORNUMBER = :VENDORNUMBER |
| END-EXEC. |
| |
| D200-EXIT. |
| EXIT. |
______________________________________________________________________________
Figure 7-3. Using INSERT, UPDATE, SELECT and DELETE (page 13 of 14)
______________________________________________________________________________
| |
| S100-SQL-STATUS-CHECK. |
| |
| IF SQLCODE < DEADLOCK |
| MOVE 'X' TO ABORT-FLAG. |
| |
| PERFORM S200-SQLEXPLAIN UNTIL SQLCODE = 0. |
| |
| IF ABORT |
| PERFORM A500-TERMINATE-PROGRAM THRU A500-EXIT. |
| |
| S100-EXIT. |
| EXIT. |
| |
| S200-SQLEXPLAIN. |
| |
| EXEC SQL |
| SQLEXPLAIN :SQLMESSAGE 51 |
| END-EXEC. |
| |
| DISPLAY SQLMESSAGE. |
| |
| S200-EXIT. |
| EXIT. |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
______________________________________________________________________________
Figure 7-3. Using INSERT, UPDATE, SELECT and DELETE (page 14 of 14)