 |
» |
|
|
|
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 .
Figure 7-1 Flow Chart of Program COBEX7
![[Flow Chart of Program COBEX7]](img/x0501a.gif)
![[Flow Chart of Program COBEX7]](img/x0501b.gif)
Figure 7-2 Runtime Dialog of Program COBEX7
: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
*** 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
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-3 Using INSERT, UPDATE, SELECT and DELETE
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 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.
* * * * * * 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
|
|
