HP 3000 Manuals

SQL Cursor Commands [ ALLBASE/SQL C Application Programming Guide ] MPE/iX 5.0 Documentation


ALLBASE/SQL C Application Programming Guide

SQL Cursor Commands 

The following ALLBASE/SQL commands are used in cursor processing:

   *   DECLARE CURSOR defines a cursor and associates it with a query.

   *   OPEN defines the active set.

   *   FETCH retrieves one row of the active set into host variables;
       when a row resides in host variables it is known as the current 
       row.  When a row is current and the active set is a query result
       derived from a single table, you can use one of the following two
       commands to change the row.

   *   UPDATE WHERE CURRENT updates the current row.

   *   DELETE WHERE CURRENT deletes the current row.

   *   CLOSE terminates access to the active set and frees up ALLBASE/SQL
       buffer space used to handle the cursor.

For a given cursor, the commands listed above (with the exception of
DECLARE CURSOR) should be contained within the same transaction.  Refer
to the ALLBASE/SQL Reference Manual for the complete syntax and semantics
of these commands.

DECLARE CURSOR 

The DECLARE CURSOR command names a cursor and associates it with a
particular SELECT command:

     DECLARE CursorName 
             [IN DBEFileSetName]
             CURSOR FOR
             SelectCommand 
             [FOR UPDATE OF ColumnName [,ColumnName...]

Note that the DECLARE CURSOR command has two optional clauses:

   *   The IN clause defines the DBEFileSet in which the section
       generated by the preprocessor for this command is stored.  If no
       IN clause is specified, file space in the SYSTEM DBEFileSet is
       used.

   *   The FOR UPDATE OF clause is used when you intend to use the UPDATE
       WHERE CURRENT command to update a current row.  This command may
       offer the simplest way to update a current row, but it imposes
       certain restrictions on the SELECT command.  Updating a current
       row is discussed fully later in this chapter under "Update Where
       Current."

The SELECT command for cursor declarations that do not include the FOR
UPDATE clause can consist of any of the SELECT command clause except the
INTO clause:

       SELECT SelectList 
         FROM TableNames 
        WHERE SearchCondition1 
     GROUP BY ColumnNames 
       HAVING SearchCondition2 
     ORDER BY ColumnIdentifiers 

A SELECT command associated with a cursor does not name output host
variables, but may name input host variables in the select list, the
WHERE clause, and the HAVING clause.  In the following example, the rows
qualifying for the query result will be those with a CountCycle matching
that specified by the user in input host variable CountCycle:

     EXEC SQL DECLARE Inventory
               CURSOR FOR
               SELECT PartNumber,
                      BinNumber,
                      QtyOnHand,
                      AdjustmentQty
                 FROM PurchDB.Inventory
                WHERE CountCycle = :CountCycle
             ORDER BY BinNumber;

When performing cursor processing, the ORDER BY clause may be useful.  In
the previous example, the rows in the query result will be in order by
ascending bin number to help the program user, who will be moving from
bin to bin, taking a physical inventory.

The DECLARE CURSOR command is actually a preprocessor directive.  When
the preprocessor parses this command, it stores a section in the target
DBEnvironment.  At run time, the section is not executed when the DECLARE
CURSOR command is encountered.  The section is executed when the OPEN
command is encountered.  Because the DECLARE CURSOR command is not
executed at run time, you do not need to perform error status checking in
your program following this command.

OPEN 

The OPEN command allocates internal buffer space and defines the active
set:

OPEN CursorName[KEEP CURSOR [[WITH LOCKS]]]
               [            [[NOLOCKS   ]]]
The following command opens the cursor defined earlier:

      EXEC SQL OPEN Inventory;

Once the active set is defined, the FETCH command will retrieve data from
it, one row at a time.

You can use the KEEP CURSOR WITH NOLOCKS option for a cursor that
involves sorting, whether through the use of a DISTINCT, GROUP BY, or
ORDER BY clause, or as the result of a union or a join operation.
However, for kept cursors involving sorting, ALLBASE/SQL does not ensure
data integrity.

It is your responsibility to ensure data integrity by verifying the
continued existence of a row before updating it or using it as the basis
for updating some other table.  For an updatable cursor, you can use
either the REFETCH or SELECT command to verify the continued existence of
a row.  For a cursor that is non-updatable, you must use the SELECT
command.

A warning (DBWARN 2056) regarding the kept cursor on a sort with no locks
is generated.  You must check for this warning if you want to detect the
execution of this type of cursor operation.

FETCH 

The FETCH command defines a current row and delivers the row into output
host variables:

     FETCH CursorName INTO OutputHostVariables 

Remember to include indicator variables when one or more columns in the
query result may contain a null value, for example: 

     EXEC SQL FETCH  Inventory
               INTO :PartNumber,
                    :BinNumber,
                    :QtyOnHand     :QtyOnHandInd,
                    :AdjustmentQty :AdjustmentQtyInd;

The first time you execute the FETCH command, the first row in the query
result becomes the current row.  With each subsequent execution of the
FETCH command, each succeeding row in the query result becomes the
current row.  After the last row in the query result has been fetched,
ALLBASE/SQL sets sqlca.sqlcode to 100.  ALLBASE/SQL also sets
sqlca.sqlcode to 100 if no rows qualify for the active set.  You should
test for an sqlca.sqlcode value of 100 after each execution of the FETCH
command to determine whether to re-execute the command.

     #define   TRUE    0
     #define   FALSE   1
       .
       .
       .
     int GetARow()
     {
     int  DoFetch;
     .
     .
     .
     DoFetch = TRUE;
     do {
     .
     .     The FETCH command appears here. 
     .
     switch (sqlca.sqlcode) {
       case 0:           DisplayRow();
                         break;
       case 100:         DoFetch = FALSE;
                         CloseCursor();
                         CommitWork();
                         break;
       default:          DoFetch = FALSE;
                         SQLStatusCheck();
                         CloseCursor();
                         RollBack();
                         break;
                         }
       } while (DoFetch == TRUE);
       }

When a row is current, you can update it by using the UPDATE WHERE
CURRENT command or delete it by using the DELETE WHERE CURRENT command.

UPDATE WHERE CURRENT 

This command can be used to update the current row when the SELECT
command associated with the cursor does not contain one of the following:

   *   DISTINCT clause in the select list.

   *   Aggregate function in the select list.

   *   FROM clause with more than one table.

   *   ORDER BY clause.

   *   GROUP BY clause.

The UPDATE WHERE CURRENT command identifies the active set to be updated
by naming the cursor and the column(s) to be updated:

     UPDATE TableName 
        SET ColumnName = ColumnValue [,...]
      WHERE CURRENT OF CursorName 

Any columns you name in this command must also have been named in a FOR
UPDATE clause in the related DECLARE CURSOR command, for example:

     EXEC SQL  DECLARE AdjustQtyOnHand 
               CURSOR FOR
               SELECT PartNumber,
                      BinNumber,
                      QtyOnHand,
                      AdjustmentQty
                 FROM PurchDB.Inventory
                WHERE QtyOnHand IS NOT NULL
                  AND AdjustmentQty IS NOT NULL
         FOR UPDATE OF QtyOnHand, 
                       AdjustmentQty; 

     EXEC SQL OPEN AdjustQtyOnHand;
     .
     In this case, the output host variables do not need to include
     indicator variables, because the SELECT command associated with the
     cursor eliminates any rows having null values from the active set. 
     .
     EXEC SQL  FETCH  AdjustQtyOnHand 
               INTO :PartNumber,
                    :BinNumber,
                    :QtyOnHand,
                    :AdjustmentQty;
     .
     .
     EXEC SQL  UPDATE PurchDB.Inventory
                 SET  QtyOnHand     = :QtyOnHand + :AdjustmentQty,
                      AdjustmentQty = 0
                WHERE CURRENT OF AdjustQtyOnHand; 

In the previous example, the order of the rows in the query result is not
important.  Therefore the SELECT command associated with the cursor
AdjustQtyOnHand does not need to contain an ORDER BY clause and the
UPDATE WHERE CURRENT command can be used.

In cases where order is important and the ORDER BY clause must be used,
you can use the UPDATE command with the WHERE clause to update values in
the current row, as well as other rows that qualify for the search
condition:

     EXEC SQL  DECLARE Inventory 
               CURSOR FOR
               SELECT PartNumber,
                      BinNumber,
                      QtyOnHand,
                      AdjustmentQty
                 FROM PurchDB.Inventory
                WHERE CountCycle = :CountCycle
             ORDER BY BinNumber;
     .
     .
     .
     EXEC SQL  FETCH  Inventory 
               INTO :PartNumber,
                    :BinNumber,
                    :QtyOnHand     :QtyOnHandInd
                    :AdjustmentQty :AdjustmentQtyInd;
     .
     The program displays the current row.  If the QtyOnHand value is not null,
     the program prompts the user for an adjustment quantity.  Adjustment
     quantity is the difference between the quantity actually in the bin and the
     QtyOnHand in the row displayed.  If the QtyOnHand value is null, the program
     prompts the user for both QtyOnHand and AdjustmentQty.  Any value entered is
     used in the following UPDATE command. 
     .
     .
     EXEC SQL  UPDATE PurchDB.Inventory
                 SET QtyOnHand =     :QtyOnHand :QtyOnHandInd,
                     AdjustmentQty = :AdjustmentQty :AdjustmentQtyInd
                WHERE PartNumber = :PartNumber
                 AND BinNumber  = :BinNumber;

After either the UPDATE WHERE CURRENT or the UPDATE command is executed,
the current row remains the same until the FETCH command is re-executed.

If you want to execute UPDATE commands inside the FETCH loop, remember
that more than one row in the active set may qualify for the UPDATE
operation, as when the WHERE clause in the the UPDATE command does not
specify a unique key.  When more than one row qualifies for the UPDATE,
you may not see a changed row unless you CLOSE and re-OPEN the cursor.
To avoid this problem, either ensure that your UPDATE commands change
only one row (the current row) or perform the UPDATE operations outside
the FETCH loop.

DELETE WHERE CURRENT 

This command can be used to delete the current row when the SELECT
command associated with the cursor does not contain one of the following:

   *   DISTINCT clause in the select list.

   *   Aggregate function in the select list.

   *   FROM clause with more than one table.

   *   ORDER BY clause.

   *   GROUP BY clause.

The DELETE WHERE CURRENT command has a very simple structure:

     DELETE FROM TableName 
           WHERE CURRENT OF CursorName 

The DELETE WHERE CURRENT command can be used in conjunction with a cursor
declared with or without the FOR UPDATE clause:

     The program displays the current row and asks the user whether to update or
     delete it.  If the user wants to delete the row, the following command
     is executed. 

     EXEC SQL  DELETE FROM PurchDB.Inventory
                WHERE CURRENT OF AdjustQtyOnHand;

Even though the SELECT command associated with cursor Inventory names
only some of the columns in table PurchDB.Inventory, the entire current
row is deleted.

After the DELETE WHERE CURRENT command is executed, there is no current
row.  You must re-execute the FETCH command to obtain another current
row.

As with the UPDATE WHERE CURRENT command, if the SELECT command
associated with the cursor contains an ORDER BY clause or other
components listed earlier, you can use the DELETE command with the WHERE
clause to delete a row:

     EXEC SQL  DELETE FROM PurchDB.Inventory
                     WHERE PartNumber = :PartNumber
                      AND BinNumber  = :BinNumber;

If you use the DELETE command to delete a row while using a cursor to
examine an active set, remember that more than one row will be deleted if
multiple rows satisfy the conditions specified in the WHERE clause of the
DELETE command.  In addition, the row that is current when the DELETE
command is executed remains the current row until the FETCH command is
re-executed.

CLOSE 

When you no longer want to operate on the active set, you use the CLOSE
command:

     CLOSE CursorName 

The CLOSE command frees up ALLBASE/SQL internal buffers used to handle
cursor operations.  This command does not release any locks obtained
since the cursor was opened; to release locks, you must terminate the
transaction with a COMMIT WORK or a ROLLBACK WORK:

     The program opens a cursor and operates on the active set.  After the last
     row has been operated on, the cursor is closed. 

     EXEC SQL CLOSE Inventory;

     Additional SQL commands are executed, then the transaction is terminated. 

     EXEC SQL COMMIT WORK;

You also use the CLOSE command when you want to re-access the active set.
In this case, simply re-open the cursor after executing the CLOSE
command.  Because locks have not been released, any changes to the rows
in the active set will be those made by your program since the cursor was
first opened:

     Cursor Inventory is used to update information
     in table PurchDB.Inventory.  After the last row
     in the active set has been fetched and its information
     changed, the cursor is closed. 

     EXEC SQL CLOSE Inventory;

     The cursor is then re-opened to allow the program
     user to review the information and optionally make
     last-minute adjustments. 

     EXEC SQL OPEN Inventory;

     After the user has reviewed all rows in the active
     set, any changes made to the active set are
     made permanent as follows. 

     EXEC SQL COMMIT WORK;

When a transaction terminates, any cursors opened during that transaction
are automatically closed unless you are using the KEEP CURSOR option of
the OPEN command.  To avoid possible confusion, it is good programming
practice to always use the CLOSE command followed by COMMIT WORK to
explicitly close any open cursors before ending a transaction.



MPE/iX 5.0 Documentation