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.
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:
var
EXEC SQL BEGIN DECLARE SECTION;
PartNumber : packed array[1..16] of char;
PartName : packed array[1..30] of char;
PartNameInd : sqlInd;
SalesPrice : longreal;
SalesPriceInd : sqlInd;
EXEC SQL END DECLARE SECTION;
.
.
.
procedure DoQuery;
begin
This procedure accepts a part number from the user,
then executes a query to determine whether one or
more rows containing that value actually exists.
EXEC SQL SELECT PartNumber, PartName, SalesPrice
INTO :PartNumber,
:PartName :PartNameInd,
:SalesPrice :SalesPriceInd
FROM PurchDB.Parts
WHERE PartNumber = :PartNumber;
case SQLCA.SQLCODE of
0 : DisplayDelete;
100 : writeln('Row not found!');
-10002 : writeln('WARNING: More than one row qualifies!');
otherwise : SqlStatusCheck;
end;
.
.
.
|
procedure 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
WHERE PartNumber = :PartNumber;
|
The chapter, "Simple Data Manipulation," provides more details about simple data manipulation.
