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 SQLCA.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 SQLCA.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 SQLCA.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.

Figure 7-1 Flow Chart of Program forex7

Figure 7-2 Flow Chart of Program forex7 (page 2 of 2)

Figure 7-3 Runtime Dialog of Program 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 ../sampledb/PartsDBE 

   CONNECT TO ../sampledb/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

 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
  
 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

 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 ../sampledb/PartsDBE

 $

Figure 7-4 Program forex7: Using INSERT, UPDATE, SELECT, and DELETE

    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 (*,*) ' '

      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 *)
      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 *)

      LOGICAL*2 FUNCTION ConnectDBE()   3
C**** FUNCTION to connect to ../sampledb/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 ../sampledb/PartsDBE'
      EXEC SQL CONNECT TO '../sampledb/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 ../sampledb/PartsDBE
     
C             (* Begin SQL Communication Area *)
 
      EXEC SQL INCLUDE SQLCA
      EXEC SQL BEGIN DECLARE SECTION
      EXEC SQL END DECLARE SECTION

      WRITE(*,*) ' '
      WRITE(*,*) 'RELEASE ../sampledb/PartsDBE'
      EXEC SQL RELEASE
      RETURN
      END
C             (* End of Subroutine TerminateProgram *)
 
      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 *)

      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.
      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

      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
      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

          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

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

        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)

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)

        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 *)

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)
 
      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
      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

         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 *)
    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 (*,*)
          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
  
          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 *)

Program Using SELECT, UPDATE, DELETE, and INSERT

Technical documentation

» Table of Contents

» Index

Select Function

Update Function

Delete Function

Insert Function