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.
