HP 3000 Manuals HP 3000 Manuals
Sample Program Using Cursor Operations
The flow chart in Figure 6-5 summarizes the functionality of program
cex8. This program uses a cursor and the UPDATE WHERE CURRENT command to
update column ReceivedQty in table PurchDB.OrderItems. The runtime
dialog for cex8 appears in Figure 6-6, and the source code in Figure 6-7.
The program first executes function 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, function DeclareCursor
only displays the message Declare the 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 cex8 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 function FetchUpdate until the Done flag is set
to TRUE 28 .
Function FetchUpdate prompts for an order number or a zero 17 . When
the user enters a zero, function FetchUpdate ends and the main program
prompts the user to indicate whether another OrderNumber should be
FETCHed. When the user enters an order number, the program begins a
transaction by executing function 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 function DisplayUpdate
22 to display the current row and optionally update it.
Function DisplayUpdate executes function DisplayRow 10 to display the
current row 6 . If column ReceivedQty in the current row contains a
null value, the message ReceivedQty is NULL is displayed.
Function DisplayUpdate then prompts whether the user 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 zero, a null value is assigned to this column.
The program then prompts whether to FETCH another row 13 . If so, the
FETCH command is re-executed. If not, the program prompts the user
whether to make permanent any updates that may have made made to the
active set 14 . To keep any row changes, the program executes function
EndTransaction 16 which executes the COMMIT WORK command 4 . To undo
any row changes, the program executes function RollBack 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 an N in response to the main program's
prompt to FETCH another order number, the program terminates by executing
function ReleaseDBE 29 , which executes the RELEASE command 2 .
![[]](../../pic3k/M011754/FFN743c50.gif)
Figure 6-5. Flow Chart of Program cex8
_________________________________________________________________
| |
| Program to UPDATE OrderItems Table via a CURSOR - cex8 |
| 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 the DBEnvironment |
| |
| Connect to PartsDBE |
| |
| Declare Cursor |
| |
| Enter OrderNumber or 0 to STOP > 30520 |
| |
| Begin Work |
| |
| Open the Cursor |
| |
| Fetch the next row. |
| Order Number: 30520 |
| Item Number: 1 |
| Vendor Part Number: 9375 |
| Received Quantity: 9 |
| |
| Do you want to change ReceivedQty (Y/N)? > n |
| |
| Do you want to see another row (Y/N)? > y |
| |
| Fetch the next row. |
| Order Number: 30520 |
| Item Number: 2 |
| Vendor Part Number: 9105 |
| Received Quantity: 3 |
| |
| Do you want to change ReceivedQty (Y/N)? > y |
| |
| Enter New ReceivedQty or 0 for NULL > 15 |
| |
_________________________________________________________________
Figure 6-6. Runtime Dialog of Program cex8
___________________________________________________________
| |
| Update the PurchDB.OrderItems table |
| |
| Do you want to see another row (Y/N)? > y |
| |
| Fetch the next row. |
| Order Number: 30520 |
| Item Number: 3 |
| Vendor Part Number: 9135 |
| Received Quantity: 3 |
| |
| Do you want to change ReceivedQty (Y/N)? > n |
| |
| Do you want to see another row (Y/N)? > y |
| |
| Fetch the next row. |
| |
| Row not found or no more rows! |
| |
| Do you want to save your changes (Y/N)? > y |
| |
| Close the Cursor |
| |
| Commit Work |
| 1 rows changed! |
| Do you want to FETCH another OrderNumber (Y/N)? > y|
| |
| Enter an OrderNumber or a 0 to STOP > 30510 |
| |
| Begin Work |
| |
| Open the Cursor |
| |
| Fetch the next row. |
| Order Number: 30510 |
| Item Number: 1 |
| Vendor Part Number: 1001 |
| Received Quantity: 3 |
| |
| Do you want to change ReceivedQty (Y/N)? > n |
| |
| Do you want to see another row (Y/N)? > n |
| |
| Close Cursor |
| |
| Commit Work |
| Do you want to FETCH another OrderNumber (Y/N)? > n|
| Release PartsDBE |
| |
___________________________________________________________
Figure 6-6. Runtime Dialog of Program cex8 (page 2 of 2)
____________________________________________________________________________
| |
| /* Program cex8 */ |
| |
| /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */|
| /* This program illustrates the use of SQL's UPDATE WHERE */|
| /* CURRENT command using a cursor to update a single row */|
| /* at a time. */|
| /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */|
| |
| typedef int boolean; |
| |
| boolean Abort; |
| boolean Done; |
| boolean DoFetch; |
| char response[2]; |
| int RowCounter; |
| |
| #include <stdio.h> |
| |
| #define OK 0 |
| #define NotFound 100 |
| #define MultipleRows -10002 |
| #define DeadLock -14024 |
| #define FALSE 0 |
| #define TRUE 1 |
| |
| sqlca_type sqlca; /* SQL Communication Area */ |
| |
| /* Begin Host Variable Declarations */ |
| EXEC SQL BEGIN DECLARE SECTION; |
| int OrderNumber; |
| int ItemNumber; |
| char VendPartNumber[17]; |
| int ReceivedQty; |
| sqlind ReceivedQtyInd; |
| char SQLMessage[133]; |
| EXEC SQL END DECLARE SECTION; |
| /* End Host Variable Declarations */ |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
____________________________________________________________________________
Figure 6-7. Program cex8: Using UPDATE WHERE CURRENT
___________________________________________________________________________
| |
| int SQLStatusCheck() /* Function to Display Error Messages */ |
| { |
| Abort = FALSE; |
| if (sqlca.sqlcode < DeadLock) Abort = TRUE; |
| |
| do { |
| EXEC SQL SQLEXPLAIN :SQLMessage; |
| printf("\n"); |
| printf("%s\n",SQLMessage); |
| } while (sqlca.sqlcode != 0); |
| |
| if (Abort) { |
| |
| ReleaseDBE(); |
| } |
| |
| } /* End SQLStatusCheck Function */ |
| |
| boolean ConnectDBE() /* Function to Connect to PartsDBE */ |
| 27 |
| { |
| boolean ConnectDBE; |
| printf("\n Connect to PartsDBE"); |
| EXEC SQL CONNECT TO 'PartsDBE'; 1 |
| |
| ConnectDBE = TRUE; |
| if (sqlca.sqlcode != OK) { |
| ConnectDBE = FALSE; |
| SQLStatusCheck(); |
| } /* End if */ |
| return (ConnectDBE); |
| } /* End of ConnectDBE Function */ |
| |
| |
| int ReleaseDBE() /* Function to Release PartsDBE */ |
| 29 |
| { |
| printf("\n Release PartsDBE"); |
| printf("\n"); |
| EXEC SQL RELEASE; 2 |
| |
| Done = TRUE; |
| if (sqlca.sqlcode != OK) SQLStatusCheck(); |
| |
| } /* End ReleaseDBE Function */ |
| |
| |
___________________________________________________________________________
Figure 6-7. Program cex8: Using UPDATE WHERE CURRENT (page 2 of 8)
___________________________________________________________________________
| |
| boolean BeginTransaction() /* Function to Begin Work */ 18 |
| { |
| boolean BeginTransaction; |
| printf("\n"); |
| printf("\n Begin Work"); |
| |
| EXEC SQL BEGIN WORK; 3 |
| |
| if (sqlca.sqlcode != OK) { |
| SQLStatusCheck(); |
| ReleaseDBE(); |
| } |
| |
| } /* End BeginTransaction Function */ |
| |
| |
| int EndTransaction() /* Function to Commit Work */ 16 |
| { |
| printf("\n"); |
| printf("\n Commit Work"); |
| EXEC SQL COMMIT WORK; 4 |
| if (sqlca.sqlcode != OK) { |
| SQLStatusCheck(); |
| ReleaseDBE(); |
| } |
| |
| } /* End EndTransaction Function */ |
| |
| |
| int RollBack() /* Function to RollBack work */ 15 |
| { |
| printf("\n"); |
| printf("\n RollBack Work"); |
| EXEC SQL ROLLBACK WORK; 5 |
| if (sqlca.sqlcode != OK) { |
| SQLStatusCheck(); |
| ReleaseDBE(); |
| } |
| |
| } /* End of RollBack Function */ |
| |
| |
| |
| |
| |
| |
| |
___________________________________________________________________________
Figure 6-7. Program cex8: Using UPDATE WHERE CURRENT (page 3 of 8)
___________________________________________________________________________
| |
| int DisplayRow() /* Function to Display Parts Table Rows */ 10 |
| { |
| printf("\n"); |
| printf("Order Number: %10d\n", OrderNumber); 6 |
| printf("Item Number: %10d\n", ItemNumber); |
| printf("Vendor Part Number: %s\n", VendPartNumber); |
| |
| if (ReceivedQtyInd != 0) |
| printf("Received Quantity: is NULL \n"); |
| else |
| printf("Received Quantity: %5d\n", ReceivedQty); |
| |
| } /* End of DisplayRow */ |
| |
| |
| int DeclareCursor() /* Function to Declare the Cursor */ 26 |
| { |
| printf("\n"); |
| printf("\n Declare the 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 DeclareCursor Function */ |
| |
| boolean OpenCursor() /* Function to Open the Declared Cursor */ |
| 19 |
| { |
| boolean OpenCursor; |
| OpenCursor = TRUE; |
| |
| printf("\n"); |
| printf("\n Open the Cursor"); |
| EXEC SQL OPEN OrderReview; 8 |
| if (sqlca.sqlcode != OK) { |
| OpenCursor = FALSE; |
| SQLStatusCheck(); |
| RollBack(); |
| } |
| return(OpenCursor); |
| } /* End of OpenCursor Function */ |
| |
___________________________________________________________________________
Figure 6-7. Program cex8: Using UPDATE WHERE CURRENT (page 4 of 8)
___________________________________________________________________________
| |
| int CloseCursor() /* Function to Close the Declared Cursor */ |
| { |
| printf("\n"); |
| printf("\n Close the Cursor"); |
| EXEC SQL CLOSE OrderReview; |
| if (sqlca.sqlcode != OK) { |
| SQLStatusCheck(); |
| EndTransaction(); |
| } |
| |
| } /* End of CloseCursor Function */ |
| |
| |
| int DisplayUpdate() /* Display & Update row in OrderItems Table*/ |
| { 22 |
| DisplayRow(); 10 |
| printf("\n"); |
| |
| printf("\n Do you want to change ReceivedQty (Y/N)? > "); 11 |
| scanf("%s",response); |
| if ((response[0] == 'Y') || (response[0] == 'y')) { |
| printf("\n"); |
| printf("\n Enter new ReceivedQty or a 0 for NULL > "); |
| scanf("%d5",&ReceivedQty); |
| if (ReceivedQty == 0) |
| ReceivedQtyInd = -1; |
| else |
| ReceivedQtyInd = 0; |
| |
| printf("\n UPDATE the PurchDB.OrderItems table"); |
| EXEC SQL UPDATE PurchDB.OrderItems 12 |
| SET ReceivedQty = :ReceivedQty :ReceivedQtyInd |
| WHERE CURRENT OF OrderReview; |
| |
| if (sqlca.sqlcode != OK) |
| SQLStatusCheck(); |
| else |
| RowCounter = RowCounter + 1; |
| } |
| |
| printf("\n"); |
| printf("\n Do you want to see another row (Y/N)? > "); 13 |
| scanf("%s",response); |
| if ((response[0] == 'N') || (response[0] == 'n')) { |
| if (RowCounter > 0) { |
| printf("\n"); |
| printf("\n Do you want to save the changes you made (Y/N)?>"); |
| scanf("%s",response); 14 |
___________________________________________________________________________
Figure 6-7. Program cex8: Using UPDATE WHERE CURRENT (page 5 of 8)
___________________________________________________________________________
| |
| if ((response[0] == 'N') || (response[0] == 'n')) { |
| CloseCursor(); |
| RollBack(); 15 |
| DoFetch = FALSE; |
| } |
| else { |
| CloseCursor(); |
| EndTransaction(); 16 |
| printf(RowCounter," %d\n rows were changed!"); |
| DoFetch = FALSE; |
| } |
| } |
| if (RowCounter == 0) { |
| CloseCursor(); |
| EndTransaction(); |
| DoFetch = FALSE; |
| } |
| } |
| |
| } /* End DisplayUpdate Function */ |
| |
| |
| int FetchUpdate() /* Fetch a row to Update within OrderItems */ |
| { 28 |
| printf("\n"); |
| printf("\n Enter an OrderNumber or a 0 to STOP > "); 17 |
| scanf("%d",&OrderNumber); |
| RowCounter = 0; |
| if (OrderNumber != 0) { |
| BeginTransaction(); 18 |
| if (OpenCursor()) { 19 |
| DoFetch = TRUE; |
| do { |
| printf("\n"); |
| printf("\n FETCH the next row."); |
| |
| EXEC SQL FETCH OrderReview 21 |
| INTO :OrderNumber, |
| :ItemNumber, |
| :VendPartNumber, |
| :ReceivedQty :ReceivedQtyInd; |
| |
| |
| |
| |
| |
| |
___________________________________________________________________________
Figure 6-7. Program cex8: Using UPDATE WHERE CURRENT (page 6 of 8)
________________________________________________________________________________
| |
| switch(sqlca.sqlcode) { |
| |
| case OK: DisplayUpdate(); 22 |
| break; |
| case NotFound: DoFetch = FALSE; 20 |
| printf("\n"); |
| printf("\n Row not found or no more rows!"); |
| if (RowCounter > 0) { |
| printf("\n"); |
| printf("\n Do you want to save your changes (Y/N)?>"); |
| scanf("%s",response); |
| if ((response[0] == 'N') || (response[0] == 'n')) {|
| CloseCursor(); 9 |
| RollBack(); |
| } |
| else { |
| CloseCursor(); 9 |
| EndTransaction(); 23 |
| printf(RowCounter,"%d\n rows were changed!"); |
| } |
| } |
| if (RowCounter == 0) { |
| CloseCursor(); 9 |
| EndTransaction(); 23 |
| } |
| break; |
| default: DoFetch = FALSE; 20 |
| SQLStatusCheck(); |
| CloseCursor(); 9 |
| RollBack(); 24 |
| break; |
| } /* End switch */ |
| |
| } while (DoFetch != FALSE); /* End do */ |
| |
| } /* End if open */ |
| |
| } /* End if OrderNumber */ |
| |
| } /* End of FetchUpdate Function */ |
| |
| |
| |
| |
| |
| |
| |
________________________________________________________________________________
Figure 6-7. Program cex8: Using UPDATE WHERE CURRENT (page 7 of 8)
_______________________________________________________________________________
| |
| main() /* Beginning of program */ |
| { |
| printf("\n Program to UPDATE the OrderItems table via a CURSOR - cex8");|
| printf("\n"); |
| printf("\n Event list:"); |
| printf("\n CONNECT TO PartsDBE"); |
| printf("\n Prompt for an Order Number"); |
| printf("\n BEGIN WORK"); |
| printf("\n OPEN CURSOR"); |
| printf("\n FETCH a row"); |
| printf("\n Display the retrieved row"); |
| printf("\n Prompt for new Received Quantity"); |
| printf("\n UPDATE row within the OrderItems table"); |
| printf("\n FETCH the next row, if any, with the same Order Number"); |
| printf("\n Repeat the above five steps until there are no more rows");|
| printf("\n CLOSE CURSOR"); |
| printf("\n End Transaction"); |
| printf("\n Repeat the above eleven steps until the user enters a 0"); |
| printf("\n RELEASE the DBEnvironment"); |
| printf("\n"); |
| |
| if (ConnectDBE()) { 27 |
| |
| DeclareCursor(); 26 |
| |
| Done = FALSE; |
| do { |
| FetchUpdate(); 28 |
| printf("\n Do you want to FETCH another OrderNumber (Y/N)?>"); |
| scanf("%s",response); |
| if ((response[0] == 'N') || (response[0] == 'n')) Done = TRUE; |
| } while (Done != TRUE); |
| |
| ReleaseDBE(); 29 |
| } |
| else |
| printf("\n Error: Cannot Connect to PartsDBE!\n"); |
| |
| } /* End of Program */ |
| |
| |
| |
| |
| |
| |
| |
| |
_______________________________________________________________________________
Figure 6-7. Program cex8: Using UPDATE WHERE CURRENT (page 8 of 8)