HP 3000 Manuals HP 3000 Manuals
Transaction Management for Simple Operations (cont.)
_____________________________________________________________________________________
| |
| IF (SQLCode .EQ. OK) THEN |
| CALL |
| DisplayRow(VendorNumber,VendorName,ContactName, |
| 1 PhoneNumber,VendorStreet,VendorCity, |
| 2 VendorState,VendorZipCode,VendorRemarks, |
| 3 ContactNameInd,PhoneNumberInd,VendorRemarksInd) |
| ELSEIF (SQLCode .EQ. NotFound) THEN |
| WRITE (*,*) ' ' |
| WRITE (*,*) 'Row not found!' |
| ELSEIF (SQLCode .EQ. MultipleRows) THEN |
| WRITE (*,*) ' ' |
| WRITE (*,*) 'WARNING: More than one row qualifies!' |
| ELSE |
| CALL SQLStatusCheck |
| ENDIF |
| CALL CommitWork |
| ENDIF |
| END DO |
| |
| RETURN |
| END |
| C (* End of Function Select *) |
| |
| SUBROUTINE DisplayUpdate(VendorNumber, VendorName, ContactName, |
| 1 PhoneNumber, VendorStreet, VendorCity, |
| 2 VendorState, VendorZipCode, VendorRemarks, |
| 3 ContactNameInd, PhoneNumberInd, VendorRemarksInd) |
| C**** SUBROUTINE to display and update 11 |
| |
| C**** a row from the 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 |
| |
| |
| |
| |
| |
| |
| |
| |
_____________________________________________________________________________________
Figure 7-3. Program forex7: Using INSERT, UPDATE, SELECT, and DELETE (page 10 of 21)
______________________________________________________________________________
| |
| 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 |
| |
| CALL DisplayRow(VendorNumber, VendorName, ContactName, |
| 1 PhoneNumber, VendorStreet, VendorCity, |
| 2 VendorState, VendorZipCode, VendorRemarks, |
| 3 ContactNameInd, PhoneNumberInd, VendorRemarksInd)|
| IF (AnyNulls(ContactNameInd, PhoneNumberInd, |
| 1 VendorRemarksInd)) THEN |
| IF (ContactNameInd .LT. 0) THEN |
| WRITE(*,*) |
| WRITE(*,100) |
| 100 FORMAT($, ' Enter new ContactName (0 for NULL) > ') |
| READ(*,110) ContactName |
| 110 FORMAT (A30) |
| ENDIF |
| |
| IF (PhoneNumberInd .LT. 0) THEN |
| WRITE (*,*) ' ' |
| WRITE(*,120) |
| 120 FORMAT($, ' Enter new PhoneNumber (0 for NULL) > ') |
| READ(*,130) PhoneNumber |
| 130 FORMAT(A16) |
| ENDIF |
| |
| IF (VendorRemarksInd .LT. 0) THEN |
| WRITE(*,*) |
| WRITE(*,140) |
| 140 FORMAT($, ' Enter new VendorRemarks (0 for NULL) > ') |
| READ(*,150) VendorRemarks |
| 150 FORMAT(A50) |
| ENDIF |
| |
______________________________________________________________________________
Figure 7-3. Program forex7: Using INSERT, UPDATE, SELECT, and DELETE (page 11 of 21)
_________________________________________________________________________________
| |
| IF (ContactName .EQ. '0') THEN |
| ContactNameInd = -1 |
| ELSE |
| ContactNameInd = 0 |
| ENDIF |
| |
| IF (PhoneNumber .EQ. '0') THEN |
| PhoneNumberInd = -1 |
| ELSE |
| PhoneNumberInd = 0 |
| ENDIF |
| |
| IF (VendorRemarks .EQ. '0') THEN |
| VendorRemarksInd = -1 |
| ELSE |
| VendorRemarksInd = 0 |
| ENDIF |
| |
| WRITE (*,*) 'UPDATE the PurchDB.Vendors table' |
| EXEC SQL UPDATE PurchDB.Vendors |
| 1 SET ContactName = :ContactName :ContactNameInd, |
| 2 PhoneNumber = :PhoneNumber :PhoneNumberInd, |
| 3 VendorRemarks = :VendorRemarks :VendorRemarksInd |
| 4 WHERE VendorNumber = :VendorNumber |
| |
| IF (SQLCode .NE. OK) THEN |
| CALL SQLStatusCheck |
| ENDIF |
| |
| ENDIF |
| RETURN |
| END |
| C (End of Subroutine DisplayUpdate) |
| |
| |
| LOGICAL*2 FUNCTION Update() 12 |
| |
| C**** FUNCTION to update rows from PurchDB.Vendors table. |
| |
| INTEGER NotFound,MultipleRows,OK |
| LOGICAL*2 AnyNulls |
| PARAMETER (NotFound = 100, |
| 1 MultipleRows = -10002, |
| 2 OK = 0) |
| |
| |
| |
| |
_________________________________________________________________________________
Figure 7-3. Program forex7: Using INSERT, UPDATE, SELECT, and DELETE (page 12 of 21)
____________________________________________________________________________
| |
| 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 *) |
| |
| Update = .FALSE. |
| VendorNumber = 1 |
| DO WHILE (VendorNumber .NE. 0) |
| WRITE (*,*) ' ' |
| WRITE (*,100) |
| 100 FORMAT($, ' Enter Vendor Number to UPDATE or a 0 to STOP > ')|
| READ (*,110) VendorNumber |
| 110 FORMAT(I4) |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
____________________________________________________________________________
Figure 7-3. Program forex7: Using INSERT, UPDATE, SELECT, and DELETE (page 13 of 21)
_____________________________________________________________________________
| |
| 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 |
| IF (SQLCode .EQ. OK) THEN |
| CALL DisplayUpdate (VendorNumber, VendorName, ContactName,|
| 1 PhoneNumber, VendorStreet, VendorCity, |
| 2 VendorState, VendorZipCode, VendorRemarks, |
| 3 ContactNameInd, PhoneNumberInd, VendorRemarksInd)|
| ELSEIF (SQLCode .EQ. NotFound) THEN |
| WRITE (*,*) ' ' |
| WRITE (*,*) 'Row not found!' |
| ELSEIF (SQLCode .EQ. MultipleRows) THEN |
| WRITE(*,*) ' ' |
| WRITE (*,*) 'WARNING: More than one row qualifies!' |
| CALL SQLStatusCheck |
| ENDIF |
| CALL CommitWork |
| ENDIF |
| END DO |
| |
| RETURN |
| END |
| C (* End of Function Update *) |
| |
| |
| |
| |
_____________________________________________________________________________
Figure 7-3. Program forex7: Using INSERT, UPDATE, SELECT, and DELETE (page 14 of 21)
_____________________________________________________________________________________
| |
| C**** SUBROUTINE to Display and Delete a row 13 |
| |
| C**** from the PurchDB.Vendors table |
| SUBROUTINE DisplayDelete(VendorNumber, VendorName, ContactName, |
| 1 PhoneNumber, VendorStreet, VendorCity, |
| 2 VendorState, VendorZipCode, VendorRemarks, |
| 3 ContactNameInd, PhoneNumberInd, VendorRemarksInd) |
| |
| CHARACTER Response |
| 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 |
| CHARACTER*2 VendorState |
| CHARACTER*10 VendorZipCode |
| CHARACTER*50 VendorRemarks |
| SQLIND VendorRemarksInd |
| CHARACTER*120 SQLMessage |
| EXEC SQL END DECLARE SECTION |
| |
| CALL DisplayRow(VendorNumber, VendorName, ContactName, |
| 1 PhoneNumber, VendorStreet, VendorCity, |
| 2 VendorState, VendorZipCode, VendorRemarks, |
| 3 ContactNameInd, PhoneNumberInd, VendorRemarksInd) |
| > |
| |
| |
| |
| |
| |
| |
| |
_____________________________________________________________________________________
Figure 7-3. Program forex7: Using INSERT, UPDATE, SELECT, and DELETE (page 15 of 21)
____________________________________________________________________________
| |
| WRITE (*,100) |
| 100 FORMAT($, ' Is it OK to DELETE this row (N/Y)? >') |
| READ (*, 110) Response |
| 110 FORMAT(A1) |
| IF ((Response .EQ. 'Y') .OR. (Response .EQ. 'y')) THEN |
| WRITE (*,*) ' ' |
| WRITE (*,*) 'DELETE row from PurchDB.Vendors!' |
| EXEC SQL DELETE FROM PurchDB.Vendors |
| 1 WHERE VendorNumber = :VendorNumber |
| IF (SQLCode .NE. OK) THEN |
| CALL SQLStatusCheck |
| ENDIF |
| ELSE |
| WRITE (*,*) ' ' |
| WRITE (*,*) 'Row not deleted from PurchDB.Vendors!' |
| ENDIF |
| RETURN |
| END |
| C**** (End of Subroutine DisplayDelete) |
| |
| |
| LOGICAL*2 FUNCTION Delete() 14 |
| |
| C**** FUNCTION to delete 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 |
| |
| |
| |
| |
____________________________________________________________________________
Figure 7-3. Program forex7: Using INSERT, UPDATE, SELECT, and DELETE (page 16 of 21)
____________________________________________________________________________
| |
| 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 *) |
| |
| Delete = .FALSE. |
| VendorNumber = 1 |
| DO WHILE (VendorNumber .NE. 0) |
| WRITE (*,*) ' ' |
| WRITE (*,100) |
| 100 FORMAT($, ' Enter Vendor Number to DELETE 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 |
| IF (SQLCode .EQ. OK) THEN |
| |
| |
| |
| |
| |
____________________________________________________________________________
Figure 7-3. Program forex7: Using INSERT, UPDATE, SELECT, and DELETE (page 17 of 21)
___________________________________________________________________________________
| |
| CALL DisplayDelete (VendorNumber, VendorName, ContactName, |
| 1 PhoneNumber, VendorStreet, VendorCity, |
| 2 VendorState, VendorZipCode, VendorRemarks, |
| 3 ContactNameInd, PhoneNumberInd, VendorRemarksInd) |
| ELSEIF (SQLCode .EQ. NotFound) THEN |
| WRITE (*,*) ' ' |
| WRITE (*,*) 'Row not found!' |
| ELSEIF (SQLCode .EQ. MultipleRows) THEN |
| WRITE (*,*) ' ' |
| WRITE (*,*) 'WARNING: More than one row qualifies!' |
| ELSE |
| CALL SQLStatusCheck |
| ENDIF |
| CALL CommitWork |
| ENDIF |
| END DO |
| RETURN |
| END |
| C** (End of Function Delete) |
| |
| |
| LOGICAL*2 FUNCTION Insert() 15 |
| |
| C** FUNCTION to insert a row into the 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 *) |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
___________________________________________________________________________________
Figure 7-3. Program forex7: Using INSERT, UPDATE, SELECT, and DELETE (page 18 of 21)
____________________________________________________________________________
| |
| 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 *) |
| |
| Insert = .FALSE. |
| VendorNumber = 1 |
| DO WHILE (VendorNumber .NE. 0) |
| WRITE (*,*) ' ' |
| WRITE (*,100) |
| 100 FORMAT($, ' Enter Vendor Number to INSERT or a 0 to STOP > ')|
| READ (*,110) VendorNumber |
| 110 FORMAT(I4) |
| IF (VendorNumber .NE. 0) THEN |
| WRITE (*,*) ' ' |
| WRITE (*,120) |
| 120 FORMAT($,' Enter Vendor Name > ') |
| READ (*,130) VendorName |
| 130 FORMAT(A30) |
| |
| WRITE(*,*) |
| WRITE (*,140) |
| 140 FORMAT($,' Enter new ContactName (0 for NULL) > ') |
| READ(*,150) ContactName |
| 150 FORMAT (A30) |
| |
| IF (ContactName .EQ. '0') THEN |
| ContactNameInd = -1 |
| ELSE |
| ContactNameInd = 0 |
| ENDIF |
| |
| WRITE (*,*) |
| |
| |
____________________________________________________________________________
Figure 7-3. Program forex7: Using INSERT, UPDATE, SELECT, and DELETE (page 19 of 21)
____________________________________________________________________
| |
| WRITE (*,160) |
| 160 FORMAT($,' Enter new PhoneNumber (0 for NULL) > ') |
| READ(*,170) PhoneNumber |
| 170 FORMAT(A16) |
| |
| IF (PhoneNumber .EQ. '0') THEN |
| PhoneNumberInd = -1 |
| ELSE |
| PhoneNumberInd = 0 |
| ENDIF |
| |
| WRITE(*,*) |
| WRITE (*,180) |
| 180 FORMAT($,' Enter new Vendor Street > ') |
| READ(*,190) VendorStreet |
| 190 FORMAT(A30) |
| |
| WRITE(*,*) |
| WRITE (*,200) |
| 200 FORMAT($,' Enter new Vendor City > ') |
| READ(*,210) VendorCity |
| 210 FORMAT(A20) |
| |
| WRITE(*,*) |
| WRITE (*,220) |
| 220 FORMAT($,' Enter new Vendor State > ') |
| READ(*,230) VendorState |
| 230 FORMAT(A2) |
| |
| WRITE(*,*) |
| WRITE (*,240) |
| 240 FORMAT($,' Enter new Vendor Zip Code > ') |
| READ(*,250) VendorZipCode |
| 250 FORMAT(A10) |
| |
| WRITE(*,*) |
| WRITE (*,260) |
| 260 FORMAT($,' Enter new VendorRemarks (0 for NULL > ')|
| READ(*,270) VendorRemarks |
| 270 FORMAT(A50) |
| |
| IF (VendorRemarks .EQ. '0') THEN |
| VendorRemarksInd = -1 |
| ELSE |
| VendorRemarksInd = 0 |
| ENDIF |
| |
| |
____________________________________________________________________
Figure 7-3. Program forex7: Using INSERT, UPDATE, SELECT, and DELETE (page 20 of 21)
__________________________________________________________________________
| |
| CALL BeginTransaction |
| WRITE (*,*)'INSERT new row into PurchDB.Vendors' |
| EXEC SQL INSERT |
| 1 INTO PurchDB.Vendors |
| 2 (VendorNumber, VendorName, ContactName, |
| 3 PhoneNumber, VendorStreet, VendorCity, |
| 4 VendorState, VendorZipCode, VendorRemarks)|
| 5 VALUES (:VendorNumber, |
| 6 :VendorName, |
| 6 :ContactName :ContactNameInd, |
| 7 :PhoneNumber :PhoneNumberInd, |
| 8 :VendorStreet, |
| 9 :VendorCity, |
| 1 :VendorState, |
| 2 :VendorZipCode, |
| 3 :VendorRemarks :VendorRemarksInd) |
| IF (SQLCode .NE. OK) THEN |
| CALL SQLStatusCheck |
| CALL RollBackWork |
| ELSE |
| CALL CommitWork |
| ENDIF |
| ENDIF |
| END DO |
| RETURN |
| END |
| C (* End of Function Insert *) |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
__________________________________________________________________________
Figure 7-3. Program forex7: Using INSERT, UPDATE, SELECT, and DELETE (page 21 of 21)