HP 3000 Manuals

ALLBASE/SQL FORTRAN Application Programming Guide

Simple Data Manipulation 

In simple data manipulation, you retrieve or insert single rows or update
one or more rows based on a specific criterion.  In most cases, the
simple data manipulation technique is used to support the random
retrieval and/or change of specific rows.  The duration of locks can be
minimized by making each data manipulation operation a separate
transaction.

In the following example, if the user wants to perform a DELETE
operation, the program performs the operation only if a single row 
qualifies.  If no rows qualify or if more than one row qualifies, the
program displays a message.  Note that the host variables in this case
are designed to accommodate only a single row.  In addition, two of the
columns may contain null values, so an indicator variable is used for
these columns:

               EXEC SQL BEGIN DECLARE SECTION
               CHARACTER*16     PartNumber
               CHARACTER*30     PartName
               SQLIND           PartNameInd
               DOUBLE PRECISION SalesPrice
               SQLIND           SalesPriceInd
               EXEC SQL END DECLARE SECTION
               .
               .
               .
           SUBROUTINE DoQuery

         . This procedure accepts a part number from the user,
           .   then executes a query to determine whether one or
           .   more rows containing that value actually exist. 

            EXEC SQL SELECT  PartNumber, PartName, SalesPrice
          1            INTO :PartNumber,
          2                 :PartName :PartNameInd,
          3                 :SalesPrice :SalesPriceInd
          4            FROM  PurchDB.Parts
          5           WHERE  PartNumber = :PartNumber

            IF (SQLCode .EQ. 0) THEN
              CALL DisplayDelete
            ELSEIF (SQLCode .EQ.100) THEN
              CALL WriteOut ('Row Not Found!')
            ELSEIF (SQLCode .EQ. -10002) THEN
              CALL WriteOut ('WARNING: More than one row qualifies!')
            ELSE
               CALL SQLStatusCheck
            ENDIF
            .
            .
            .
            RETURN
            END

           SUBROUTINE DisplayDelete

         . The qualifying row is displayed for the user to
           .   verify that it should be deleted before the following
           .   command is executed:

                EXEC SQL DELETE FROM PurchDB.Parts
          1                  WHERE PartNumber = :PartNumber
            .
            .
            .
           RETURN
           END

Chapter 7 provides more details about simple data manipulation.

MPE/iX 5.0 Documentation