HP 3000 Manuals HP 3000 Manuals
Program Using UPDATE WHERE CURRENT
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.
![[]](../../pic3k/M012400/FFN863c50.gif)
Figure 8-4. Flow Chart 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 |
| |
| |
| |
| |
| |
| |
| |
| |
_________________________________________________________________
Figure 8-5. Runtime Dialog of Program pasex8
____________________________________________________
| |
| 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-5. Runtime Dialog of Program pasex8 (page 2 of 2)
____________________________________________________________________________
| |
| $Heap_Dispose ON$ |
| $Heap_Compact ON$ |
| Standard_Level 'HP_Pascal$ |
| (* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *)|
| (* 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; |
| |
| |
| |
| |
| |
| |
| |
____________________________________________________________________________
Figure 8-6. Program pasex8: Using UPDATE WHERE CURRENT
____________________________________________________________________________
| |
| 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 '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 *) |
| |
____________________________________________________________________________
Figure 8-6. Program pasex8: Using UPDATE WHERE CURRENT (page 2 of 7)
____________________________________________________________________________
| |
| 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 $ |
| |
| |
| |
| |
| |
| |
| |
| |
____________________________________________________________________________
Figure 8-6. Program pasex8: Using UPDATE WHERE CURRENT (page 3 of 7)
____________________________________________________________________________
| |
| 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; |
| |
____________________________________________________________________________
Figure 8-6. Program pasex8: Using UPDATE WHERE CURRENT (page 4 of 7)
____________________________________________________________________________
| |
| 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); |
| 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$ |
____________________________________________________________________________
Figure 8-6. Program pasex8: Using UPDATE WHERE CURRENT (page 5 of 7)
____________________________________________________________________________
| |
| 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 RowCounter = 0 then |
| begin |
| CloseCursor; |
| CommitWork; |
| end; |
| |
| |
____________________________________________________________________________
Figure 8-6. Program pasex8: Using UPDATE WHERE CURRENT (page 6 of 7)
_____________________________________________________________________________
| |
| 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 *) |
| |
| |
_____________________________________________________________________________
Figure 8-6. Program pasex8: Using UPDATE WHERE CURRENT (page 7 of 7)