HP 3000 Manuals HP 3000 Manuals
Program Using SELECT, UPDATE, DELETE, and INSERT
The flow chart shown in Figure 7-1 summarizes the functionality of
program forex7, which uses the four simple data manipulation commands to
operate on the PurchDB.Vendors table. Forex7 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 forex7 appears in Figure 7-2, and the
source code in Figure 7-3.
The main program 1 first calls function ConnectDBE 3 to start a DBE
session. This function executes the CONNECT command for the sample
DBEnvironment, PartsDBE. The main program then displays a menu of
selections. The next operation performed depends on the number entered
in response to this menu:
* The program terminates if 0 is entered.
* Function Select is executed if 1 is entered.
* Function Update is executed if 2 is entered.
* Function Delete is executed if 3 is entered.
* Function Insert is executed if 4 is entered.
Select Function
Function Select 10 prompts for a vendor number or a zero. If a zero is
entered, the function menu is re-displayed. If a vendor number is
entered, subroutine BeginTransaction 5 is executed to issue the BEGIN
WORK command. Then a SELECT command is executed to retrieve all data for
the vendor specified from PurchDB.Vendors. The SQLCode returned is
examined to determine the next action:
* If no rows qualify for the SELECT operation, a message is
displayed and subroutine CommitWork 6 terminates the transaction
by executing the COMMIT WORK command. The user is then
re-prompted for a vendor number or a zero.
* If more than one row qualifies for the SELECT operation, a
different message is displayed and subroutine CommitWork 6
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,
subroutine SQLStatusCheck 2 is executed. This subroutine executes
SQLEXPLAIN to display all error messages. If the error is
serious, (less than -14024) a message is displayed and subroutine
TerminateProgram (4) is called to release the DBEnvironment and
terminate the entire program. If the error is not serious,
subroutine CommitWork 6 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 can be successfully executed, subroutine
DisplayRow 9 is executed to display the row. This subroutine
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 zero, a message
indicating that the value is null is displayed. After the row is
completely displayed, subroutine CommitWork 6 terminates the
transaction by executing the COMMIT WORK command. The user is
then re-prompted for a vendor number or a zero.
Update Function
Function Update 12 lets the user UPDATE the value of a column only if it
contains a null value. The function prompts for a vendor number or a
zero. If a zero is entered, the function menu is re-displayed. If a
vendor number is entered, subroutine BeginTransaction 5 is executed.
Then a SELECT command is executed to retrieve data from PurchDB.Vendors
for the vendor specified. The SQLCode returned is examined to determine
the next action:
* If no rows qualify for the SELECT operation, a message is
displayed and subroutine CommitWork 6 terminates the transaction
by executing the COMMIT WORK command. The user is then
re-prompted for a vendor number or a zero.
* If more than one row qualifies for the SELECT operation, a
different message is displayed and subroutine CommitWork 6
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,
subroutine SQLStatusCheck 2 is executed. Then subroutine
CommitWork 6 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 can be successfully executed, subroutine
DisplayUpdate 11 is executed. This subroutine executes subroutine
DisplayRow 9 to display the row retrieved. Function AnyNulls 8 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.
If function AnyNulls evaluates to FALSE, a message is displayed,
no UPDATE is performed, and subroutine CommitWork 6 terminates the
transaction by executing the COMMIT WORK command. The user is
then re-prompted for a vendor number or a zero.
If function AnyNulls evaluates to TRUE, the null indicators are
examined to determine which of them contain negative values. If
the null indicator is less than zero, the column contains a null
value, and the user is prompted for a new value. If the user
enters a zero, the program assigns a -1 to the null indicator so
that when the UPDATE command 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 command is executed,
subroutine CommitWork 6 terminates the transaction by executing
the COMMIT WORK command. The user is then re-prompted for a
vendor number or a zero.
Delete Function
Function Delete 14 lets the user DELETE one row. The function prompts
for a vendor number or a zero. If a zero is entered, the function
menu is re-displayed. If a vendor number is entered, subroutine
BeginTransaction 5 is executed. Then a SELECT command is executed to
retrieve all data for the vendor specified from PurchDB.Vendors. The
SQLCode returned is examined to determine the next action:
* If no rows qualify for the SELECT operation, a message is
displayed and subroutine CommitWork 6 terminates the transaction
by executing the COMMIT WORK command. The user is then
re-prompted for a vendor number or a zero.
* If more than one row qualifies for the SELECT operation, a
different message is displayed and subroutine CommitWork 6
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,
subroutine SQLStatusCheck 2 is executed. Then subroutine
CommitWork 6 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 can be successfully executed, subroutine
DisplayDelete 13 is executed. This subroutine executes subroutine
DisplayRow 9 to display the row retrieved. Then the user is asked
whether she wants to actually delete the row. If the user does
not wish to delete, subroutine CommitWork 6 terminates the
transaction by executing the COMMIT WORK command, and the user is
re-prompted for a vendor number or a zero. If the user does wish
to delete, the DELETE command is executed, then subroutine
CommitWork 6 terminates the transaction by executing the COMMIT
WORK command. The user is then re-prompted for a vendor number or
a zero.
Insert Function
Function Insert 15 lets the user INSERT one row. The subroutine prompts
for a vendor number or a zero. If a zero 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 zero to specify a null
value for potentially null columns; to assign a null value, the
program assigns a -1 to the appropriate null indicator. Subroutine
BeginTransaction is executed to start a transaction, then an INSERT
command is used to insert a row containing the specified values. If the
INSERT operation results in an error condition, subroutine SQLStatusCheck
2 is executed, and then subroutine RollBackWork 7 is executed to issue
the ROLLBACK WORK command. If the INSERT operation is successful,
subroutine CommitWork 6 terminates the transaction by executing the
COMMIT WORK command. The user is then re-prompted for a vendor number or
a zero.
When the user enters a zero in response to the function menu display, the
program terminates by executing subroutine TerminateProgram 4. This
subroutine executes the RELEASE command.
![[]](../../pic3k/M011857/FFN763c50.gif)
Figure 7-1. Flow Chart of Program forex7
![[]](../../pic3k/M011857/FFN773c50.gif)
Figure 7-1. Flow Chart of Program forex7 (page 2 of 2)
___________________________________________________________________
| |
| : run forex7 |
| Program for Simple Data Manipulation of |
| the Vendors Table -- forex7 |
| |
| Event List: |
| CONNECT TO PartsDBE |
| Prompt for type of transaction |
| Prompt for VendorNumber |
| BEGIN WORK |
| Display row |
| Perform specified function |
| COMMIT WORK or ROLLBACK WORK |
| Repeat the above five steps until user enters 0 |
| Repeat the above seven steps until user enters 0 |
| RELEASE PartsDBE |
| |
| CONNECT TO PartsDBE |
| |
| 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 your choice or a 0 to STOP > 4 |
| |
| Enter Vendor Number to INSERT or a 0 to STOP > 9016 |
| |
| Enter Vendor Name > Wolfe Works |
| |
| Enter new ContactName (0 for NULL) > Stanley Wolfe |
| |
| Enter new PhoneNumber (0 for NULL) > 408 975 6061 |
| |
| Enter new Vendor Street > 7614 Canine Way |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
___________________________________________________________________
Figure 7-2. Runtime Dialog of Program forex7
__________________________________________________________________
| |
| Enter new Vendor City > San Jose |
| |
| Enter new Vendor State > CA |
| |
| Enter new Vendor Zip Code > 90016 |
| |
| Enter new VendorRemarks (0 for NULL) > 0 |
| |
| BEGIN WORK |
| INSERT new row into PurchDB.Vendors |
| COMMIT WORK |
| |
| Enter Vendor Number to INSERT or a 0 to STOP > 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 your choice or a 0 to STOP > 1 |
| |
| Enter Vendor Number to SELECT or a 0 to STOP > 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 |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
__________________________________________________________________
Figure 7-2. Runtime Dialog of Program forex7 (page 2 of 4)
_____________________________________________________________________
| |
| Enter Vendor Number to SELECT or a 0 to STOP > 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 your choice or a 0 to STOP > 2 |
| |
| Enter Vendor Number to UPDATE or a 0 to STOP > 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|
| |
| UPDATE the PurchDB.Vendors table |
| COMMIT WORK |
| |
| Enter Vendor Number to UPDATE or a 0 to STOP > 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 your choice or a 0 to STOP > 3 |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
_____________________________________________________________________
Figure 7-2. Runtime Dialog of Program forex7 (page 3 of 4)
__________________________________________________________________
| |
| Enter Vendor Number to DELETE or a 0 to STOP > 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 to DELETE or a 0 to STOP > 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 your choice or a 0 to STOP > 0 |
| |
| RELEASE PartsDBE |
| : |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
__________________________________________________________________
Figure 7-2. Runtime Dialog of Program forex7 (page 4 of 4)
_____________________________________________________________________________
| |
| PROGRAM forex7 |
| C * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * |
| C * This program illustrates simple data manipulation. It * |
| C * uses the UPDATE command with indicator variables to * |
| C * update any row in the Vendors table that contains null * |
| C * values. It also uses indicator variables in * |
| C * conjunction with SELECT and INSERT. The DELETE * |
| C * command is also illustrated. * |
| C * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * |
| |
| IMPLICIT NONE |
| |
| LOGICAL*2 Done, ConnectDBE, Select, Update, Delete |
| LOGICAL*2 Insert |
| CHARACTER Response |
| |
| C (* Begin SQL Communication Area *) |
| |
| EXEC SQL INCLUDE SQLCA |
| |
| C (* Beginning of the Main Program *) 1 |
| |
| WRITE (*,*) CHAR(27),'U' |
| WRITE (*,*) 'Program for Simple Data Manipulation of Vendors |
| 1 - table forex7' |
| WRITE (*,*) ' ' |
| WRITE (*,*) 'Event List:' |
| WRITE (*,*) ' CONNECT TO PartsDBE' |
| WRITE (*,*) ' Prompt for type of transaction' |
| WRITE (*,*) ' Prompt for VendorNumber' |
| WRITE (*,*) ' BEGIN WORK' |
| WRITE (*,*) ' Display row' |
| WRITE (*,*) ' Perform specified function' |
| WRITE (*,*) ' COMMIT WORK or ROLLBACK WORK' |
| WRITE (*,*) ' Repeat the above five steps until user enters 0' |
| WRITE (*,*) ' Repeat the above seven steps until user enters 0'|
| WRITE (*,*) ' RELEASE PartsDBE' |
| WRITE (*,*) ' ' |
| |
| |
| |
| |
| |
| |
| |
| |
_____________________________________________________________________________
Figure 7-3. Program forex7: Using INSERT, UPDATE, SELECT, and DELETE
_______________________________________________________________________________
| |
| IF (ConnectDBE()) THEN |
| Done = .FALSE. |
| DO WHILE (.NOT.Done) |
| WRITE (*,*) ' ' |
| WRITE (*,*) '1....SELECT rows from PurchDB.Vendors table' |
| WRITE (*,*) '2....UPDATE rows with null values in PurchDB.Vend|
| 1ors table' |
| WRITE (*,*) '3....DELETE rows from PurchDB.Vendors table' |
| WRITE (*,*) '4....INSERT rows into PurchDB.Vendors table' |
| WRITE (*,*) ' ' |
| WRITE (*,100) |
| 100 FORMAT($, ' Enter your choice or a 0 to STOP > ') |
| READ (*,110) Response |
| 110 FORMAT(A1) |
| IF (Response .EQ. '0') THEN |
| Done = .TRUE. |
| ELSEIF (Response .EQ. '1') THEN |
| Done = Select() |
| ELSEIF (Response .EQ. '2') THEN |
| Done = Update() |
| ELSEIF (Response .EQ. '3') THEN |
| Done = Delete() |
| ELSEIF (Response .EQ. '4') THEN |
| Done = Insert() |
| ELSE |
| WRITE (*,*) ' Enter 0-4 only please!' |
| WRITE (*,*) ' ' |
| ENDIF |
| END DO |
| CALL TerminateProgram |
| ELSE |
| WRITE (*,*) 'Cannot Connect to your DBEnvironment!' |
| ENDIF |
| STOP |
| END |
| C (* End of Main Program *) |
| |
| C (* Beginning of the Sub-Routines *) |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
_______________________________________________________________________________
Figure 7-3. Program forex7: Using INSERT, UPDATE, SELECT, and DELETE (page 2 of 21)
_____________________________________________________________________________
| |
| SUBROUTINE SQLStatusCheck 2 |
| |
| C**** SUBROUTINE SQLStatusCheck checks status of SQL commands |
| C**** and print HPSQL error messages. |
| |
| C (* Begin SQL Communication Area *) |
| |
| EXEC SQL INCLUDE SQLCA |
| |
| LOGICAL*2 Abort, Check |
| INTEGER DeadLock |
| PARAMETER (DeadLock = -14024) |
| |
| |
| C (* Begin Host Variable Declarations *) |
| |
| EXEC SQL BEGIN DECLARE SECTION |
| CHARACTER*120 SQLMessage |
| EXEC SQL END DECLARE SECTION |
| |
| C (* End Host Variable Declarations *) |
| |
| Abort = .FALSE. |
| IF (SQLCode .LT. DeadLock) THEN |
| Abort = .TRUE. |
| WRITE (*,*) 'A serious error has occured!' |
| ENDIF |
| |
| Check = .TRUE. |
| DO WHILE (Check) |
| |
| EXEC SQL SQLEXPLAIN :SQLMessage |
| WRITE(*, 100) SQLMessage |
| 100 FORMAT(A120) |
| IF (SQLCode .EQ. 0) THEN |
| Check = .FALSE. |
| ENDIF |
| END DO |
| |
| IF (Abort) THEN |
| CALL TerminateProgram |
| STOP 'Program Aborted' |
| END IF |
| RETURN |
| END |
| C (* End of Subroutine SQLStatusCheck *) |
| |
| |
_____________________________________________________________________________
Figure 7-3. Program forex7: Using INSERT, UPDATE, SELECT, and DELETE (page 3 of 21)
______________________________________________________________________
| |
| LOGICAL*2 FUNCTION ConnectDBE() 3 |
| |
| C**** FUNCTION to connect to PartsDBE |
| |
| INTEGER*2 OK |
| PARAMETER (OK = 0) |
| |
| C (* Begin SQL Communication Area *) |
| |
| EXEC SQL INCLUDE SQLCA |
| |
| EXEC SQL BEGIN DECLARE SECTION |
| EXEC SQL END DECLARE SECTION |
| |
| WRITE (*,*) 'CONNECT TO PartsDBE' |
| EXEC SQL CONNECT TO 'PartsDBE' |
| ConnectDBE = .TRUE. |
| IF (SQLCode .NE. OK) THEN |
| ConnectDBE = .FALSE. |
| CALL SQLStatusCheck |
| ENDIF |
| RETURN |
| END |
| C (* End of Function ConnectDBE *) |
| |
| SUBROUTINE TerminateProgram 4 |
| |
| C**** SUBROUTINE to release from PartsDBE |
| |
| C (* Begin SQL Communication Area *) |
| |
| EXEC SQL INCLUDE SQLCA |
| |
| EXEC SQL BEGIN DECLARE SECTION |
| EXEC SQL END DECLARE SECTION |
| |
| WRITE(*,*) ' ' |
| WRITE(*,*) 'RELEASE PartsDBE' |
| EXEC SQL RELEASE |
| RETURN |
| END |
| C (* End of Subroutine TerminateProgram *) |
| |
| |
| |
| |
| |
| |
| |
| |
______________________________________________________________________
Figure 7-3. Program forex7: Using INSERT, UPDATE, SELECT, and DELETE (page 4 of 21)
______________________________________________________________________
| |
| SUBROUTINE BeginTransaction 5 |
| |
| C**** SUBROUTINE to begin work |
| INTEGER*2 OK |
| PARAMETER (OK = 0) |
| |
| C (* Begin SQL Communication Area *) |
| |
| EXEC SQL INCLUDE SQLCA |
| |
| EXEC SQL BEGIN DECLARE SECTION |
| EXEC SQL END DECLARE SECTION |
| |
| WRITE (*,*) ' ' |
| WRITE (*,*) 'BEGIN WORK' |
| EXEC SQL BEGIN WORK |
| IF (SQLCode .NE. OK) THEN |
| CALL SQLStatusCheck |
| CALL TerminateProgram |
| ENDIF |
| RETURN |
| END |
| C (* End of Subroutine BeginTransaction *) |
| |
| SUBROUTINE CommitWork 6 |
| |
| C**** SUBROUTINE to commit work |
| INTEGER*2 OK |
| PARAMETER (OK = 0) |
| |
| C (* Begin SQL Communication Area *) |
| |
| EXEC SQL INCLUDE SQLCA |
| |
| EXEC SQL BEGIN DECLARE SECTION |
| EXEC SQL END DECLARE SECTION |
| |
| WRITE(*,*) 'COMMIT WORK' |
| EXEC SQL COMMIT WORK |
| IF (SQLCode .NE. OK) THEN |
| CALL SQLStatusCheck |
| CALL TerminateProgram |
| ENDIF |
| RETURN |
| END |
| C (* End of Subroutine CommitWork *) |
| |
| |
| |
| |
______________________________________________________________________
Figure 7-3. Program forex7: Using INSERT, UPDATE, SELECT, and DELETE (page 5 of 21)
_________________________________________________________________________________
| |
| SUBROUTINE RollBackWork 7 |
| |
| C**** SUBROUTINE to RollBack Work |
| INTEGER*2 OK |
| PARAMETER (OK = 0) |
| |
| C (* Begin SQL Communication Area *) |
| |
| EXEC SQL INCLUDE SQLCA |
| |
| EXEC SQL BEGIN DECLARE SECTION |
| EXEC SQL END DECLARE SECTION |
| |
| WRITE(*,*) 'ROLLBACK WORK' |
| EXEC SQL ROLLBACK WORK |
| IF (SQLCode .NE. OK) THEN |
| CALL SQLStatusCheck |
| CALL TerminateProgram |
| ENDIF |
| RETURN |
| END |
| C (* End of Subroutine RollBackWork *) |
| |
| LOGICAL*2 FUNCTION AnyNulls(ContactNameInd, |
| 1 PhoneNumberInd, VendorRemarksInd) |
| C****FUNCTION to test rows for NULL values 8 |
| |
| |
| C (* Begin SQL Communication Area *) |
| |
| EXEC SQL INCLUDE SQLCA |
| |
| C (* Begin Host Variable Declarations *) |
| |
| EXEC SQL BEGIN DECLARE SECTION |
| SQLIND ContactNameInd, PhoneNumberInd, VendorRemarksInd |
| EXEC SQL END DECLARE SECTION |
| |
| C (* End Host Variable Declarations *) |
| |
| IF ((ContactNameInd .EQ. 0) .AND. |
| 1 (PhoneNumberInd .EQ. 0) .AND. |
| 2 (VendorRemarksInd .EQ. 0)) THEN |
| C (All columns that might be null contain non-null values) |
| WRITE (*,*) 'No null values exist for this vendor.' |
| WRITE (*,*) ' ' |
| AnyNulls = .FALSE. |
| |
| |
| |
_________________________________________________________________________________
Figure 7-3. Program forex7: Using INSERT, UPDATE, SELECT, and DELETE (page 6 of 21)
_____________________________________________________________________________
| |
| ELSE |
| AnyNulls = .TRUE. |
| ENDIF |
| RETURN |
| END |
| C (* End of Function AnyNulls *) |
| |
| SUBROUTINE DisplayRow (VendorNumber, VendorName, ContactName, |
| 1 PhoneNumber, VendorStreet, VendorCity, |
| 2 VendorState, VendorZipCode, VendorRemarks, |
| 3 ContactNameInd, PhoneNumberInd, VendorRemarksInd)|
| C**** SUBROUTINE to display Vendors table rows 9 |
| |
| C (* Begin SQL Communication Area *) |
| |
| EXEC SQL INCLUDE SQLCA |
| |
| C (* Begin Host Variable Declarations *) |
| |
| EXEC SQL BEGIN DECLARE SECTION |
| INTEGER*4 VendorNumber |
| CHARACTER*30 VendorName |
| CHARACTER*30 ContactName |
| SQLIND ContactNameInd |
| CHARACTER*16 PhoneNumber |
| SQLIND PhoneNumberInd |
| CHARACTER*30 VendorStreet |
| CHARACTER*20 VendorCity |
| CHARACTER*2 VendorState |
| CHARACTER*10 VendorZipCode |
| CHARACTER*50 VendorRemarks |
| SQLIND VendorRemarksInd |
| CHARACTER*120 SQLMessage |
| EXEC SQL END DECLARE SECTION |
| |
| C (* End Host Variable Declarations *) |
| |
| WRITE(*,*) ' ' |
| WRITE(*, '('' VendorNumber: '',I10)') VendorNumber |
| WRITE(*, '('' VendorName: '',A30)') VendorName |
| IF (ContactNameInd .LT. 0) THEN |
| WRITE(*,*) ' ContactName is NULL' |
| ELSE |
| WRITE(*, '('' ContactName: '',A30)') ContactName |
| ENDIF |
| |
| |
| |
_____________________________________________________________________________
Figure 7-3. Program forex7: Using INSERT, UPDATE, SELECT, and DELETE (page 7 of 21)
____________________________________________________________________________
| |
| IF (PhoneNumberInd .LT. 0) THEN |
| WRITE(*,*) ' PhoneNumber is NULL' |
| ELSE |
| WRITE(*, '('' PhoneNumber: '',A16)') PhoneNumber |
| ENDIF |
| WRITE(*, '('' VendorStreet: '',A30)') VendorStreet |
| WRITE(*, '('' VendorCity: '',A20)') VendorCity |
| WRITE(*, '('' VendorState: '',A2)') VendorState |
| WRITE(*, '('' VendorZipCode:'',A10)') VendorZipCode |
| IF (VendorRemarksInd .LT. 0) THEN |
| WRITE(*,*) ' VendorRemarks is NULL' |
| ELSE |
| WRITE(*, '('' VendorRemarks:'',A50)') VendorRemarks |
| ENDIF |
| WRITE(*,*) ' ' |
| RETURN |
| END |
| C (* End of Subroutine DisplayRow *) |
| |
| |
| LOGICAL*2 FUNCTION Select() 10 |
| |
| C**** FUNCTION to select rows from PurchDB.Vendors table. |
| |
| INTEGER NotFound,MultipleRows,OK |
| LOGICAL*2 AnyNulls |
| PARAMETER (NotFound = 100, |
| 1 MultipleRows = -10002, |
| 2 OK = 0) |
| |
| C (* Begin SQL Communication Area *) |
| |
| EXEC SQL INCLUDE SQLCA |
| |
| C (* Begin Host Variable Declarations *) |
| |
| EXEC SQL BEGIN DECLARE SECTION |
| INTEGER*4 VendorNumber |
| CHARACTER*30 VendorName |
| CHARACTER*30 ContactName |
| SQLIND ContactNameInd |
| CHARACTER*16 PhoneNumber |
| SQLIND PhoneNumberInd |
| CHARACTER*30 VendorStreet |
| CHARACTER*20 VendorCity |
| |
| |
| |
| |
____________________________________________________________________________
Figure 7-3. Program forex7: Using INSERT, UPDATE, SELECT, and DELETE (page 8 of 21)
_____________________________________________________________________________
| |
| CHARACTER*2 VendorState |
| CHARACTER*10 VendorZipCode |
| CHARACTER*50 VendorRemarks |
| SQLIND VendorRemarksInd |
| CHARACTER*120 SQLMessage |
| EXEC SQL END DECLARE SECTION |
| |
| C (* End Host Variable Declarations *) |
| |
| Select = .FALSE. |
| VendorNumber = 1 |
| DO WHILE (VendorNumber .NE. 0) |
| WRITE (*,*) ' ' |
| WRITE (*,100) |
| 100 FORMAT($, ' Enter Vendor Number to SELECT or a 0 to STOP > ')|
| READ (*,110) VendorNumber |
| 110 FORMAT(I4) |
| IF (VendorNumber .NE. 0) THEN |
| CALL BeginTransaction |
| WRITE (*,*) ' ' |
| WRITE (*,*) 'SELECT * from PurchDB.Vendors' |
| EXEC SQL SELECT VendorNumber, |
| 1 VendorName, |
| 2 ContactName, |
| 3 PhoneNumber, |
| 4 VendorStreet, |
| 5 VendorCity, |
| 6 VendorState, |
| 7 VendorZipCode, |
| 8 VendorRemarks |
| 9 INTO :VendorNumber, |
| 1 :VendorName, |
| 2 :ContactName :ContactNameInd, |
| 3 :PhoneNumber :PhoneNumberInd, |
| 4 :VendorStreet, |
| 5 :VendorCity, |
| 6 :VendorState, |
| 7 :VendorZipCode, |
| 8 :VendorRemarks :VendorRemarksInd |
| 9 FROM PurchDB.Vendors |
| 1 WHERE VendorNumber = :VendorNumber |
| |
| |
| |
| |
| |
| |
| |
_____________________________________________________________________________
Figure 7-3. Program forex7: Using INSERT, UPDATE, SELECT, and DELETE (page 9 of 21)