HP 3000 Manuals HP 3000 Manuals
Deleting Records
To delete a record from an HP ALLBASE/SQL base table you can use the FILE
*DELETE command in an HP ALLBASE/4GL logic block, or the SQL DELETE or
DELETE WHERE CURRENT command in an SQL logic block.
FILE *DELETE Command
To delete a row from a base table, the FILE *DELETE command has the
format:
FILE *DELETE file_name
where file_name is the HP ALLBASE/4GL dictionary name of a base table.
To use this command, you must declare and open a cursor with a SELECT
command in an SQL logic block earlier in the same HP ALLBASE/4GL process,
and position the cursor with a FILE *NEXT command.
The FILE *DELETE command performs the same function as the following SQL
command:
DELETE FROM table_name WHERE CURRENT OF cursor_name;
The FILE *DELETE command deletes the table row identified by the current
cursor position. After the row has been deleted, the cursor is
positioned between the preceding and following rows of the active set.
To reposition the cursor for further deletions or updates, use the FILE
*NEXT command.
Example 6 - Using the FILE *DELETE Command
This example shows the use of the FILE *DELETE command to delete records
from a base table.
Function del_items
1 SQL find_items
2 FILE *NEXT parts; ENTER 8
3 SHOW *REFRESH
4 MESSAGE confirm_del
5 IF V-confirm <> "Y" MESSAGE no_delete; ENTER 2
6 FILE *DELETE parts; ENTER 12
7 ENTER 2
8 IF *IOSTATUS <> N-eof ENTER 12
9 MESSAGE all_done
10 SQL commit
11 EXIT
12 MESSAGE file_error
13 EXIT
SQL Logic Block find_items
SELECT :parts FROM Purchdb.parts
WHERE part_no BETWEEN :V-first_no AND :V-last_no;
The first command in the function del_items calls the SQL logic block
find_items. This SQL logic block opens a cursor on the base table
Purchdb.parts. The FILE *NEXT command then positions the cursor on the
first record to be deleted, and the SHOW *REFRESH command displays the
record contents on the screens.
If the user enters Y in response to the message, the FILE *DELETE command
in step 6 deletes the record, and control returns to step 2 to retrieve
the next record.
After the last selected record has been deleted, the FILE *NEXT command
encounters the end of file, and returns an error condition. Control then
passes to step 8 which tests the error return in the communication area
field *IOSTATUS. If the value in *IOSTATUS is the same as the value of
the numeric constant N-eof (60110, end of file), control passes to step
9. This step displays a message to confirm the deletions, and the
following SQL command commits the changes.
Any other file error condition passes control to step 12 to display an
error message, and exit from the function without committing the changes.
The SQL logic block find_items contains a SELECT command. This command
selects all records with a value in the part_no field that is between the
values contained in the HP ALLBASE/4GL variables V-first_no and
V-last_no. In this SQL logic block, parts is the HP ALLBASE/4GL name of
a base table, and Purchdb.parts is the external name for the table.
Deleting via a Cursor
You can use the SQL DELETE WHERE CURRENT command in an SQL logic block to
delete a row from a base table via a cursor. The command has the format:
DELETE FROM table_name WHERE CURRENT OF cursor_name;
In this command, table_name is the external name of the HP ALLBASE/SQL
table. You can use a fully qualified name of the form owner.table_name.
The term cursor_name is the name of the SQL logic block containing the
SQL SELECT command that defined the cursor. You must execute the SELECT
command to open the cursor, and use a FILE *NEXT command in an HP
ALLBASE/4GL logic block to position the cursor before you can delete the
row.
Example 7 - Deleting HP ALLBASE/SQL Records via a Cursor
This example shows the use of the SQL DELETE command in an SQL logic
block.
Process del_order_items
1 MODE *WRITE ord_item
.
.
SQL select_items
FILE *NEXT ord_item
.
.
SQL delete_item
.
.
.
SQL Logic Block select_items
SELECT :ord_item FROM Purchdb.ord_item
WHERE order_no = :F-order_no.ord_item;
SQL Logic Block delete_item
DELETE FROM Purchdb.ord_item
WHERE CURRENT OF select_items;
This HP ALLBASE/4GL process calls two SQL logic blocks. The SQL logic
block select_items opens a cursor on the base table ord_item. The FILE
*NEXT command in the HP ALLBASE/4GL logic block positions the cursor on
the first record in the active set for the cursor.
The SQL logic block delete_item deletes the current record. Note that
you must specify the cursor name as the name of the SQL logic block
containing the SELECT command that defines the cursor. This SQL DELETE
command is equivalent to the HP ALLBASE/4GL command:
FILE *DELETE ord_item
After the delete, the cursor is positioned between the preceding row and
the following row in the table. You can use a further FILE *NEXT command
to position the cursor on the next row of the active set. You must also
use an explicit COMMIT WORK command in an SQL logic block to terminate
the SQL transaction.
Deleting Without a Cursor.
You can use the SQL DELETE command in an SQL logic block to delete a row,
or a number of rows, from an HP ALLBASE/SQL base table. The command has
the format:
DELETE FROM table_name [WHERE search_condition]
In this expression, table_name is the external name of the table. You
can use a fully qualified table name of the form owner.table_name in this
command.
Example 8 - Deleting Without a Cursor
This example shows the use of an SQL DELETE command in an SQL logic
block.
SQL Logic Block del_old_orders
DELETE from purchdb.orders
WHERE order_number < :V-orders_old;
This SQL logic block deletes all records from the base table
purchdb.orders where the value of the field order_number is less than the
current value in the HP ALLBASE/4GL variable orders_old.