Positions Frame
The Positions frame displays the Positions table (or a user‑specified subset) in a table field format. Users can view and update records from the table.
When records are loaded into the table field, the position code is displayed in both the "Old PosCode" and "New PosCode" table field columns. Users cannot modify the old position code; it is displayed to help users keep track of the changes that occur when they select Save (see the section Menu Operations).
The following figure shows the form for the Positions frame:
The form components have the following names:
• The table field is named "tf"
• The table field columns are named "old_position_code," "position_code," "position_title," "merge," "last_updated_at," "last_updated_by," and a hidden field, "position_description."
Menu Operations
The Positions frame provides the following menu operations:
The following operations are available after a user has selected Go or AppendMode:
Deleting Records from the Positions Table
Users cannot use the RowDelete operation to delete a table field row that was loaded from the database. A user can take either of the following actions to delete a record from the Positions table:
• Enter blanks into the "New PosCode" table field column and select Save. The record then is deleted from the Positions table if there are no records in the Employee_ histories table that contain the same position code. If there are any such records, the Save fails.
This validation is necessary to ensure referential integrity. If the Save does fail, users must use the method described immediately below.
• "Merge" the record as follows:
d. Change the value in the "New PosCode" column to another valid position code in the Positions table.
e. Enter y into the "M" (merge) column.
When the user selects Save, the record is deleted from the Positions table. All records in the Employee_ histories table that have this position code are updated to contain the value entered into the "New PosCode" column.
To ensure referential integrity, the Save operation fails if there is no record in the Positions table with the value that the user has entered into the "New PosCode" column.
Updating Records in the Positions Table
Users can use the Positions frame to update records in the Positions table as follows:
• Changing a position description by entering a new value into the "position_description" table field column and selecting Save
• Changing the position code by entering a new value into the "position_code" table field column and selecting Save, leaving the "M" blank (or entering n)
In this case, the application takes the following actions:
– Ensuring referential integrity by updating all records in the Employee_histories table that contain the position code that was changed
– Rejecting the change if the value entered for the new position code already exists in the Positions table (because position code is a unique key for the table)
Inserting Records into the Positions Table
Users can use the Positions frame to insert records into the Positions table in either of the following ways:
• By selecting InsertRow and typing data into the resulting blank row
• By scrolling down to the blank row below the last row displayed and typing in new data
Users can insert records in any order.
Calling the Descriptions Frame
Users can select MoreInfo to call the Descriptions frame to view and edit the position description field.
The MoreInfo operation passes the position_description information for the current row by reference to the Descriptions frame. The MoreInfo operation also passes the position_code, position_title, last_updated_at, and last_updated_by fields for the current row to simple read-only fields on the Descriptions form.
The Descriptions frame allows users to manipulate the information in the position_description field by importing it from or exporting it to an ASCII file. Any changes made to the position_description field are passed back to the Positions frame. However, the position_description field is not changed in the database until the record is saved.
See
Descriptions Frame for more information.
4GL Source Code
The following is the complete 4GL source code file for the Positions frame. The code for the frame's local procedures follows the basic frame processing code.
/*
** Frame: positions
** Form: samp_positions
** Source File: positions.osq
** Description:
**
** Allows the user to browse and update the Positions table.
** The table is presented to the user in tablefield form.
**
** The user is allowed to do arbitrary inserts, updates, and
** deletes as long as referential integrity is maintained
** (position_code appears as a foreign key in the
** employee_histories table). The user can insert a record by
** selecting InsertRow and typing data into the resulting
** blank row, or by scrolling down to the blank row below
** the last row displayed and typing in new data.
** Records can be inserted in any order.
** The user can delete a record by blanking out its position
** code (in the column labelled New PosCode).
** The user can update any fields in the positions table
** except for the last_updated_at (time) and last_updated_by
** (user) columns, which are maintained by code in this frame.
**
** The last_updated_at column is used to implement an
** optimistic locking scheme: If two users simultaneously
** manipulate a record in the Positions table, the changes
** done by the first user to select Save are written to the
** database; the second user is informed that his changes
** didn't take.
**
** If the user changes the position_code in a record in the
** Positions table, all records in the employee_histories
** table with the same position_code are automatically
** updated. The last_updated_at and last_updated_by
** columns of the corresponding master record (in the
** employees table) are also updated.
**
** A special mechanism is available to "merge" two records in
** the positions table. Merging record A into record B
** entails the following:
** (1) the record in the positions table with position_code A
** is deleted, and
** (2) any records in the employee_histories table with
** position_code A have their position_code changed to B
** (which must already exist in the positions table).
** The user specifies that record A is to be merged into B
** by selecting A into the tablefield, changing the
** "New PosCode" column from A to B, and setting the "M"
** column to 'y'.
**
** There is one hidden field "position_description" in the
** tablefield. Information contained in this field will be
** saved along with the rest of the tablefield to the database.
** The user must select "MoreInfo" (which puts the user into
** the description frame) to view or access the
** position_description information. Any changes made by the user
** in the description frame will be saved on the position frame.
*/
initialize =
declare
/* working variables*/
char1 = char(1) not null, /* holds answer to
** yes/no prompts */
error_no = integer not null, /* holds DBMS
** statement error no. */
i = integer not null, /* general purpose integer */
i_descript = integer not null, /* holds change state of
** description */
row_count = integer not null, /* holds DBMS
** statement row count */
row_number = integer not null, /* holds table field
** row number */
row_state = integer not null, /* holds table field
** row state */
/* local procedures */
display_submenu = procedure returning none,
check_io_err = procedure returning integer not null,
begin
set_forms frs (validate(nextfield) = 1,
validate(previousfield) = 1,
activate(nextfield) = 1, activate(previousfield) = 1,
activate(menuitem) = 1, activate(keys) = 1,
getmessages = 0);
/* query mode required for qualification function */
set_forms form (mode = 'query');
set_forms field samp_positions (mode(tf) = 'query');
set_forms column samp_positions tf (displayonly(merge) = 1);
set autocommit off;
/* If the application has not already done so, ask the
** DBMS for the name of the user running the application,
** and save the name in a global variable.
*/
if (user_name = '') then
select user_name = dbmsinfo('username');
commit work;
endif;
end
'Go' (explanation = 'Run query'), key frskey4 =
begin
message 'Selecting data . . .';
/* selecting to table field with qualification function
** changes table field mode to Update, even if 0 rows
** were selected.
*/
/*# begin Select\Master */
samp_positions.tf := select
old_position_code = m.position_code,
position_code = m.position_code,
position_title= m.position_title,
position_description = m.position_description,
merge = ' ', last_updated_at = m.last_updated_at,
last_updated_by = m.last_updated_by
from positions m
where
qualification(m.position_code = tf.position_code,
m.position_title = tf.position_title)
order by position_code asc;
/*# END Select\Master */
inquire_sql (row_count = rowcount);
if (row_count <= 0) then
rollback work;
set_forms field samp_positions (mode(tf) = 'query');
set_forms column samp_positions tf
(displayonly(merge) = 1);
message 'No rows retrieved';
sleep 2;
resume;
endif;
/* Assertion: rows selected above */
commit work; /* release locks */
callproc display_submenu(menu_mode = 'u');
end
'AppendMode' (validate = 0, activate = 0,
explanation = 'Display submenu for Appending new data') =
begin
callproc display_submenu(menu_mode = 'a');
end
'Clear' (validate = 0, activate = 0,
explanation = 'Clear all fields') =
begin
clear field all;
end
'ListChoices' (validate = 0, activate = 0,
explanation = 'Show valid values for current field'),
key frskey10 (validate = 0, activate = 0) =
begin
i = callproc help_field;
end
'Help' (validate = 0, activate = 0,
explanation = 'Display help for this frame'),
key frskey1 (validate = 0, activate = 0) =
begin
helpfile 'positions table'
'/m/corgi/supp60/employee/positions.hlq';
end
'End' (validate = 0, activate = 0,
explanation = 'Return to previous frame'),
key frskey3 (validate = 0, activate = 0) =
begin
return;
end
/*
** Local procedure: display_submenu
**
** Description:
** Displays a submenu suitable for either append mode or
** update mode.
**
** Input parameter:
** menu_mode 'a' (append) or 'u' (update).
**
** Returns: none.
*/
procedure display_submenu (
menu_mode = char(1), /* a/u: append mode or
** update mode? */
) =
declare
delete_position = char(1), /* y/n: delete record
** from Positions? */
update_position = char(1), /* y/n: update record
** in Positions? */
verify_position = char(1), /* y/n: check to make
** sure the record in
** Positions being
** merged into really
** exists */
update_employees = char(1), /* y/n: update records
** in Employees and
** Employee_histories
** to reflect change
** in position_code?
*/
verify_no_employees = char(1), /* y/n: check to make
** sure there are
** no records in
** Employee_histories
** with the position
** code being deleted*/
begin
set_forms form (change = 0); /* typing query
** qualification
** set change = 1 */
set_forms form (mode = 'update');
/* If table field in query mode (as it will be for an
** 'Append'), then the following statement will clear it */
set_forms field samp_positions (mode(tf) = 'fill');
set_forms column samp_positions tf (displayonly(merge) = 0);
display submenu
begin
'RowDelete' (validate = 0, activate = 0,
explanation = 'Delete current row from table field') =
begin
if (tf.old_position_code is null) then
deleterow tf;
set_forms form (change = 1);
else
message 'You can RowDelete only those rows that'
+ ' you have inserted (with RowInsert)'
+ ' and haven''t saved yet.'
+ ' To delete a record that''s already in'
+ ' the database, blank out its'
+ ' New PosCode field, or merge it with'
+ ' another record by setting its M field'
+ ' to "y" and changing its New PosCode'
+ ' to the position code of the other record.'
with style = popup;
endif;
end
'RowInsert' (explanation = 'Open new row in table field') =
begin
validrow tf; /* error if current row invalid */
inquire_forms table '' (i = rowno);
insertrow tf[i-1] (_state = 0);
end
'ReSelect' (explanation =
'Refresh current row in tablefield from database') =
begin
if (tf.old_position_code is null) then
message 'You can ReSelect only those rows that'
+ ' were retrieved from the database.'
with style = popup;
resume;
endif;
/* Re-select the record from the database.
** If the database record has been deleted,
** delete the tablefield row.
*/
repeated select
:tf.position_code = m.position_code,
:tf.position_title = m.position_title,
:tf.position_description = m.position_description,
:tf.merge = ' ',
:tf.last_updated_at = m.last_updated_at,
:tf.last_updated_by = m.last_updated_by
from positions m
where m.position_code = :tf.old_position_code;
inquire_sql (row_count = rowcount);
if (row_count <= 0) then
deleterow tf;
set_forms form (change = 1);
endif;
commit work; /* Release locks */
end
'MoreInfo' (explanation =
'Access the job description information') =
begin
/* pass the current row of the tablefield to simple fields
on the description frame. The only field that is not
display-only field is the position_description field, so
this is passed using byref. Any changes made to this field
will be passed back to the calling frame. */
i = callframe descriptions (
samp_descriptions.position_code := iitf[].position_code,
samp_descriptions.position_title := iitf[].position_title,
samp_descriptions.last_updated_at := iitf[].last_updated_at,
samp_descriptions.last_updated_by := iitf[].last_updated_by,
position_description :=
byref(iitf[].position_description));
/* If the user has made changes on the description form, mark
the position_description field changed
A status = 1 means that the position_description field
was changed in the descriptions frame */
if i = 1 then
SET_FORMS ROW '' iitf
(CHANGE (position_description) = 1);
SET_FORMS FORM (CHANGE = 1);
endif;
end;
'Save' (activate = 1,
explanation = 'Update database with current screen data'),
key frskey8 (activate = 1) =
begin
inquire_forms form (i = change);
if (i = 1) then
validate; /* validate all fields on form */
message 'Saving changes . . .';
/* process all rows */
unloadtable tf (row_state = _state,
row_number = _record)
begin
/* Check if the position_description field has been
** changed, since this value will be passed from the
** descriptions form using byref, the row_state will
** be unchanged */
INQUIRE_FORMS ROW samp_positions iitf
(i_descript = CHANGE(position_description));
IF (row_state = 1 and i_descript = 0) THEN /* new */
if (tf.position_code is not null) then
repeated insert
into positions(position_code, position_title
position_description,
last_updated_at, last_updated_by)
values(tf.position_code, tf.position_title,
tf.position_description,
'now', user_name);
if (check_io_err() != 0) then
scroll tf to :row_number;
row_number = 0;
resume field tf;
endif;
endif;
elseif (row_state = 3 or i_descript = 1) then
/* changed */
/* The tablefield row has been marked as
** 'changed'. First, analyze which tables
** need records updated, deleted, or
** checked (for [non]existence). It's
** possible that no tables need to be
** changed: if the user changed a
** tablefield row and then did a ReSelect
** into it and made no further
** change, row_state will be 3.
*/
verify_position = 'n';
delete_position = 'n';
update_position = 'n';
update_employees = 'n';
verify_no_employees = 'n';
if (tf.position_code is null) then
delete_position = 'y';
verify_no_employees = 'y';
elseif (tf.position_code !=
tf.old_position_code) then
if (uppercase(tf.merge) = 'Y') then
verify_position = 'y';
delete_position = 'y';
update_employees = 'y';
else
update_position = 'y';
update_employees = 'y';
endif;
else
inquire_forms row samp_positions tf
(i = change(position_title));
if (i = 1 or i_descript = 1) then
update_position = 'y';
endif;
endif;
/* If merging a position into a second
** position, verify that the second
** position exists. The Select puts a
** shared lock on the second position,
** so no other transaction can delete
** it while Employees is changed
** to the new position.
*/
if (verify_position = 'y') then
repeated select i = 0 from positions
where position_code = :tf.position_code;
inquire_sql (row_count = rowcount);
if (row_count <= 0) then
rollback work;
message 'The "Save" operation was'
+ ' not performed, because you'
+ ' are attempting to merge a'
+ ' record with a second'
+ ' record that does not'
+ ' exist. The cursor will be'
+ ' placed on the record.'
with style = popup;
scroll tf to :row_number;
row_number = 0;
resume field tf;
endif;
endif;
/* Now, try to delete or update the Positions
** record represented by the tablefield row, as
** required. The Update or Delete places an
** exclusive lock on the Positions record.
** This prevents any Insert or Update to the
** Employee_histories table from adding a record
** by another user that specifies the
** position_code of the Positions record until
** the current user does a commit, since the Insert
** and Update in the Employees frame have a WHERE
** clause that refers to the position_code in the
** Positions table. This is an important
** consideration when deleting the Positions
** record or updating its key (position_code).
*/
if (delete_position = 'y') then
/* Delete row using old key in WHERE clause. */
repeated delete from positions
where position_code = :tf.old_position_code
and last_updated_at = :tf.last_updated_at;
elseif (update_position = 'y') then
/* Update row using old key field in WHERE clause. */
repeated update positions
set position_code = :tf.position_code,
position_title = :tf.position_title,
position_description =
:tf.position_description,
last_updated_at = 'now',
last_updated_by = :user_name
where position_code = :tf.old_position_code
and last_updated_at = :tf.last_updated_at;
endif;
/* If trying to delete or update the positions
** record, see if successful.
*/
if (delete_position = 'y') or
(update_position = 'y') then
if (check_io_err() != 0) then
scroll tf to :row_number;
row_number = 0;
resume field tf;
endif;
inquire_sql (row_count = rowcount);
if (row_count <= 0) then
rollback work;
message 'The "Save" operation was not'
+ ' performed, because you are'
+ ' attempting to update or'
+ ' delete a record that has been'
+ ' updated or deleted by another'
+ ' user since you selected the'
+ ' record. The cursor will be'
+ ' placed on the record. You may'
+ ' wish to "ReSelect" the record'
+ ' to see what changes were made.'
with style = popup;
scroll tf to :row_number;
row_number = 0;
resume field tf;
endif;
endif;
/* If deleting a position, verify that it doesn't
** occur in any records in Employee_histories.
*/
if (verify_no_employees = 'y') then
repeated select i = 0 from employee_histories
where position_code = :tf.old_position_code;
inquire_sql (row_count = rowcount);
if (row_count >= 0) then
rollback work;
message 'The "Save" operation was not'
+ ' performed, because you are'
+ ' attempting to delete a record'
+ ' whose position_code appears in'
+ ' records in the employee_histories'
+ ' table. The cursor will be'
+ ' placed on the record. You may'
+ ' wish to merge this Position'
+ ' record with another record by'
+ ' setting its M field to "y" and'
+ ' changing its New PosCode to the'
+ ' position code of the other record.'
with style = popup;
scroll tf to :row_number;
row_number = 0;
resume field tf;
endif;
endif;
/* If the user changes a position_code in a Positions
** record or merges one Positions record into another,
** change all occurrences of the position_code
** in the Employee_histories table.
** To adhere to proper optimistic locking protocol,
** first update the last_updated_at and last_updated_by
** columns in the corresponding master records
** in the Employees table. Thus, if a second user is
** currently working on one of the employees whose
** history is about to be updated by the current user,
** any attempt by the second user to save changes to
** the employee is flagged as an error.
*/
if (update_employees = 'y') then
repeated update employees m
from employee_histories d
set last_updated_at = 'now',
last_updated_by = :user_name
where m.ssn = d.ssn
and d.position_code = :tf.old_position_code;
if (check_io_err() != 0) then
scroll tf to :row_number;
row_number = 0;
resume field tf;
endif;
repeated update employee_histories
set position_code = :tf.position_code
where position_code = :tf.old_position_code;
if (check_io_err() != 0) then
scroll tf to :row_number;
row_number = 0;
resume field tf;
endif;
endif;
endif;
end; /* end of unloadtable */
row_number = 0;
commit work;
if (check_io_err() != 0) then
resume;
endif;
endloop;
else
message 'no changes to Save.' with style = popup;
endif;
end /* end of 'Save' menuitem */
'ListChoices' (validate = 0, activate = 0,
explanation = 'Show valid values for current field'),
key frskey10 (validate = 0, activate = 0) =
begin
i = callproc help_field;
if (i > 0) then
/* value was selected onto form */
set_forms form (change = 1);
endif;
end
key frskey5 (explanation = 'scroll to top of table field') =
begin
scroll tf to 1;
end
key frskey6 (explanation = 'scroll to bottom of table field') =
begin
scroll tf to end;
end
key frskey7
(explanation = 'search table field for a specified value') =
begin
i = callproc find_record;
end
'Help' (validate = 0, activate = 0,
explanation = 'display help for this frame'),
key frskey1 (validate = 0, activate = 0) =
begin
if (menu_mode = 'a') then
helpfile 'positions table'
'/m/corgi/supp60/employee/positions.hla';
else
helpfile 'positions table'
'/m/corgi/supp60/employee/positions.hlp';
endif;
end
'End' (validate = 0, activate = 0,
explanation =
'Return from Update or Append mode to Query mode'),
key frskey3 (validate = 0, activate = 0) =
begin
inquire_forms form (i = change);
if (i = 1) then
char1 = callproc confirm (
question = 'Do you wish to "End" without'
+ ' saving changes?',
no = 'Cancel the "End" operation. (You can then'
+'save your changes by selecting Save).',
yes = '"End" without saving changes.'
);
if (char1 = 'n') then
resume menu;
endif;
endif;
endloop; /* exit submenu */
end
end; /* end of submenu in 'Go' menuitem */
set_forms form (mode = 'query'); /* so user can enter
** another query */
set_forms field samp_positions (mode(tf) = 'query');
set_forms column samp_positions tf (displayonly(merge) = 1);
end
/*
** Local procedure: check_io_err
**
** Description:
** Checks to see if the last database I/O statement
** executed properly; does a ROLLBACK and puts out an
** error message if not. If an error *has* occurred,
** the DBMS has already issued its own error message.
**
** Returns:
** The error number generated by the
** database I/O statement (0 if no error).
*/
procedure check_io_err =
declare
cursor_msg = varchar(80) not null,
correct_msg = varchar(80) not null,
begin
inquire_sql (error_no = dbmserror);
if (error_no = 0) then
return 0;
endif;
if (row_number <= 0) then
cursor_msg = '';
else
cursor_msg = ' The cursor will be placed on the row'
+ ' where the error occurred.';
endif;
if (error_no = 4700) then /* deadlock (DBMS has already
** done ROLLBACK) */
correct_msg = ''; /* Nothing to fix on a
** deadlock; just retry */
else
correct_msg = ' correct the error (if possible) and';
rollback work;
endif;
message 'The "Save" operation was not performed, due to'
+ ' the error described in the previous message.'
+ cursor_msg
+ ' Please' + correct_msg + ' select "Save" again.'
with style = popup;
return error_no;
end
