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.

Figure 7-1 Flow Chart of Program forex7

Figure 7-2 Runtime Dialog of Program forex7


   : 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

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

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