HP 3000 Manuals HP 3000 Manuals
Program Using Simple DML Operations
The flow chart shown in Figure 7-1 summarizes the functionality of
program pasex7. This program uses the four simple data manipulation
commands to operate on the PurchDB.Vendors table. Program pasex7 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 pasex7 appears in Figure 7-2, and the
source code in Figure 7-3.
Function ConnectDBE starts a DBE session 48. This function executes the
CONNECT command 2 for the sample DBEnvironment, PartsDBE.
The next operation performed depends on the number entered in response to
this menu 49:
* The program terminates if 0 is entered.
* Procedure Select is executed if 1 is entered.
* Procedure Update is executed if 2 is entered.
* Procedure Delete is executed if 3 is entered.
* Procedure Insert is executed if 4 is entered.
Procedure Select
Procedure Select 9 prompts for a vendor number or a 0 10. If a 0 is
entered, the function menu is re-displayed. If a vendor number is
entered, procedure BeginTransaction is executed 11 to issue the BEGIN
WORK command 4. Then a SELECT command is executed to retrieve all data
for the vendor specified from PurchDB.Vendors 12. The SQLCA.SQLCODE
returned is examined to determine the next action:
* If no rows qualify for the SELECT operation, a message 14 is
displayed and the transaction terminated 16. Procedure CommitWork
terminates the transaction by executing the COMMIT WORK command 5.
The user is then re-prompted for a vendor number or a 0.
* If more than one row qualifies for the SELECT operation, a
different message is displayed and procedure CommitWork 5
terminates the transaction by executing the COMMIT WORK command.
The user is then re-prompted for a vendor number or a zero.
* If the SELECT command execution results in an error condition,
procedure SqlStatusCheck is executed 15. This procedure executes
SQLEXPLAIN 1 to display all error messages. Then the transaction
is terminated 16 and the user re-prompted for a vendor number or a
0.
* If the SELECT command can be successfully executed, procedure
DisplayRow 13 is executed to display the row. This procedure
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 8, a message
indicating that the value is null is displayed. After the row is
completely displayed, the transaction is terminated 16 and the
user re-prompted for a vendor number or a 0.
Procedure Update
Procedure Update 22 lets the user UPDATE the value of a column only if it
contains a null value. The procedure prompts for a vendor number or a 0
23. If a 0 is entered, the function menu is re-displayed. If a vendor
number is entered, procedure BeginTransaction is executed 24. Then a
SELECT command is executed to retrieve data from PurchDB.Vendors for the
vendor specified 25. The SQLCA.SQLCODE returned is examined to determine
the next action:
* If no rows qualify for the SELECT operation, a message 27 is
displayed and the transaction is terminated 29. The user is then
re-prompted for a vendor number or a 0.
* If more than one row qualifies for the SELECT operation, a
different message is displayed and procedure CommitWork 5
terminates the transaction by executing the COMMIT WORK command.
The user is then re-prompted for a vendor number or a zero.
* If the SELECT command execution results in an error condition,
procedure SqlStatusCheck is executed 28. Then the transaction is
terminated 29 and the user re-prompted for a vendor number or a 0.
* If the SELECT command can be successfully executed, procedure
DisplayUpdate 26 is executed. This procedure executes procedure
DisplayRow to display the row retrieved 17. Function AnyNulls is
then executed to determine whether the row contains any null
values. This boolean function evaluates to TRUE if the indicator
variable for any of the three potentially null columns contains a
non-zero value 6.
If function AnyNulls evaluates to FALSE, a message is displayed 7
and the transaction is terminated 29; the user is then re-prompted
for a vendor number or a 0.
If function AnyNulls evaluates to TRUE, the null indicators are
examined to determine which of them contain a negative value 18.
A negative null indicator means the column contains a null value,
and the user is prompted for a new value 19. If the user enters a
0, the program assigns a -1 to the null indicator 20 so that when
the UPDATE command 21 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 21 command is executed, the
transaction is terminated 29 and the user re-prompted for a vendor
number or a 0.
Procedure Delete
Procedure Delete 33 lets the user DELETE one row. The procedure prompts
for a vendor number or a 0 34. If a 0 is entered, the function menu is
re-displayed. If a vendor number is entered, procedure BeginTransaction
is executed 35. Then a SELECT command is executed to retrieve all data
for the vendor specified from PurchDB.Vendors 36. The SQLCA.SQLCODE
returned is examined to determine the next action:
* If no rows qualify for the SELECT operation, a message 38 is
displayed and the transaction is terminated 40. The user is then
re-prompted for a vendor number or a 0.
* If more than one row qualifies for the SELECT operation, a
different message is displayed and procedure CommitWork 5
terminates the transaction by executing the COMMIT WORK command.
The user is then re-prompted for a vendor number or a zero.
* If the SELECT command execution results in an error condition,
procedure SqlStatusCheck is executed 39. Then the transaction is
terminated 40 and the user re-prompted for a vendor number or a 0.
* If the SELECT command can be successfully executed, procedure
DisplayDelete 37 is executed. This procedure executes procedure
DisplayRow to display the row retrieved 30. Then the user is
asked whether she wants to actually delete the row 31. If not,
the transaction is terminated 40 and the user re-prompted for a
vendor number or a 0. If so, the DELETE command 32 is executed
before the transaction is terminated 40 and the user re-prompted.
Procedure Insert
Procedure Insert 41 lets the user INSERT one row. The procedure prompts
for a vendor number or a 0 42. 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 43; to assign a null value, the program
assigns a -1 to the appropriate null indicator 44. After a transaction
is started 45, an INSERT command 46 is used to insert a row containing
the specified values. After the INSERT operation, the transaction is
terminated 47, 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 procedure TerminateProgram 50. This
procedure executes the RELEASE command 3.
![[]](../../pic3k/M012400/FFN663c50.gif)
Figure 7-1. Flow Chart of Program pasex7
![[]](../../pic3k/M012400/FFN673c50.gif)
Flow Chart of Program pasex7 (page 2 of 2)
_________________________________________________________________________
| |
| Program for Simple Data Manipulation of Vendors Table - pasex7 |
| |
| 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 pasex7
_______________________________________________________________________
| |
| *** 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 pasex7 (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 |
| |
| |
| |
| |
| |
| |
_______________________________________________________________________
Figure 7-2. Runtime Dialog of Program pasex7 (page 3 of 3)
____________________________________________________________________________
| |
| $Heap_dispose ON$ |
| $Heap_Compact ON$ |
| Standard_level 'HP_Pascal$ |
| (* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *)|
| (* This program illustrates simple data manipulation. It uses the *)|
| (* UPDATE command with indicator variables to update any row in *)|
| (* Vendors Table that contains null values. It also uses *)|
| (* indicator variables in conjunction with SELECT and INSERT. The *)|
| (* DELETE command is also illustrated. *)|
| (* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *)|
| |
| Program pasex7(input,output); |
| |
| const |
| OK = 0; |
| NotFound = 100; |
| DeadLock = -14024; |
| |
| var |
| (* Begin Host Variable Declarations *) |
| EXEC SQL BEGIN DECLARE SECTION; |
| VendorNumber : integer; |
| VendorName : packed array[1..30] of char; |
| ContactName : packed array[1..30] of char; |
| ContactNameInd : SqlInd; |
| PhoneNumber : packed array[1..15] of char; |
| PhoneNumberInd : SqlInd; |
| VendorStreet : packed array[1..30] of char; |
| VendorCity : packed array[1..20] of char; |
| VendorState : packed array[1..2] of char; |
| VendorZipCode : packed array[1..10] of char; |
| VendorRemarks : string[60]; |
| VendorRemarksInd : SqlInd; |
| SQLMessage : packed array[1..132] of char; |
| EXEC SQL END DECLARE SECTION; |
| (* End Host Variable Declarations *) |
| |
| SQLCA : SQLCA_type; (* SQL Communication Area *) |
| |
| Abort : boolean; |
| Response : integer; |
| Response1 : packed array[1..3] of char; |
| |
| procedure TerminateProgram; forward; |
| procedure SQLStatusCheck; (* Procedure to Display Error Messages *) |
| begin |
| |
| |
| |
____________________________________________________________________________
Figure 7-3. Program pasex7: Using SELECT, UPDATE, DELETE and INSERT
_______________________________________________________________________________
| |
| Abort := FALSE; |
| if SQLCA.SQLCODE < DeadLock then Abort := TRUE; |
| |
| repeat |
| EXEC SQL SQLEXPLAIN :SQLMessage; 1 |
| writeln(SQLMessage); |
| until SQLCA.SQLCODE = 0; |
| |
| if Abort then TerminateProgram; |
| |
| end; (* End SQLStatusCheck Procedure *) |
| $PAGE $ |
| function ConnectDBE: boolean; (* Function to Connect to PartsDBE *) |
| begin |
| |
| writeln('Connect to PartsDBE'); |
| EXEC SQL CONNECT TO 'PartsDBE'; 2 |
| |
| ConnectDBE := TRUE; |
| if SQLCA.SQLCODE <> OK then |
| begin |
| |
| ConnectDBE := FALSE; |
| SQLStatusCheck; |
| |
| end; (* End if *) |
| end; (* End of ConnectDBE Function *) |
| |
| |
| procedure TerminateProgram; (* Procedure to Release from PartsDBE *)|
| begin |
| |
| EXEC SQL RELEASE; 3 |
| |
| end; (* End of TerminateProgramProcedure *) |
| $PAGE $ |
| |
| procedure BeginTransaction; (* procedure to BEGIN WORK *) |
| |
| begin |
| |
| writeln('Begin Work'); |
| EXEC SQL BEGIN WORK; 4 |
| if SQLCA.SQLCODE <> OK then |
| begin |
| |
| |
| |
| |
_______________________________________________________________________________
Figure 7-3. Program pasex7: Using SELECT, UPDATE, DELETE and INSERT (page 2 of 12)
________________________________________________________________________________
| |
| SQLStatusCheck; |
| TerminateProgram; |
| end; |
| |
| end; (* End BeginTransaction procedure *) |
| |
| |
| procedure CommitWork; (* Procedure to Commit Work *) |
| begin |
| |
| writeln('Commit Work'); |
| EXEC SQL COMMIT WORK; 5 |
| if SQLCA.SQLCODE <> OK then |
| begin |
| |
| SqlStatusCheck; |
| TerminateProgram; |
| |
| end; |
| |
| end; (* End CommitWork Procedure *) |
| |
| function AnyNulls: boolean; (* Function to test row for null value(s) *)|
| begin |
| |
| AnyNulls := TRUE; |
| |
| if (ContactNameInd = 0) and 6 |
| (PhoneNumberInd = 0) and |
| (VendorRemarksInd = 0) |
| then (* all columns that might be null contain non-null values *) |
| begin |
| writeln(' No null values exist for this vendor'); 7 |
| AnyNulls := FALSE; |
| end; |
| end; (* End of Null Function *) |
| |
| procedure DisplayRow; (* Procedure to Display Vendors Table Rows *) |
| begin |
| |
| writeln; |
| writeln(' VendorNumber: ', VendorNumber); |
| writeln(' VendorName: ', VendorName); |
| if ContactNameInd <0 then 8 |
| writeln(' ContactName is NULL') |
| else |
| writeln(' ContactName: ', ContactName); |
| |
| |
________________________________________________________________________________
Figure 7-3. Program pasex7: Using SELECT, UPDATE, DELETE and INSERT (page 3 of 12)
____________________________________________________________________________
| |
| if PhoneNumberInd <0 then |
| writeln(' PhoneNumber is NULL') |
| else |
| writeln(' PhoneNumber: ', PhoneNumber); |
| writeln(' VendorStreet: ', VendorStreet); |
| writeln(' VendorCity: ', VendorCity); |
| writeln(' VendorState: ', VendorState); |
| writeln(' VendorZipCode: ', VendorZipCode); |
| if VendorRemarksInd <0 then |
| writeln(' VendorRemarks is NULL') |
| else |
| writeln(' VendorRemarks: ', VendorRemarks); |
| writeln; |
| |
| |
| end; (* End of DisplayRow *) |
| $PAGE $ |
| |
| procedure Select; (* procedure to select row from Vendors Table *)9 |
| |
| begin |
| |
| writeln; |
| writeln(' *** Procedure to SELECT rows from PurchDB.Vendors *** ');|
| writeln; |
| |
| repeat |
| |
| writeln; |
| prompt('Enter Vendor Number <> or 0 for MENU> '); 10 |
| readln(VendorNumber); |
| writeln; |
| |
| if VendorNumber |
| 0 then |
| begin |
| BeginTransaction; 11 |
| writeln('SELECT * from PurchDB.Vendors'); |
| EXEC SQL SELECT VendorNumber, 12 |
| VendorName, |
| ContactName, |
| PhoneNumber, |
| VendorStreet, |
| VendorCity, |
| VendorState, |
| VendorZipCode, |
| VendorRemarks |
| |
| |
| |
____________________________________________________________________________
Figure 7-3. Program pasex7: Using SELECT, UPDATE, DELETE and INSERT (page 4 of 12)
__________________________________________________________________________
| |
| INTO :VendorNumber, |
| :VendorName, |
| :ContactName :ContactNameInd, |
| :PhoneNumber :PhoneNumberInd, |
| :VendorStreet, |
| :VendorCity, |
| :VendorState, |
| :VendorZipCode, |
| :VendorRemarks :VendorRemarksInd |
| FROM PurchDB.Vendors |
| WHERE VendorNumber = :VendorNumber; |
| |
| case SQLCA.SQLCODE of |
| OK : DisplayRow; 13|
| NotFound : begin |
| writeln; |
| writeln('Row not found!'); 14|
| end; |
| otherwise begin |
| SqlStatusCheck; 15|
| end; |
| end; (* end case *) |
| CommitWork; 16|
| end; (* end if response *) |
| until VendorNumber = 0; |
| |
| end; (* end Select Procedure *) |
| |
| procedure DisplayUpdate; (* procedure to display and update row *) |
| begin |
| |
| DisplayRow; 17|
| if AnyNulls then |
| begin |
| |
| if ContactNameInd < 0 then 18|
| begin |
| writeln; |
| prompt('Enter new ContactName (0 for NULL)> '); 19|
| readln(ContactName); |
| end; |
| |
| |
| |
| |
| |
| |
| |
| |
__________________________________________________________________________
Figure 7-3. Program pasex7: Using SELECT, UPDATE, DELETE and INSERT (page 5 of 12)
__________________________________________________________________________
| |
| if PhoneNumberInd < 0 then |
| begin |
| writeln; |
| prompt('Enter new PhoneNumber (0 for NULL)> '); |
| readln(PhoneNumber); |
| end; |
| |
| if VendorRemarksInd < 0 then |
| begin |
| writeln; |
| prompt('Enter new VendorRemarks (0 for NULL)> '); |
| readln(VendorRemarks); |
| end; |
| |
| if ContactName = '0' then 20|
| ContactNameInd := -1 |
| else |
| ContactNameInd := 0; |
| |
| |
| if PhoneNumber = '0' then |
| PhoneNumberInd := -1 |
| else |
| PhoneNumberInd := 0; |
| |
| if VendorRemarks = '0' then |
| VendorRemarksInd := -1 |
| else |
| VendorRemarksInd := 0; |
| |
| EXEC SQL UPDATE PurchDB.Vendors 21|
| SET ContactName = :ContactName :ContactNameId, |
| PhoneNumber = :PhoneNumber :PhoneNumberInd, |
| VendorRemarks = :VendorRemarks :VendorRemarksInd |
| WHERE VendorNumber = :VendorNumber; |
| |
| if SQLCA.SQLCODE <> OK then SqlStatusCheck; |
| |
| end; (* end if AnyNulls *) |
| |
| end; (* end of DisplayUpdate procedure *) |
| |
| |
| |
| $PAGE $ |
| |
| procedure Update; (* Update a row within the Vendors Table *) 22|
| |
| |
__________________________________________________________________________
Figure 7-3. Program pasex7: Using SELECT, UPDATE, DELETE and INSERT (page 6 of 12)
__________________________________________________________________________
| |
| begin |
| |
| writeln; |
| writeln(' *** Procedure to UPDATE rows in PurchDB.Vendors *** ');|
| writeln; |
| |
| repeat |
| writeln; |
| prompt('Enter Vendor Number or 0 for MENU> '); 23|
| readln(VendorNumber); |
| writeln; |
| |
| if VendorNumber <> 0 then |
| begin |
| BeginTransaction; 24|
| writeln ('SELECT * from PurchDB.Vendors'); |
| EXEC SQL SELECT VendorNumber, 25|
| 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; |
| |
| case SQLCA.SQLCODE of |
| OK : begin |
| DisplayUpdate; 26|
| end; |
| NotFound : begin |
| writeln; |
| writeln('Row not found!'); 27|
| end; |
| |
| |
| |
| |
__________________________________________________________________________
Figure 7-3. Program pasex7: Using SELECT, UPDATE, DELETE and INSERT (page 7 of 12)
____________________________________________________________________________
| |
| otherwise begin |
| SQLStatusCheck; 28 |
| end; |
| end; (* case *) |
| CommitWork; 29 |
| |
| end; (* end if response *) |
| until VendorNumber = 0; |
| |
| end; (* End of Update Procedure *) |
| |
| $PAGE $ |
| procedure DisplayDelete; (* procedure to display and delete a row *) |
| |
| begin |
| |
| DisplayRow; 30 |
| |
| prompt('Is it OK to DELETE this row (N/Y)? > '); 31 |
| readln(Response1); |
| writeln; |
| if response1[1] in ['Y','y'] then |
| begin |
| writeln; |
| writeln('DELETE row from PurchDB.Vendors'); |
| EXEC SQL DELETE FROM PurchDB.Vendors 32 |
| WHERE VendorNumber = :VendorNumber; |
| if SQLCA.SQLCODE <> OK then SqlStatusCheck; |
| end; |
| end; (* end procedure DisplayDelete *) |
| |
| procedure Delete; 33 |
| (* procedure to delete a row from PurchDB.Vendors *) |
| |
| begin |
| |
| writeln; |
| writeln(' *** Procedure to DELETE rows from PurchDB.Vendors *** ');|
| writeln; |
| |
| repeat |
| writeln; |
| prompt('Enter Vendor Number or 0 for MENU> '); 34 |
| readln(VendorNumber); |
| writeln; |
| |
| |
| |
| |
____________________________________________________________________________
Figure 7-3. Program pasex7: Using SELECT, UPDATE, DELETE and INSERT (page 8 of 12)
____________________________________________________________________________
| |
| if VendorNumber <> 0 then |
| begin |
| BeginTransaction; 35 |
| writeln('SELECT * from PurchDB.Vendors'); |
| EXEC SQL SELECT VendorNumber, 36 |
| 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; |
| |
| case SQLCA.SQLCODE of |
| OK : DisplayDelete; 37 |
| NotFound : begin |
| writeln; |
| writeln('Row not found!'); 38 |
| end; |
| otherwise begin |
| SqlStatusCheck; 39 |
| end; |
| end; (* end case *) |
| CommitWork; 40 |
| end; (* end if response *) |
| until VendorNumber = 0; |
| end; (* end Delete procedure *) |
| |
| procedure Insert; 41 |
| (* procedure to insert a row into PurchDB.Vendors *) |
| |
| begin |
| |
| writeln; |
| writeln(' *** Procedure to INSERT rows into PurchDB.Vendors *** ');|
| writeln; |
| |
____________________________________________________________________________
Figure 7-3. Program pasex7: Using SELECT, UPDATE, DELETE and INSERT (page 9 of 12)
__________________________________________________________________________
| |
| repeat |
| writeln; |
| prompt('Enter Vendor Number or 0 for MENU> '); 42|
| readln(VendorNumber); |
| writeln; |
| |
| if VendorNumber <> 0 then |
| begin |
| |
| writeln; |
| prompt('Enter Vendor Name> '); |
| readln(VendorName); |
| writeln; |
| |
| writeln; |
| prompt('Enter Contact Name (0 for null)> '); 43|
| readln(ContactName); |
| if ContactName = '0' then |
| ContactNameInd := -1 44|
| else |
| ContactNameInd := 0; |
| writeln; |
| |
| prompt('Enter Phone Number (0 for null)> '); |
| readln(PhoneNumber); |
| if PhoneNumber = '0' then |
| PhoneNumberInd := -1 |
| else |
| PhoneNumberInd := 0; |
| writeln; |
| |
| prompt('Enter Vendor Street> '); |
| readln(VendorStreet); |
| writeln; |
| |
| prompt('Enter Vendor City> '); |
| readln(VendorCity); |
| writeln; |
| |
| prompt('Enter Vendor State> '); |
| readln(VendorState); |
| writeln; |
| |
| |
| |
| |
| |
| |
| |
__________________________________________________________________________
Figure 7-3. Program pasex7: Using SELECT, UPDATE, DELETE and INSERT (page 10 of 12)
_________________________________________________________________________________
| |
| prompt('Enter Vendor Zip Code> '); |
| readln(VendorZipCode); |
| writeln; |
| |
| prompt('Enter Vendor Remarks (0 for null)> '); |
| readln(VendorRemarks); |
| if VendorRemarks = '0' then |
| VendorRemarksInd := -1 |
| else |
| VendorRemarksInd := 0; |
| BeginTransaction; 45 |
| writeln('INSERT row into PurchDB.Vendors'); |
| EXEC SQL INSERT 46 |
| 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); |
| if SQLCA.SQLCODE <> OK then SqlStatusCheck; |
| CommitWork; 47 |
| end; (* end if response *) |
| until VendorNumber = 0; |
| end; (* end of insert procedure *) |
| |
| begin (* Beginning of Program *) |
| |
| writeln('Program for Simple Data Manipulation of Vendors Table - pasex7');|
| writeln; |
| |
| if ConnectDBE then 48 |
| begin |
| |
| repeat |
| |
| |
_________________________________________________________________________________
Figure 7-3. Program pasex7: Using SELECT, UPDATE, DELETE and INSERT (page 11 of 12)
_________________________________________________________________________________
| |
| writeln; |
| writeln(' 1 . . . SELECT rows from PurchDB.Vendors table'); |
| writeln(' 2 . . . UPDATE rows with null values in PurchDB.Vendors table|
| writeln(' 3 . . . DELETE rows from PurchDB.Vendors table'); |
| writeln(' 4 . . . INSERT rows into PurchDB.Vendors table'); |
| writeln; |
| prompt('Enter choice or 0 to STOP> '); |
| readln(Response); |
| writeln; |
| |
| if Response <> 0 then |
| begin |
| case Response of 49 |
| 1 : Select; |
| 2 : Update; |
| 3 : Delete; |
| 4 : Insert; |
| otherwise writeln('Enter 0-4 only, please'); |
| |
| end; (* end case *) |
| end; (* end if Response *) |
| until Response = 0; |
| TerminateProgram; 50 |
| end (* end if connect *) |
| else |
| writeln('Cannot connect to PartsDBE'); |
| end. (* end of program *) |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
_________________________________________________________________________________
Figure 7-3. Program pasex7: Using SELECT, UPDATE, DELETE and INSERT (page 12 of 12)