The flow chart in Figure 8-4 summarizes the functionality
of program pasex8. This program uses a cursor and the UPDATE WHERE
CURRENT command to update column ReceivedQty in table
PurchDB.OrderItems. The runtime dialog for pasex8
appears in Figure 8-5, and the source code in Figure 8-6.
The program first executes procedure
DeclareCursor 26, which
contains the DECLARE CURSOR command 7. This
command is a preprocessor directive and is not executed at run time.
At run time, procedure DeclareCursor only displays the message,
Declare Cursor. The DECLARE CURSOR command defines a cursor
named OrderReview. The cursor is associated with a SELECT
command that retrieves the following columns for all rows in
table PurchDB.OrderItems having a specific order number but
no null values in column VendPartNumber:
OrderNumber (defined NOT NULL)
ItemNumber (defined NOT NULL)
VendPartNumber
ReceivedQty
|
Cursor OrderReview has a FOR UPDATE clause naming column
ReceivedQty to allow the user to change the value in this
column.
To establish a DBE session, program pasex8 executes function
ConnectDBE 27. This function evaluates to TRUE when
the
CONNECT command 1 for the
sample DBEnvironment, PartsDBE, is
successfully executed.
The program then executes procedure FetchUpdate
until the Done flag is set to TRUE 28.
Procedure FetchUpdate |
 |
Procedure FetchUpdate prompts for an order number or a
0 17. When the user enters a 0, the Done flag is set to
TRUE 25,
and the program terminates. When the user enters
an order number, the program begins a transaction by executing
procedure BeginTransaction 18, which executes the
BEGIN WORK command 3.
Cursor OrderReview is then opened by invoking function
OpenCursor 19. This function, which executes the OPEN
command 8, evaluates to TRUE when the command is successful.
A row at a time is retrieved and optionally updated until
the DoFetch flag
is set to FALSE 20. This flag becomes false when:
The FETCH command fails; this command fails when no rows
qualify for the active set, when the last row has already been
fetched, or when ALLBASE/SQL cannot execute this command for some
other reason.
The program user wants to stop reviewing rows from the active set.
The FETCH command 21 names an indicator variable for
ReceivedQty, the only column in the query result that may contain
a null value. If the FETCH command is successful, the program executes procedure DisplayUpdate
22 to display the current row and optionally update it.
Procedure DisplayUpdate |
|
Procedure DisplayUpdate executes procedure
DisplayRow 10 to display
the current row.
The user is asked whether he
wants to update the current ReceivedQty value 11. If so,
the user is prompted for a new value. The value accepted is used
in an UPDATE WHERE CURRENT command 12.
If the user entered a 0, a null value is assigned to this
column.
The program then asks whether to FETCH another row 13. If
so, the FETCH command is re-executed. If not, the program
asks whether the user wants to make permanent any updates he
may have made in the active set 14. To keep any row
changes, the program executes procedure CommitWork 16,
which executes the COMMIT WORK command 4. To undo any row
changes, the program executes procedure RollBackWork 15,
which executes the ROLLBACK WORK command 5.
The COMMIT WORK command is also executed
when ALLBASE/SQL sets SQLCA.SQLCODE to
100 following execution of the FETCH command 23.
SQLCA.SQLCODE is set to 100 when
no rows qualify for the active set or when the last row has
already been fetched. If the FETCH command fails for some other
reason, the ROLLBACK WORK command is executed instead 24.
Before any COMMIT WORK or ROLLBACK WORK command is executed,
cursor OrderReview is closed 9.
Although the cursor is automatically closed whenever a
transaction is terminated, it is good programming practice to
use the CLOSE command to close open cursors prior to terminating
transactions.
When the program user enters a 0 in response to the
order number prompt 17, the program
terminates by executing procedure TerminateProgram 29,
which executes the RELEASE command 2.
Explicit status checking is used throughout this program. After
each embedded SQL command is executed, SQLCA.SQLCode is checked.
If SQLCode is less than 0, the program executes procdure
SQLStatusCheck, which executes the SQLEXPLAIN
command.
Figure 8-4 Flow Chart of Program pasex8
![[Flow Chart of Program pasex8]](img/x0601.gif)
Figure 8-5 Runtime Dialog of Program pasex8
Program to UPDATE OrderItems Table via a CURSOR - pasex8
Event List:
Connect to PartsDBE
Prompt for Order Number
Begin Work
Open Cursor
FETCH a row
Display the retrieved row
Prompt for new Received Quantity
Update row within OrderItems table
FETCH the next row, if any, with the same Order Number
Repeat the above five steps until there are no more rows
Close Cursor
End Transaction
Repeat the above eleven steps until user enters 0
Release PartsDBE
Declare Cursor
Connect to PartsDBE
Enter OrderNumber or 0 to STOP > 30520
Begin Work
Open Cursor
OrderNumber: 30520
ItemNumber: 1
VendPartNumber: 9375
ReceivedQty 9
Do you want to change ReceivedQty (Y/N)? > n
Do you want to see another row (Y/N)? > y
OrderNumber: 30520
ItemNumber: 2
VendPartNumber: 9105
ReceivedQty is 3
Do you want to change ReceivedQty (Y/N)? > y
Enter New ReceivedQty (0 for NULL)> 15
Update PurchDB.OrderItems Table
Do you want to see another row (Y/N)? > y
OrderNumber: 30520
ItemNumber: 3
VendPartNumber: 9135
ReceivedQty 3
Do you want to change ReceivedQty (Y/N)? > n
Do you want to see another row (Y/N)? > y
Row not found or no more rows
Want to save your changes (Y/N)? > y
Close Cursor
Commit Work
1 row(s) changed.
Enter OrderNumber or 0 to STOP > 30510
Begin Work
Open Cursor
OrderNumber: 30510
ItemNumber: 1
VendPartNumber: 1001
ReceivedQty 3
Do you want to change ReceivedQty (Y/N)? > n
Do you want to see another row (Y/N)? > n
Close Cursor
Commit Work
Enter OrderNumber or 0 to STOP > 0
|
Figure 8-6 Program pasex8: Using UPDATE WHERE CURRENT
(* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *)
(* This program illustrates the use of UPDATE WHERE CURRENT *)
(* with a Cursor to update a single row at a time. *)
(* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *)
Program pasex8(input, output);
const
OK = 0;
NotFound = 100;
DeadLock = -14024;
var
(* Begin Host Variable Declarations *)
EXEC SQL BEGIN DECLARE SECTION;
OrderNumber : integer;
ItemNumber : integer;
VendPartNumber : packed array [1..16] of char;
ReceivedQty : SmallInt;
ReceivedQtyInd : SqlInd;
SQLMessage : packed array[1..132] of char;
EXEC SQL END DECLARE SECTION;
(* End Host Variable Declarations *)
SQLCA : SQLCA_type; (* SQL Communication Area *)
Abort : boolean;
Done : boolean;
DoFetch : boolean;
Response : packed array [1..3] of char;
RowCounter : integer;
procedure TerminateProgram; forward;
procedure SQLStatusCheck; (* Procedure to Display Error Messages *)
begin
Abort := FALSE;
if SQLCA.SQLCODE < DeadLock then Abort := TRUE;
repeat
EXEC SQL SQLEXPLAIN :SQLMessage;
writeln(SQLMessage);
until SQLCA.SQLCODE = 0;
if Abort then TerminateProgram;
end; (* End SQLStatusCheck Procedure *)
$PAGE $
function ConnectDBE: boolean; (* Function to Connect to PartsDBE *)
begin
writeln('Connect to PartsDBE');
EXEC SQL CONNECT TO '../sampledb/PartsDBE'; 1 >>
ConnectDBE := TRUE;
if SQLCA.SQLCODE <> OK then
begin
ConnectDBE := FALSE;
SQLStatusCheck;
end; (* End if *)
end; (* End of ConnectDBE Function *)
procedure TerminateProgram; (* Procedure to Release PartsDBE *)
begin
EXEC SQL RELEASE; 2
Done := TRUE;
end; (* End TerminateProgram Procedure *)
$PAGE $
procedure BeginTransaction; (* Procedure to Begin Work *)
begin
writeln;
writeln('Begin Work');
EXEC SQL BEGIN WORK; 3
if SQLCA.SQLCODE <> OK then
begin
SQLStatusCheck;
TerminateProgram;
end;
end; (* End BeginTransaction Procedure *)
procedure CommitWork; (* Procedure to Commit Work *)
begin
writeln('Commit Work');
EXEC SQL COMMIT WORK; 4
if SQLCA.SQLCODE <> OK then
begin
SQLStatusCheck;
TerminateProgram;
end;
end; (* End CommitWork Procedure *)
procedure RollBackWork; (* Procedure to RollBack Work *)
begin
writeln('Rollback Work');
EXEC SQL ROLLBACK WORK; 5
if SQLCA.SQLCODE <> OK then
begin
SQLStatusCheck;
TerminateProgram;
end;
end; (* End RollBackWork Procedure *)
procedure DisplayRow; (* Procedure to Display OrderItems Rows *) 6
begin
writeln;
writeln(' OrderNumber: ', OrderNumber);
writeln(' ItemNumber: ', ItemNumber);
writeln(' VendPartNumber: ', VendPartNumber);
if ReceivedQtyInd < 0 then
writeln(' ReceivedQty is NULL')
else
writeln(' ReceivedQty ', ReceivedQty);
end; (* End of DisplayRow *)
$PAGE $
Procedure DeclareCursor;
begin
writeln('Declare Cursor');
EXEC SQL DECLARE OrderReview 7
CURSOR FOR
SELECT OrderNumber,
ItemNumber,
VendPartNumber,
ReceivedQty
FROM PurchDB.OrderItems
WHERE OrderNumber = :OrderNumber
AND VendPartNumber IS NOT NULL
FOR UPDATE OF ReceivedQty;
end; (* End of DeclareCursor Procedure *)
function OpenCursor: boolean; (* Function to Open Cursor *)
begin
writeln('Open Cursor');
EXEC SQL OPEN OrderReview; 8
if SQLCA.SQLCODE <> OK then
begin
OpenCursor := FALSE;
SQLStatusCheck;
RollBackWork;
end
else
OpenCursor := TRUE;
end; (* End OpenCursor Function *)
procedure CloseCursor; (* Procedure to Close Cursor *)
begin
writeln;
writeln('Close Cursor');
EXEC SQL CLOSE OrderReview; 9
if SQLCA.SQLCODE <> OK then
begin
SQLStatusCheck;
TerminateProgram;
end;
end; (* End CloseCursor Procedure *)
$PAGE $
procedure DisplayUpdate; (* Display & Update row in Parts Table *)
begin
DisplayRow; 10
writeln;
prompt('Do you want to change ReceivedQty (Y/N)? > '); 11
readln(Response);
if Response[1] in ['Y','y'] then
begin
writeln;
prompt('Enter New ReceivedQty (0 for NULL)> ');
readln(ReceivedQty);
writeln('Update PurchDB.OrderItems Table');
if ReceivedQty = 0 then ReceivedQtyInd := -1
else ReceivedQtyInd := 0;
EXEC SQL UPDATE PurchDB.OrderItems
SET ReceivedQty = :ReceivedQty :ReceivedQtyInd 12
WHERE CURRENT OF OrderReview;
if SQLCA.SQLCODE <> OK then SQLStatusCheck
else RowCounter := RowCounter+1;
end;
writeln;
prompt('Do you want to see another row (Y/N)? > '); 13
readln(Response);
if Response[1] in ['N','n'] then
begin
if RowCounter > 0 then
begin
writeln;
prompt('Do you want to save any changes you made (Y/N)?> '); 14
readln(Response);
if Response[1] in ['N','n'] then
begin
CloseCursor;
RollBackWork; 15
DoFetch := FALSE;
end
else
begin
CloseCursor;
CommitWork; << 16 >>
writeln(RowCounter, ' row(s) changed.');
DoFetch := FALSE;
end;
end; (* end if RowCounter *)
if RowCounter = 0 then
begin
CloseCursor;
CommitWork;
DoFetch := FALSE;
end;
end;
end; (* End of DisplayUpdate Procedure *)
$PAGE$
procedure FetchUpdate;
begin
RowCounter := 0;
writeln;
prompt('Enter OrderNumber or 0 to STOP > '); << 17 >>
readln(OrderNumber);
if OrderNumber <> 0 then
begin
BeginTransaction; 18
if OpenCursor then 19
begin
DoFetch := TRUE;
while DoFetch = TRUE do 20
begin
EXEC SQL FETCH OrderReview 21
INTO :OrderNumber,
:ItemNumber,
:VendPartNumber,
:ReceivedQty :ReceivedQtyInd;
case SQLCA.SQLCODE of
OK : DisplayUpdate; 22
NotFound : begin
DoFetch := FALSE;
writeln;
writeln('Row not found or no more rows');
if RowCounter > 0 then
begin
prompt('Want to save your changes (Y/N)? > ');
readln(Response);
if Response[1] in ['N','n'] then
begin
CloseCursor;
RollBackWork;
end
else
begin
CloseCursor;
CommitWork; 23
writeln(RowCounter ,' row(s) changed.');
end;
end;
if RowCoounter = 0 then
begin
CloseCursor;
CommitWork;
end;
end;
otherwise begin
DoFetch := FALSE;
SQLStatusCheck;
CloseCursor;
RollbackWork; 24
end;
end; (* case *)
end; (* while *)
end; (* if OpenCursor *)
end (* end if OrderNumber *)
else
Done := TRUE; 25
end; (* End of FetchUpdate Procedure *)
$PAGE $
begin (* Beginning of Program *)
writeln('Program to UPDATE OrderItems Table via a CURSOR - pasex8');
writeln;
writeln('Event List:');
writeln(' Connect to PartsDBE');
writeln(' Prompt for Order Number');
writeln(' Begin Work');
writeln(' Open Cursor');
writeln(' FETCH a row');
writeln(' Display the retrieved row');
writeln(' Prompt for new Received Quantity');
writeln(' Update row within OrderItems table');
writeln(' FETCH the next row, if any, with the same Order Number');
writeln(' Repeat the above five steps until there are no more rows');
writeln(' Close Cursor');
writeln(' End Transaction');
writeln(' Repeat the above eleven steps until user enters 0');
writeln(' Release PartsDBE');
writeln;
DeclareCursor; 26
if ConnectDBE then 27
begin
Done := FALSE;
repeat 28
FetchUpdate
until Done;
end;
TerminateProgram; 29
end. (* End of Program *)
|
