HP 3000 Manuals HP 3000 Manuals
Updating HP ALLBASE/SQL Data
To update an existing record in a base table, you must use the HP
ALLBASE/SQL UPDATE command in an SQL logic block. You can perform the
update via an SQL cursor, or use the UPDATE command without a cursor.
Updating Via a Cursor
To update a base table via a cursor, the UPDATE command must be in the
following format:
UPDATE table_name SET column_name = value [,...]
WHERE CURRENT OF cursor_name;
In this expression, table_name is the external name of an HP ALLBASE/SQL
base table. The term value is an HP ALLBASE/4GL item containing the new
value to be inserted in the column to be updated. It can be any of the
following:
* A literal enclosed in single quotes (').
* A file record field reference.
* A work area field reference.
* A screen field name.
* A variable or calculated item.
* A numeric or alphanumeric constant.
The term cursor_name is the HP ALLBASE/4GL name of the SQL logic block
containing the SELECT command that defined the current cursor.
To use this form of the UPDATE command, you must execute a SELECT command
in an SQL logic block earlier in the same process to define and open an
SQL cursor, and then execute a FILE *NEXT command in an HP ALLBASE/4GL
logic block to position the cursor.
Example 4 - Updating HP ALLBASE/SQL Data via a Cursor
This example shows one way of updating records in an HP ALLBASE/SQL base
table. The HP ALLBASE/4GL function and SQL logic blocks shown here
operate under the process emp_update. This process firstly displays the
screen mod_employee, then updates the base table empgrp.employee
according to the data entered by the user.
Process emp_updt
1 MODE *WRITE employee
2 SCREEN mod_employee
3 SQL upd_employee; ENTER 6
4 SQL commit
5 ENTER 2
6 MESSAGE upd_error
7 SQL roll_back
8 EXIT
Function mod_emp
1 SQL sel_employee
2 FILE *NEXT employee; ENTER 5
3 SHOW *REFRESH
4 EXIT
5 IF *IOSTATUS <> N-eof THEN ENTER 10
6 MESSAGE no_rec
7 FILE *BUFFER employee
8 CLEAR *MAP S-emp_name S-spouse
9 EXIT
10 VISIT db_error
11 EXIT
SQL Logic Block sel_employee
SELECT :employee FROM empgrp.employee
WHERE emp_no = :S-emp_no.mod_employee
FOR UPDATE OF emp_name, address, spouse;
SQL Logic Block upd_employee
UPDATE empgrp.employee SET
emp_name = :F-emp_name.employee,
address = :F-address.employee,
spouse = :F-spouse.employee
WHERE CURRENT OF sel_employee;
The function mod_emp, which is called as an after function on an employee
number field on the screen mod_employee, calls the SQL logic block
sel_employee and then executes a FILE *NEXT command. These two commands
declare and open a cursor on the table empgrp.employee and retrieve the
first record in the active set for the cursor. If a record is retrieved
successfully, the SHOW *REFRESH command displays the record on the
screen, and the function exits. If the FILE command does not retrieve a
record, control passes to step 5 which tests the value in *IOSTATUS. If
the SELECT command does not retrieve a record, the FILE command
encounters an end of file condition without retrieving a record. In this
case the *IOSTATUS value is 60110, (the value of the numeric constant
N-eof), and a suitable message is displayed. The function then clears
the file record buffer and the screen to allow the user to enter the
details for a new record.
If the FILE command fails for any reason other than reaching the end of
file, control passes to step 10. This step executes a further function
db_error. (This function is not shown in this example.) Typically, such
a function would use the SQLEXPLAIN command in a SQL logic block to
determine the cause of the error, and then take the appropriate actions
to rectify the situation.
The SQL logic block sel_employee contains a single SELECT command that
opens a cursor on the table empgrp.employee, where employee is the HP
ALLBASE/4GL name of an HP ALLBASE/SQL base table, and empgrp.employee is
the external name of an existing base table. This SELECT command finds a
record with the value in the field emp_no that matches the current value
in the emp_no field on the screen mod_employee. Note that the SELECT
command must include the FOR UPDATE OF clause to specify the columns of
the table that are to be updated.
When the user exits from the screen by pressing the Commit Data
function key, control returns to the process emp_updt. The process
immediately calls the SQL logic block upd_employee to update the current
record in the base table. If the update is successful, control passes to
the SQL logic block commit. This logic block must contain an SQL COMMIT
WORK command. If an error occurs during the update, control passes to
step 6 which displays an error message. The SQL logic block roll_back
then reverses the incomplete SQL transaction. This SQL logic block would
contain an SQL ROLLBACK WORK command.
The SQL logic block upd_employee contains an SQL UPDATE command. This
command must specify the new values for the columns in the base table row
to be updated. It must also contain the WHERE CURRENT OF clause to
specify the row to be updated. Note that the cursor name for this form
of the UPDATE command is the name of the SQL logic block that contains
the SELECT command that declares and opens the cursor. In this example
the cursor name is sel_employee.
Updating Without a Cursor
You can update a base table without using a cursor by using the UPDATE
command with an SQL WHERE search_condition clause in an SQL logic block.
The UPDATE command has the format:
UPDATE table_name SET column_name = value [,...]
WHERE search_condition;
In this expression, table_name is the external name of the table. You
can use a fully qualified name of the form owner.table_name in this
command.
Example 5 - Updating HP ALLBASE/SQL Data Without a Cursor
This example demonstrates the procedure to update an HP ALLBASE/SQL table
without using a cursor.
SQL Block update
UPDATE debtors
SET extend_credit = 'NO'
WHERE days_overdue >= :V-days_late;
COMMIT WORK;
This SQL logic block contains a single SQL UPDATE command that sets the
extend_credit field to "NO" for each record where the value in the field
days_overdue is equal to or greater than the value in the HP ALLBASE/4GL
variable days_late.