A flexible method of storing control data

Tony Marston - 16th March 2001

Where an application requires certain values at runtime, and where these values may be changed at irregular intervals, it is common practice to hold these on a database record rather than having them hard-coded into any program. This means that should any of these values ever change it is a simple matter of updating the database rather than changing, compiling and releasing individual program modules. This can be a problem, of course, if one of the modules is missed out.

The most common structure used is to have a single control record containing a separate field for each item of data. However, this has the following disadvantages:

A more flexible structure that I have used for several years is not to have a single control record containing all possible fields, but to have a separate record for each individual field. The record structure is as follows:

primary key
RECORD_ID
C16
FIELD_ID
C32
FIELD_VALUE
VC255
CONTROL DEFAULT_LANGUAGE USA
CONTROL PSWD_ENCRYPT F
CONTROL PSWD_RETRIES 5
CONTROL STD_FACTOR 3.275

RECORD_ID is used to group various records into logical sets. The same table could be used by multiple applications, so by setting RECORD_ID to the application name each application's data can be kept separate from the other, even if any FIELD_IDs are the same.
FIELD_ID is the name of the field, unqualified, in upper case.
FIELD_VALUE is a string field as its holds any value in display format regardless of the interface definition (number, date, time, boolean, etc) of the source field on the screen. Data will be transferred to/from the screen using getitem and putitem.

Defining entities in the form component

You will want to display the values in your component as if they exist on a single occurrence, but they actually exist on multiple occurrences. This means that within your component you define the CONTROL table without any fields, and define a second DUMMY entity to contain all the fields that you actually require. If you are retrieving existing entries then the field names that you use must match the field names defined in the FIELD_ID field. The field sizes cannot exceed the maximum size defined for the FIELD_VALUE field, but the interface definition can be whatever is suitable for each individual value - string, number, boolean, date or time, etc.

Retrieving entries from the CONTROL table

All the CONTROL file entries must be retrieved manually before the edit/display statement is processed using code similar to the following:

record_id.CONTROL/init = "whatever"
retrieve/e "CONTROL"
setocc "CONTROL",-1                  ; read all entries

getlistitems/occ $list$, "DUMMY"     ; put values into DUMMY entity

This constructs an associative list of field names and values in the component variable $list$, then loads the values into the corresponding fields that have been painted in the DUMMY entity. The <read> trigger for the CONTROL entity should contain something like the following:

read

if ($status = 0)
   ; add "field_id=field_value" to associative list in $LIST$
   putitem/id $list$, "%%field_id.CONTROL", field_value.CONTROL
endif

If a field name found on the CONTROL table does not exist on the DUMMY entity it will not cause an error. The only effect is that the value cannot be displayed and therefore cannot be modified.

If a field name painted on the DUMMY entity does not have a corresponding entry in the CONTROL table then it will initially be displayed as empty, and any data entered by the user will be treated as an insert, not an update.

Writing values back to the CONTROL table

When the user has finished making changes to the screen contents it will be necessary to transfer the values from the DUMMY entity to the corresponding occurrences on the CONTROL table. This can be achieved using code similar to the following:

entry LP_WRITE_CONTROL     ; write new values to database
variables
   string  lv_list, lv_fieldname, lv_fieldvalue
endvariables

lv_list = $entinfo(DUMMY, "paintedfields")     ; get list of fields

; for each fieldname load value into corresponding database record
while (lv_list != "")
   getitem lv_fieldname, lv_list, 1            ; get 1st field name
   delitem lv_list, 1                          ; delete from list

   creocc "CONTROL",-1
   record_id.CONTROL = "whatever"
   field_id.CONTROL  = lv_fieldname
   retrieve/o                                  ; position on previous entry

   ; indirection only works on a $variable
   $$fieldname	= lv_fieldname
   lv_fieldvalue= @$$fieldname                 ; get field value
   if (field_value.CONTROL != lv_fieldvalue)   ; has it changed?
       field_value.CONTROL = lv_fieldvalue     ; yes - store it
   endif

endwhile                                   ; stop when empty

return(0)

end LP_WRITE_CONTROL

This will first use the $entinfo statement to obtain a list of field names that have been painted on the DUMMY entity. The while loop will then extract each field name from this list one at a time and look for a corresponding entry on the CONTROL table. The retrieve/o statement will locate an existing entry so that it can be updated, otherwise a new entry will be created. If the value on the database has not changed then no update will be performed.

Conclusion

This structure provides the following advantages:

Examples of this code in action can be found in my demonstration application which can be downloaded from my Building Blocks page.


Tony Marston
16th March 2001

mailto:tony@tonymarston.net
mailto:TonyMarston@hotmail.com
http://www.tonymarston.net

counter