Re: Restricted Fields in Forms 3.0

In article <CqEJsE.A24_at_uk.ac.brookes> p0070621_at_oxford-brookes.ac.uk (Tommy Wareing) writes:
>From: p0070621_at_oxford-brookes.ac.uk (Tommy Wareing)
>Subject: Re: Restricted Fields in Forms 3.0
>Date: Thu, 26 May 1994 09:02:38 GMT
>Dwayne K. Lanclos (lanclosd_at_bcstec.ca.boeing.com) wrote:
>> We've got a case here where we want to restrict certain fields on a form
>> to be updateable by only two special (administrative) users. All the other
>> fields in the table can be changed by regular users. The DBA restricted
>> these fields in the Oracle database, but then the regular users couldn't
>> update the form.
 

>> We also tried changing the field attributes to "not updateable"
>> on-the-fly, but that didn't seem to work, either. We don't want to
>> create a special form just for the two administrative users. What
>> should we do?
>
>My normal method is to have a pre-record trigger of:
>IF condition THEN
> set_field('field_name', ENTERABLE, ATTR_ON);
> set_field('field_name', UPDATEABLE, ATTR_ON);
>ELSE
> set_field('field_name', UPDATEABLE, ATTR_OFF);
> set_field('field_name', ENTERABLE, ATTR_OFF);
>END IF;
>
>I do it like this, since I frequently end up with some fields enterable
>dependant on other fields in the same record. If you want to set fields
>depending on the user, then a pre-form trigger might work, but it might
>only affect the first record (I've not tried it).
>
>Note that setting a field ENTERABLE on a new record removes the ability
>to actually alter the data in it, hence the use of UPDATEABLE above.
>If the field is not supposed to be updateable on existing records, then
>add a check on system.record_status.

Placing the SET_FIELD in a PRE-RECORD solves the problem, but has a performance problem. The way SQL*Forms 3.0 is implemented, it seems, SET_FIELD for certain attributes (UPDATEABLE, ENTERABLE for example) on a field causes all instances of the field to be updated (once for each record in the block). Suppose for example, that there are 10 records retrieved in the query, and the PRE-RECORD trigger fires causing SET_FIELD to be invoked to change the UPDATEABLE attribute of one of the fields. SET_FIELD will actually go through and mark the field in each of the 10 records as nonupdateable.   Each SET_FIELD call in the trigger would cause all 10 records to be visited (if I were modifying multiple attributes, or multiple fields, for example). Usually this isn't a problem since I don't care what the attribute is for other records, I'm not in them yet.

So far, this seems inefficient, but shouldn't be a big hassle. The problem comes when SQL*Forms starts buffering records to disk. Suppose the number of records to buffer is set to 10. I have retrieved the first 10 records in the query set, I cursor down and my PRE-RECORD trigger calls SET_FIELD which updates the various attributes on the various fields for each record in the buffer just fine.

When I get to the 10th record and then cursor down one more time, ALL HELL BREAKS LOOSE. In order to load the 11th record into the buffer, it appears that SQL*Forms writes out the 1st record to disk, loads the 11th record, navigates to that 11th record, fires the PRE-RECORD trigger which causes the SET_FIELD to update an attribute for a field (once for each record, mind you). Now, the 1st record was written to disk. So it needs to be loaded, causing the second record to be written to disk. The first is loaded and updated, and then the second record needs to be loaded causing the third to be written to disk. The second is loaded and updated, and then the third needs to be loaded causing the fourth to be written to disk. Etc. !

This results in severe degradation to say the least. In the application we were developing, sub second record navigation was the norm until about the 60th record (depending on a lot of things about the form). The 61st record took dozens of seconds, and the 62nd record took scores of seconds, and things just got even worse after that.

The work around to the problem was to increase the buffer size to allow people to cursor down as long as they could possibly want. 999 for example. In our case we HAD to perform the SET_FIELD in PRE-RECORD, since whether the user could update the field depended on a value stored elsewhere in the record.

If you want the same attributes applied to all records retreived, you might modify the PRE-RECORD to check the attribute before setting it, and only setting it if it isn't already set the way you want. This means that for the first record in the query set it will execute, but not as you navigate.

I also recall that we ALWAYS set the attributes if the record status is NEW, since these are inherited from the field characteristics settings, not from the current settings for the records of the block.

Send mail for further discussion if needed.

Rayo Received on Fri May 27 1994 - 05:07:03 CEST