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:

• It can result in a very large record structure, which could be a problem as most database engines have a limit to the number of columns (fields) in a table.
• Adding or removing items on the control record requires a change to the physical database structure. All components which reference this structure would have to be recompiled.
• Any component which updates any control data will have to lock the entire record. This may cause delays to other components which try to update the same record at the same time.

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:

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:

• Rather than having a single record with a large number of fields there are now a large number of records with a small number of fields.
• It is possible to add or remove values from the CONTROL table without making any structural alterations to the physical database table, or having to recompile all those components that access it.
• All that is required to add a new value to the CONTROL table is to add a new field to the maintenance screen and run it, enter data, and store.
• When a value is updated a lock is only applied on the occurrence with that field name, not the whole table.
• The limit concerning the number of columns that a table may hold does not apply. Instead the only limitation is the capacity of the table.

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