Data maintenance

Overview

Model 204 data are maintained and updated with a variety of SOUL statements. This topic describes data maintenance statements and special conditions regarding their usage.

Data maintenance statements

Use the following statements to perform basic data maintenance (record and field additions and updates):

Statement	Action
Add	Place a new field-value pair on a record.
Change	Alter the value of fields in a record.
Delete	Remove fields from a record.
Delete Record	Remove a record from a Model 204 file; this statement reclaims space occupied by the deleted record.
Delete Records	Remove sets of records from a Model 204 file; this statement executes faster than the Delete Record statement but does not reclaim the space occupied by the deleted records.
File Records Under	Save retrieved or collected sets of record numbers for reference in later requests.
Store Record	Put a new record into a Model 204 file.
Update Record	Perform a series of field-level updates in a single call. This statement is intended for use with Parallel Query Option/204.

Example data

Each statement is discussed separately in the sections that follow. To illustrate their usage, assume that the following two records have been stored:

VIN = A99999998E VIN = X99999999Z MAKE = FORD MAKE = FORD COLOR = GREEN COLOR = RED YEAR = 88 YEAR = 04 MODEL = FOCUS MODEL = MUSTANG

Using For Each Record loops

The SOUL data maintenance statements handle one record at a time, therefore the data maintenance statements are always part of a For Each Record loop. The data maintenance may involve a field-value pair for the field.

Add statement

The Add statement adds a new occurrence of a field and/or value to a record.

Syntax

The basic format of the Add statement is:

Add fieldname = {value | (expression)}

Where:

• fieldname identifies the field in a record.
• value specifies the value you want to store.
• (expression) can be used in place of value to specify the resolved value at the time of evaluation. (expression) can be a function call, string concatenation, arithmetic operation, SOUL construct, or Boolean expression. The expression must be enclosed in parentheses to invoke the expression compiler; otherwise the value will be treated as a literal string.

Note: For Large Object data in Model 204 V7.5 and later, a file must be defined with the FILEORG X'100' bit in order to use the above syntax. With LOB fields, the assigned value is typically held in a Longstring variable. In non-FILEORG X'100' files, a special syntax must be used to Add a LOB field.

Example

Referring to the two sample stored records (see Data used in examples in this topic), this request:

BEGIN FIND.RECS: FIND ALL RECORDS FOR WHICH MAKE = FORD BODY IS NOT PRESENT END FIND FOR EACH RECORD IN FIND.RECS ADD BODY = 2DR END FOR END

would change the records to:

VIN = A99999998E VIN = X99999999Z MAKE = FORD MAKE = FORD COLOR = GREEN COLOR = RED BODY = 2DR BODY = 2DR YEAR = 98 YEAR = 04 MODEL = FOCUS MODEL = MUSTANG

Usage

• The Add statement places an additional occurrence of a field-value pair on the record.
• You can use the ADD statement to add any field to a record except for a sort or hash key field. You can use this statement only within a For Each Record loop.
• The Add statement is supported in remote file and scattered group contexts.
• To use the Add statement with multiply occurring fields, see the ADD statement for multiply occurring fields.
• To add a field whose value you do not explicitly specify, see Using VALUE IN to reference a NOTE value, below.
• For Large Object data in non-FILEORG X'100' files, a compiler error is issued for Add (and Store) statements if the context to the right of the equal sign (=) is not a BUFFER reference:

  M204.0037: Invalid syntax

Change statement

The Change statement alters a record by adding a field and value pair, or altering the value of an existing field within a record.

Syntax

The basic format of the Change statement is:

Change fieldname [(subscript)] [= value |(expression)] To (newvalue |(expression))

Where:

• fieldname specifies the name of the field to add to the record, or identify the field where the value is changed.
• (subscript) specifies an ordinal number that is used to select a particular occurrence of a multiply occurring field. See the discussion in Subscripts.
• value is required only if the field has the INVISIBLE attribute. See the discussion of the INVISIBLE attribute.
• newvalue specifies the value that overwrites the existing value for the field.
• (expression) is resolved by the expression compiler and overwrites the existing value for the field. (expression) can be a function call, string concatenation, arithmetic operation, SOUL construct, or Boolean expression. The expression must be enclosed in parentheses to invoke the expression compiler; otherwise the value will be treated as a literal string.

Usage

• You can use the Change statement to change any field in a record except for a sort or hash key field.
• You can use this statement only within a For Each Record loop.
• The Change statement is supported in remote file and scattered group contexts.
• If a Change statement is applied to a record that does not contain the field to be changed, the specified field name and value are added to the record.
• To use the Change statement with multiply occurring fields, see the Change statement for multiply occurring fields.
• To use the Change statement with fields containing Large Object data, where the assigned value is typically held in a Longstring variable:
  • Use the syntax above only for a Model 204 V7.5 and later file that is defined with the FILEORG X'100' bit.
  • For pre-7.5 or non-FILEORG X'100' files, use the special syntax for changing a LOB field.
• To change a field whose value you do not explicitly specify, see Using VALUE IN to reference a NOTE value, below.

Delete statement

The Delete statement removes fields from a record.

Syntax

The format of the Delete statement is:

Delete fieldname [= value | (expression)]

Where:

• fieldname specifies the name of the field to remove from the record.
• value is required only if the field has the INVISIBLE attribute. (See the discussion in Field attributes.)
• (expression) can be used in place of value to specify the resolved value at the time of evaluation. (expression) can be a function call, string concatenation, arithmetic operation, SOUL construct, or Boolean expression. The expression must be enclosed in parentheses to invoke the expression compiler; otherwise the value will be treated as a literal string.

Example

This request directs Model 204 to remove the field BODY from the records retrieved by the FIND.RECS statement:

BEGIN FIND.RECS: FIND ALL RECORDS FOR WHICH VIN = A99999998E OR X99999999Z END FIND FOR EACH RECORD IN FIND.RECS DELETE BODY END FOR END

The records then appear as:

VIN = A99999998E VIN = X99999999Z MAKE = FORD MAKE = FORD COLOR = BLUE COLOR = BLUE YEAR = 98 YEAR = 04 MODEL = FOCUS MODEL = MUSTANG

Usage

• You can use the Delete fieldname statement on any field in a record except for a sort or hash key field. This statement can be used only within a For Each Record loop.
• If the Delete fieldname statement is applied to a record that does not contain the field to be deleted, no action is taken on that record.
• The Delete fieldname statement is supported in remote file and scattered group contexts.
• The Delete fieldname statement supports Large Object data. Processing this statement frees the Table B and Table E data.
• To use with multiply occurring fields, see Deleting multiply occurring fields.
• To use with field groups, see Deleting field groups.
• For information about space recovered from deletions and about attempting to delete missing records, see Deleting fields and records, below.

DELETE RECORD statement

The DELETE RECORD statement removes a record or sets of records from a Model 204 file.

Syntax

The format of the DELETE RECORD statement is:

DELETE RECORD

Example

This request deletes all records found by the FIND statement:

BEGIN FIND.RECS: FIND ALL RECORDS FOR WHICH MAKE = FORD YEAR = 96 END FIND FOR EACH RECORD IN FIND.RECS DELETE RECORD END FOR END

Usage

• When you delete records with the DELETE RECORD statement, the space those records occupy may be reclaimed depending on the file order. For more information on reclaiming space, refer to Reused space.
• You can use this statement only inside a FOR EACH RECORD loop.
• The DELETE RECORD statement is supported in remote file and scattered group contexts.
• For information about space recovered from deletions and about attempting to delete missing records, see Deleting fields and records, below.

Limitation of the date-time stamp feature deleting records

The date-time stamp feature does not include support for DELETE RECORD or DELETE RECORDS processing. DELETE RECORD or DELETE RECORDS processing must be handled by your application software.

As well, you can use logical delete techniques. However, in all forms of deleting records, it is your responsibility to maintain a log of record deletions, if you want one.

DELETE ALL RECORDS statement

The DELETE ALL RECORDS statement deletes sets of records from a Model 204 file.

Syntax

The forms of this statement are:

DELETE [ALL] RECORDS IN label DELETE [ALL] RECORDS ON [LIST] listname

• DELETE ALL RECORDS IN deletes a set of records located by a FIND statement.
• DELETE ALL RECORDS ON LIST deletes the set of records on the named list from the file.

Example

This request deletes the set of records located by the FIND statement:

BEGIN FIND.RECS: FIND ALL RECORDS FOR WHICH MAKE = FORD YEAR = 00 END FIND DELETE ALL RECORDS IN FIND.RECS END

Usage

• The DELETE ALL RECORDS statement initiates fewer internal operations and therefore executes faster than the DELETE RECORD statement. However, use the DELETE RECORD statement rather than DELETE ALL RECORDS for records with ORDERED or UNIQUE fields, to ensure that values in the Ordered Index accurately reflect the contents of the data stored in Table B.

  In addition, when records are deleted with DELETE ALL RECORDS IN, the space they occupy is not reclaimed. When it is desirable to reclaim space to expand existing records or to insert new records, use the DELETE RECORD statement.

• The DELETE ALL RECORDS statement is supported in remote file and scattered group contexts.
• For information about space recovered from deletions and about attempting to delete missing records, see Deleting fields and records, below.

Deleting fields and records

This section expands on the detail of use for the DELETE statements. Some general issues related to deleting fields and records are presented.

Reused space

Space recovered from both record and field deletions is always used to expand existing records that are near the deletions, regardless of which file option is selected. Model 204 inserts new records in space recovered from deleted records only on unordered or hash files, or on sort files for which the Reuse Record Number option of the FILEORG parameter is set active.

If the Reuse Record Number option is active for an unordered, hash, or sort file, you must explicitly delete any INVISIBLE fields associated with a record in the file when deleting the record itself. If an INVISIBLE field is not deleted, it becomes part of any new record that is put into the old record's space.

Deleting from a record list

Error messages might be generated when a FOR EACH RECORD loop is performed on a list of records from which some of the records have been deleted from the file. For example:

BEGIN * * FIND ALL STATE CONTROL RECORDS * STATES: FIND ALL RECORDS FOR WHICH REC = STATE END FIND PLACE RECORDS IN STATES ON LIST FOUND * * EXCLUDE MASS. AND N.H. BECAUSE * THEIR SURCHARGE RATE HAS NOT CHANGED * REMOVE: FIND ALL RECORDS ON LIST FOUND FOR WHICH STATE CODE = MA OR NH END FIND FOR EACH RECORD IN REMOVE DELETE RECORD END FOR * * CHANGE SURCHARGE RATE FOR ALL OTHER STATES * SURCHARGE: FOR EACH RECORD ON LIST FOUND CHANGE SURCHARGE RATE TO .50 END FOR END

This request would produce these messages:

*** M204.1266: NONEXISTENT RECORD REFERENCED - 23 IN FILE INSURE *** M204.1266: NONEXISTENT RECORD REFERENCED - 24 IN FILE INSURE

Depending upon the intent of the request, these messages may or may not indicate an error.

FILE RECORDS statement

The FILE RECORDS statement files a set of records that were retrieved by a FIND statement or that were collected on a list. You can reference the set of records in later requests.

Syntax

The forms of this statement are:

FILE RECORDS IN label UNDER fieldname = value FILE RECORDS IN label UNDER fieldname = (expression) FILE RECORDS ON [LIST] listname UNDER fieldname = value

Usage

• The FILE RECORDS statement adds one of these pairs to the specified records:

  fieldname = value fieldname = (expression)

• The FILE RECORDS statement is supported in remote file and scattered group contexts.
• The field used in a FILE RECORDS statement must have the INVISIBLE KEY or INVISIBLE ORDERED field attributes. Refer to Field attributes for more information.

  In addition, the fieldname = value pair should be unique in the file. If the pair has appeared previously in other records, either by explicit field creation or by a previous FILE RECORDS statement, inconsistencies in the file can occur. The FILE RECORDS statement creates new index entries for the fieldname = value pair, eliminating existing references.

  Note: The index update generated by a FILE RECORDS UNDER statement is never deferred.

• The expression in the syntax above is enclosed in parentheses and is one of following expression types: function call, string concatenation, arithmetic operation, SOUL construct, or Boolean expression.

Example of using an expression

B %REC IS STRING LEN 3 %CT IS FLOAT %VAL1 IS FLOAT %VAL2 IS FLOAT %REC = 'REC' FOR %CT FROM 1 TO 10 IN EXPRESS STORE RECORD ORD1 = (%REC WITH %CT) ORD2 = (%CT * 2) ORD4 = (%CT * 4) END STORE COMMIT FD1: IN EXPRESS FD ORD1 EQ VALUE(%REC WITH %CT) END FIND FR FD1 CHANGE ORD2 TO (%CT * 2.1) ADD ORD3 = (%CT * 3) CHANGE ORD4 = (%CT * 4 ) TO (%CT * 4.1) DELETE ORD3 = (%CT * 3) INSERT ORD4 = (%CT * 5) END FOR FILE RECORDS IN FD1 UNDER INVORD5 = (%REC WITH %CT) END FOR PRINT 'FRV1' FRV1: IN EXPRESS FRV INVORD5 FD2: IN EXPRESS FD INVORD5 = VALUE IN FRV1 END FIND CT2: CT FD2 PRINT VALUE IN FRV1 AND COUNT IN CT2 END FOR END

Locating filed record sets

FIND statements in later requests can locate the filed set of records by using the fieldname = value pair as the retrieval condition. For example, if a set of records were filed with the statement:

SAVE.RECS: FILE RECORDS IN FIND.RECS UNDER SAVE = 1

Then to locate the records again, you use the following statement in the same request or in a later one:

GET.RECS: FIND ALL RECORDS FOR WHICH SAVE = 1

Using lists for filed record sets

Two sets of records retrieved by different FIND statements can be filed together under the same fieldname = value pair only if both sets are first placed on a list, and then the list is filed by one statement, as in the following:

BEGIN FIND.RECS: FIND ALL RECORDS FOR WHICH STATE = VIRGINIA AGENT = DOYLE END FIND SAVE.DOYLE: PLACE RECORDS IN FIND.RECS ON LIST COMPLIST FIND.T3S: FIND ALL RECORDS FOR WHICH STATE = VIRGINIA INCIDENT = T3 END FIND PLACE RECORDS IN FIND.T3S ON LIST COMPLIST SAVE.LIST: FILE RECORDS ON LIST COMPLIST UNDER SAVE = T3S END

If the SAVE.DOYLE statement were replaced with:

SAVE.DOYLE: FILE RECORDS IN FIND.RECS UNDER SAVE = T3S

the original references to SAVE = T3S would be lost as soon as the SAVE.LIST was executed. Thus, a second use of the same fieldname = value pair replaces the previous one.

Simulating the FILE RECORDS UNDER statement

You can simulate the FILE RECORDS statement by explicitly adding a fieldname = value pair to a set of records. For example, if the SAVE.LIST statement in the previous example is replaced by:

SAVE.LIST: FOR EACH RECORD ON LIST COMPLIST ADD SAVE = T3S END FOR

Then the index references to existing records that contain that fieldname = value pair are not invalidated. You are responsible for deleting such references, if deletion is desired.

STORE RECORD statement

The STORE RECORD statement is used to add new records to a Model 204 file. The fieldname=value pairs that constitute the new record must follow the STORE RECORD statement, one to a line, and must not be labeled.

Syntax

The format of the STORE RECORD statement is:

[label:] [IN filename] STORE RECORD fieldname =[value1 | (expression1)] [fieldname2=[value2 | (expression2)]] ... [THEN CONTINUE statement statement ...] END STORE [label]

Where:

• (expression) can be a function call, string concatenation, arithmetic operation, SOUL construct, or Boolean expression. The expression must be enclosed in parentheses to invoke the expression compiler; otherwise the value will be treated as a literal string.
• THEN CONTINUE maintains the record context of the STORE RECORD statement, while allowing additional work to happen on the record. THEN CONTINUE is useful for adding variable numbers of multiply occurring fields, and it is required syntax for adding fieldgroups inside a STORE statement.

Examples

BEGIN STORE RECORD NAME = JEAN ANDERSON SALARY = 30000 POSITION = CHEMIST END STORE END

Using the THEN CONTINUE statement

%COLOR = 'BLUE' STORE RECORD MODEL = %MODEL THEN CONTINUE FR WHERE RECTYPE = 'TABLE' AND COLOR = %COLOR %CODE = COLOR_CODE END FOR ADD COLOR_CODE=%CODE PAI END STORE

Usage

• Use an END STORE statement or another label to end the STORE RECORD statement. Do not end a STORE RECORD statement with an END BLOCK statement.
• This form of the STORE RECORD statement is used to add new records to any file that does not have the sorted or hashed option.
• The STORE RECORD statement is supported in remote file and scattered group contexts.
• To store a field whose value you do not explicitly specify, see Using VALUE IN to reference a NOTE value, below.
• The THEN CONTINUE statement allows for the conditional building of a Model 204 record. You can use any intervening statements after THEN CONTINUE and before END STORE.

  The statements following the THEN CONTINUE statement of the STORE RECORD block operate as if they were coded within a FRN $Currec block, which immediately follows the END STORE statement. This is easier for coding because you do not need to repeat the file specification of the STORE RECORD statement. It is also more efficient because an actual FRN statement is not necessary.

• As of version 7.4, the maximum number of fields that you can add in a STORE RECORD statement using a subscripted variable is 127. If you are currently adding more than 127 fields in this way, the following message will be issued:
  M204.2840: STATEMENT HAS TOO MANY INTERMEDIATE RESULTS

  The best solution is to recode your application to add 127 or fewer fields initially, followed by a record update statement to add the additional fields.

  Example:

  store: store record fieldx=%array(1) fieldx=%array(2) ... fieldx=%array(127) end store frn in store for %x from 128 to 999 add fieldx = %array(%x) end for end for

Large Object storage

Binary Large Objects — fields defined with a BLOB or CLOB attribute (often referred to collectively as LOBs) — are stored in Table E, and they can hold content longer than the 255 limit of regular Model 204 fields. They are useful for holding blocks of text, images, documents, etc.

Note: Prior to the Model 204 version 7.5 introduction of the FILEORG X'100' bit, LOB fields required a special STORE syntax referencing the "universal buffer." This pre-V7.5 syntax is documented in Large Object field processing for non-FILEORG X'100' files.

As of Model 204 V7.5, LOB fields can be stored using normal STORE syntax, typically with a Longstring variable holding the content:

B %REC IS STRING LEN 3 %CT IS FLOAT %VAL1 IS FLOAT %VAL2 IS FLOAT %REC = 'REC' FOR %CT FROM 1 TO 10 IN EXPRESS STORE RECORD ORD1 = (%REC WITH %CT) ORD2 = (%CT * 2) ORD4 = (%CT * 4) END STORE COMMIT FD1: IN EXPRESS FD ORD1 EQ VALUE(%REC WITH %CT) END FIND FR FD1 CHANGE ORD2 TO (%CT * 2.1) ADD ORD3 = (%CT * 3) CHANGE ORD4 = (%CT * 4 ) TO (%CT * 4.1) DELETE ORD3 = (%CT * 3) INSERT ORD4 = (%CT * 5) END FOR END FOR END

When you store an instance of a Large Object field, the value of the data is stored in the file's Table E. Additionally, a LOB descriptor containing a pointer to the value in Table E, as well as other items, are stored in the record data in a Table B entry. The LOB descriptor is 27 bytes in length, plus the 1-byte length and 2-byte field code that apply to all fields — unless the field is preallocated. See Building a Large Object descriptor for a description of how to build a Large Object data descriptor.

The following compiler error is issued when the right side of the equal sign is expected to contain a BUFFER expression and it does not.

M204.0037: INVALID SYNTAX

Sort or hash key files

If you are adding a record to a file that has the sort or hash option, the sort or hash key value follows the STORE RECORD on the same line, as shown below:

STORE RECORD [sort-or-hash-key-value]

The sort or hash key must be provided if the FILEORG parameter was set to indicate that the sort or hash key is required in every record.

For example, the request to store a record in a file that requires the vehicle identification number as the sort key can be written:

BEGIN STORE RECORD A99999998E MAKE = FORD COLOR = GREEN YEAR = 98 MODEL = FOCUS END STORE END

When this record is stored, the field VIN = A99999998E is added to it.

You can also specify the sort or hash key as an expression:

IN TEST1 STORE RECORD (expression) ... END STORE

Where:

(expression) is the sort or hash key. (expression) can be a function call, string concatenation, arithmetic operation, User Language construct, or Boolean expression. The expression must be enclosed in parentheses to invoke the expression compiler; otherwise the value will be treated as a literal string.

Files with a UNIQUE field

If a record is added to the file that has a UNIQUE field, and a uniqueness conflict is detected during the STORE RECORD processing, the partially stored record is backed out. For files without the Reuse Record Number (RRN) option, this results in the use of a record number which cannot be reclaimed.

IN GROUP MEMBER clause

You can use the IN GROUP MEMBER clause to restrict the STORE RECORD statement to one member file in a group context. See IN GROUP MEMBER clause for more information.

FIND ALL VALUES options

Like other FIND statements, you can specify a range of values for the FIND ALL VALUES statement by using the FROM and TO clauses.

In addition, you can select values based upon a pattern by using the LIKE clause.

Storing field groups (FIELDGROUP)

Introduced with Model 204 version 7.5, field groups add a formalized internal structure for sets of repeating fields — for instance, a set of children and their birthdays would automatically associate the second birthday with the second child. With regular repeating fields, the programmer must be very careful: If, for instance, an occurrence of a birthday field is deleted, the correct occurrence of the associated child must also be deleted, or else the repeating values become out-of-sync. Field groups take care of this housekeeping by associating a set of fields with an internal field group ID.

Field groups can be added, changed, and deleted on a record after the record is created, but storing them on the initial STORE RECORD statement requires the use of THEN CONTINUE. The following example demonstrates this, both for fieldgroups and for nested fieldgroups (a fieldgroup contained inside another fieldgroup). Take this set of field definitions:

IN FILE LIBRARY DEFINE FIELDGROUP BOOK IN FILE LIBRARY DEFINE FIELDGROUP CHAPTER WITH FG BOOK IN FILE LIBRARY DEFINE FIELD RECTYPE WITH ORD CHAR IN FILE LIBRARY DEFINE FIELD AUTHOR_FIRSTNAME WITH ORD CHAR IN FILE LIBRARY DEFINE FIELD AUTHOR_LASTNAME WITH ORD CHAR IN FILE LIBRARY DEFINE FIELD BOOK_TITLE WITH ORD CHAR EXACTLY-ONE FG BOOK IN FILE LIBRARY DEFINE FIELD BOOK_SUBTITLE WITH ORD CHAR EXACTLY-ONE FG BOOK IN FILE LIBRARY DEFINE FIELD BOOK_ISBN WITH ORD CHAR EXACTLY-ONE FG BOOK IN FILE LIBRARY DEFINE FIELD BOOK_PRICE WITH AT-MOST-ONE FG BOOK IN FILE LIBRARY DEFINE FIELD BOOK_READERCOMMENT WITH REPEATABLE FG BOOK IN FILE LIBRARY DEFINE FIELD CHAPTER_NO WITH REPEATABLE FG CHAPTER IN FILE LIBRARY DEFINE FIELD CHAPTER_TITLE WITH REPEATABLE FG CHAPTER

Note that the fieldgroup CHAPTER is defined with an attribute of fieldgroup BOOK, which causes CHAPTER to be nested inside BOOK. To add records to this file, a STORE RECORD statement stores a base record, and then fieldgroups are added inside a THEN CONTINUE clause:

in file library store record rectype = 'AUTHOR' author_firstname = 'Jane' author_lastname = 'Austen' then continue add fieldgroup book book_title = 'Sense and Sensibility' book_subtitle = book_isbn = %isbn(%x) book_price = %price(%x) book_readercomment = 'Loved it. Better than "Cats". Mom' book_readercomment = 'An inspired, blistering novel. The New York Times' book_readercomment = 'Excellent! GB Shaw' then continue add fieldgroup chapter chapter_no = 1 chapter_title = 'The Dashwoods of Sussex' end add add fieldgroup chapter chapter_no = 2 chapter_title = 'Mrs. John Dashwood' end add add fieldgroup chapter chapter_no = 3 chapter_title = 'Freddy and Elinor' end add end add add fieldgroup book book_title = 'Pride and Prejudice' book_subtitle = book_isbn = %isbn(%y) book_price = %price(%y) book_readercomment = 'Brief. Encountery. Mom' book_readercomment = 'Would love to snap up the movie rights. H. Smidkin' then continue add fieldgroup chapter chapter_no = 1 chapter_title = 'A Truth Universally Acknowledged' end add add fieldgroup chapter chapter_no = 2 chapter_title = 'Waiting on Mr. Bingley' end add end add end store

Note: In the above example there is no specific "end" statement for the THEN CONTINUE statements. Rather, the context for the continue ends when the containing context ends. In the case above, the context of the outermost THEN CONTINUE is the STORE RECORD statement, which is being continued in order to provide ADD FIELDGROUP statements for field group BOOK. The context for the inner THEN CONTINUE statements — used to add CHAPTER field groups — is the ADD FIELDGROUP statement above it. After adding CHAPTER information for a BOOK, that block is ended with END ADD, and STORE RECORD context returns, where if needed, another BOOK field group can be added.

Nested STORE RECORD statements

The THEN CONTINUE block allows for the coding of a nested STORE..END STORE block within the body of the outer STORE, so that related records may be built together. A nested STORE can refer to a different file context, without compromising the file context of the outer STORE.

The following example stores an order header record along with an order line record:

IN ORDHDR STORE RECORD ORDER_NUMBER = 1000568 CUSTOMER_NUMBER = 111456 THEN CONTINUE IN ORDLINE STORE RECORD ORDER_NUMBER = 1000568 ITEM_ID = F004 ITEM_QTY = 3 END STORE ADD ORDER_STATUS = A END STORE ...

The results of this would be the following record stored in the ORDHDR file:

ORDER_NUMBER = 1000568 CUSTOMER_NUMBER = 111456 ORDER_STATUS = A

and the following record stored in the ORDLINE file:

ORDER_NUMBER = 1000568 ITEM_ID = F004 ITEM_QTY = 3

Multiply occurring fields

In the following example, a For Each Occurrence loop is driven, based on occurrences of the field SALES_MM previously stored, to store occurrences of MONTHLY_TOTAL:

B %MONTHLY_SALES IS FLOAT ARRAY (3) %MONTHLY_SALES(1) = 10 %MONTHLY_SALES(2) = 15 %MONTHLY_SALES(3) = 35 IN SALES STORE RECORD RECTYPE = TOT_SALES SALES_MM = '01' SALES_MM = '02' SALES_MM = '03' THEN CONTINUE FEO_SALES: FEO SALES_MM ADD MONTHLY_TOTAL = %MONTHLY_SALES(OCCURRENCE IN FEO_SALES) END FOR END STORE END

The resultant record in the SALES file is:

RECTYPE = TOT_SALES SALES_MM = 01 SALES_MM = 02 SALES_MM = 03 MONTHLY_TOTAL = 10 MONTHLY_TOTAL = 15 MONTHLY_TOTAL = 35

COMMIT and BACKOUT

The COMMIT and BACKOUT statements can be used following a THEN CONTINUE statement to save parts of a record as it is built, and to back out all of parts of a record conditionally. Consider the following example:

%CUSTNO = '100639' IN ORDERS STORE RECORD RECTYPE = ORDER ORDER_NUMBER = 1000234 CUSTOMER_NUMBER = %CUSTNO THEN CONTINUE COMMIT /? save the order header ?/ FIND_CUST: IN CLIENTS FD RECTYPE = POLICYHOLDER POLICY NO = %CUSTNO END FIND FOR 1 RECORD IN FIND_CUST %ADDRESS = ADDRESS %CITY = CITY END FOR ADD ADDRESS = %ADDRESS ADD CITY = %CITY COMMIT /? Save the customer address ?/ ADD DELIV_DATE = ($datechg('YYYYMMDD',$DATE(1,''),10)) IF %ORDER_DELAYED = 'Y' THEN BACKOUT /? Back out deliv date if delay detected ?/ END IF END STORE

If %ORDER_DELAYED is not Y, the record is stored as follows:

RECTYPE = ORDER ORDER_NUMBER = 1000234 CUSTOMER_NUMBER = 100639 ADDRESS = 0880 HANCOCK STREET CITY = LANCASTER DELIV_DATE = 20111228

Otherwise, the DELIV_DATE fieldname=value pair is backed out.

Known restrictions or limitations

• Be cautious of using the JUMP TO statement following THEN CONTINUE to jump to a label outside the STORE..END STORE block, as this may lead to the storing of a partial record.
• It is possible to call a subroutine after the THEN CONTINUE statement, as you might in a FOR RECORD NUMBER loop. Additional update statements to the current record are allowed in the subroutine but only in a FRN $Currec loop. Otherwise, record context is not established and any additional updating statements within the subroutine would be rejected with the following compilation error:

  M204.0229: INVALID STATEMENT

• A DELETE RECORD statement following THEN CONTINUE, but before END STORE, causes the current record context to be lost. Any further update statements cause the request to be cancelled with one of the following messages:

  M204.1233: DFAV, BAD RECORD NUMBER n FOR FILE filename M204.1266: NONEXISTENT RECORD REFERENCED - n IN FILE DSNLIST

Note: When using THEN CONTINUE, keep in mind standard considerations for coding any update unit. Be aware that creating longer update units has implications for resource sharing, checkpoints, and recovery requirements.

UPDATE RECORD statement

The UPDATE RECORD statement improve performances in remote context by using only one network call to perform all of a group of field-level updates (ADD, CHANGE, DELETE) against the current record in a record loop.

Syntax

The syntax of the UPDATE RECORD statement is as follows:

UPDATE RECORD update-statement-1 update-statement-2 . . . update-statement-N END UPDATE

Where:

An update-statement is one of the following:

• ADD
• DELETE
• CHANGE
• INSERT

Usage

• The UPDATE RECORD statement, while supported in all reference contexts, is intended for use with Parallel Query Option/204.
• If a series of update statements is executed individually, each one requires a separate network call.
• All forms of the update statements are supported. Except, a DELETE EACH statement is not allowed within an UPDATE RECORD statement.
• If a field constraint violation occurs, the entire UPDATE statement is backed out.
• If an ON unit invoked during the processing of an UPDATE RECORD statement is run with a BYPASS statement, the processing of the request continues with the statement that follows the END UPDATE statement.
• If no updates are found between UPDATE RECORD and END UPDATE, the statement is ignored.

Using VALUE IN to reference a NOTE value

VALUE IN label clause

The clause VALUE IN label can replace an explicit field value in the ADD, CHANGE, and STORE RECORD statements. This also applies to the special forms of these statements that are discussed in Processing multiply occurring fields and field groups.

The forms of the VALUE IN statement are:

ADD fieldname = VALUE IN label CHANGE fieldname TO VALUE IN label STORE RECORD fieldname = VALUE IN label

Example

The following request finds all records in the CLIENTS file that are registered in Alexandria and insured by agent Casola. The policy number for each record found is noted and a corresponding policy number is located on the VEHICLES file. The vehicle premium for the policy on the VEHICLES file is then changed to the total premium amount noted for the policy on the CLIENTS file.

BEGIN FIND.RECS: IN CLIENTS FIND ALL RECORDS FOR WHICH AGENT = CASOLA CITY = ALEXANDRIA END FIND FOR EACH RECORD IN FIND.RECS KEEP.POL: NOTE POLICY NO KEEP.PREM: NOTE TOTAL PREMIUM FIND.MATCH: IN VEHICLES FIND ALL RECORDS FOR WHICH OWNER POLICY = VALUE IN KEEP.POL END FIND FOR EACH RECORD IN FIND.MATCH CHANGE VEHICLE PREMIUM TO VALUE - IN KEEP.PREM END FOR END FOR END

Storing data in fields

Storing null values

If the new value in an ADD, CHANGE, or STORE statement is left blank, no field is added to or stored with the record. If a field containing a null value must be added, you specify the value as an explicit null string (two single quotes with no space between them). For example:

ADD VEHICLE PREMIUM = '' CHANGE AGENT TO ''

Note that this statement:

CHANGE FULLNAME TO

is equivalent to:

DELETE FULLNAME

because the old value of FULLNAME is deleted, but no new value is added.

Using the FIND statement to select fields with null values

You can use the FIND statement to select records that have a field whose value is the null string, as illustrated below:

FIND.RECS: IN CLIENTS FIND ALL RECORDS FOR WHICH FULLNAME = '' END FIND

However, the FIND statement does not select records for a particular field that is missing altogether from the record. See IS PRESENT condition and Locating records missing a particular field for examples of finding records without a particular field.

Storing values in preallocated fields

The file manager can indicate in a field definition the length of the field (LENGTH attribute) and/or the number of times that field can occur in a record (OCCURS attribute). Space for fields with the LENGTH and OCCURS attributes is preallocated in each record in a file, and this space cannot be expanded.

If you attempt to store more values (an OCCURS violation) or longer values (a LENGTH violation) than a field's definition permits, an error message is displayed or the request is cancelled.

LENGTH violations

If a field is defined as having a particular length (LENGTH m), that field can store only values that are between one and m bytes long. Other values are rejected. If you explicitly specify a field name and value in a SOUL statement, as in this request:

ADD YEAR = 90

Model 204 checks the length of the value during the compilation phase. A length violation detected in an update statement (ADD, CHANGE, FILE, or STORE) results in a compilation error. A length violation also can be detected for a STORE statement for a sort or hash key defined with LENGTH m.

If field name variables or %variables are used in an update statement, length validity checks are deferred until the request is evaluated. If an error is detected at this point, the request is cancelled. Request cancellation can be avoided by using $FldLen. Specifying a field value that is too long for a LENGTH field in a retrieval context always causes the retrieval to fail, because the value could not have been stored.

Attempts to locate invalid values are treated as references to a nonexistent value. For example, a selection criterion in a FIND fieldname = value fails to locate any records.

OCCURS violations

If a field is defined as occurring a particular number of times (OCCURS n), it can be stored up to n times in any record. An attempt to add (using ADD or STORE RECORD statement) an additional occurrence to a record containing the maximum number causes the request to be cancelled. To protect SOUL requests from cancellations due to occurrence violations refer to:

• COUNT OCCURRENCES OF statement
• $Occurs

Storing values in FLOAT fields

Exponent notation

If a new value is to be stored in a field defined with the FLOAT attribute, the value can be defined in exponent notation. See Exponent notation for information.

An invalid value is stored as an unconverted string.

String values

When you supply a string as the value to be stored, Model 204 attempts to convert the string to floating point representation according to the floating point conversion rules (see Equality retrievals). If the value to be stored cannot be converted, one of two things happens:

• If the field is preallocated, the request is cancelled.
• If the field is not preallocated, the unconverted value is stored.

Floating point values

When you supply a floating point value as the value to be stored, the value is not altered if its length is the same as the floating point field's defined length. Values of different lengths are truncated or rounded according to the rules described in.

Storing values in BINARY fields

Compressed values

Fields defined as having the BINARY, OCCURS, and NON-CODED attributes can store only compressible values because only a small amount of space is preallocated for such a field. A compressible value is a decimal integer of up to nine digits with no plus sign, leading zeros, embedded blanks (following a minus sign), or decimal point. Refer to Data typing for additional information on such values.

Value checking

Values to be stored in BINARY fields are not checked until the request is evaluated. If you attempt to store an incompressible value in a BINARY, OCCURS, and NON-CODED field, the request is cancelled.

Updating field groups

Note: Physical field groups are supported as of Model 204 version 7.5.

When a field group is added, a field group ID is assigned to the field group. This field group ID is unique among all field groups in the record, whether or not they have the same field group name. The ID is a number between 1 (zero is not used as an ID) and 2**32-1. The field group ID is an unsigned 32-bit integer, though only a length byte and trailing non-zero bytes are stored.

Field group IDs from 1-255 require two bytes for the field group ID in Table B, IDs 256-65535 require three bytes, and so on. Once assigned, a field group ID is not reused regardless of whether the field group associated with the ID is deleted. That is, if you add a field group and it is assigned ID 6, and then you delete the field group, ID 6 will not be reused.

The one exception when field group ID reuse is allowed is on transaction back out. If field group ID 6 is assigned to an added field group, but then the addition is backed out, field group ID 6 can be assigned to a subsequent ADD or INSERT FIELDGROUP.

The following subsections provide an overview of the basic operations with field groups. For additional details, see Processing multiply occurring fields and field groups.

Adding a field group

To add a field group, issue an Add Fieldgroup statement in a record context:

Fr In LabelX Add Fieldgroup fieldgroupname field1 = (value1 | expression) [field2 = (value2 | expression) ...] End Add End For

The syntax of the fieldname=value pairs inside an Add Fieldgroup statement is identical to that of a Store Record statement. See also Inserting a field group.

In place of a value in the fieldname=value pair, you can enter an expression. See Expressions as field name values for details.

Inserting a field group

In addition to the ADD FIELDGROUP statement, you can also issue an INSERT FIELDGROUP statement:

FR WHERE ... INSERT FIELDGROUP name(subscript) FIELDA = ’ABC’ FIELDB = ’DEF’ ... END INSERT END FOR

The INSERT FIELDGROUP statement has the same semantics as an INSERT FIELD statement. If the indicated occurrence of the field group is found, the new occurrence is inserted immediately before that occurrence. If it is not found, the new occurrence is added at the end of the record.

The inside of the INSERT block has the identical format to the STORE RECORD and ADD FIELDGROUP statements.

Deleting a field group

You can delete a field group several ways.

Delete first field group occurrence in each record

BEGIN A: IN TEST1 FD END FIND B: FR A DELETE FIELDGROUP BIRDS END FOR

Delete all field group occurrences, if present, in one record

BEGIN A: IN TEST1 FD END FIND B: FR A DELETE EACH FIELDGROUP PESTS END FOR

Delete a field group without the name or ID reference

BEGIN A: IN TEST1 FD END FIND B: FR A FOR EACH OCCURRENCE OF FIELDGROUP TRANSLATIONS IF LANGUAGE = ’INDIAN’ THEN %X = $FIELDGROUPID DELETE FIELDGROUP PRINT ’DELETING ID: ’ WITH %X END IF END FOR END FOR

Delete a field group with a subscript

To delete the third occurrence of field group PITCHERS, you would issue the following statement:

FR IN LABELX DELETE FIELDGROUP PITCHERS(3) END FOR

When you delete a field group occurrence, all fields within the field group occurrence are deleted, as well as the indices for those fields. Or, you can delete a field group with a simple DELETE statement while in a field group context:

FR IN LABELX FAO FIELDGROUP PITCHERS DELETE FIELDGROUP END FOR END FOR

Displaying field groups and their fields

After you add a field group to a record, you can display the field groups and their fields by issuing a PAI statement, output as follows:

BEGIN IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100095 PAI END POLICY_NUMBER = 100095 POLICY_RESTRICTIONS = POLICY IS INVALID IF ... ... OTHER POLICY FIELDS \DRIVER = 1 DRIVER_NAME = ALTON, BARBARA W ...OTHER DRIVER FIELDS /DRIVER = 1 \DRIVER = 2 DRIVER_NAME = ALTON, CARRIE Y ...OTHER DRIVER FIELDS /DRIVER = 2 \VEHICLE = 6 MAKE = VOLKSWAGEN MODEL = NEW BEETLE ...OTHER VEHICLE FIELDS /VEHICLE = 6 \VEHICLE = 7 MAKE = MITSUBISHI MODEL = ECLIPSE ...OTHER VEHICLE FIELDS \CLAIM = 9 CLAIM_NUMBER = 100059 WEATHER = RAIN ...OTHER CLAIM FIELDS /CLAIM = 9 \CLAIM = 10 CLAIM_NUMBER = 100064 WEATHER = FOG ...OTHER CLAIM FIELDS /CLAIM = 10 /VEHICLE = 7 \VEHICLE = 8 MAKE = CHEVROLET MODEL = SUBURBAN ...OTHER VEHICLE FIELDS /VEHICLE = 8

The lines that begin with a backslash (\) identify the start of a field group and the lines that begin with a forward slash (/) identify the end of the field group. The field group ID of the field group occurrence is indicated after the field group name on both the field group beginning and ending lines.

The field group IDs are not necessarily in ascending order in a record. The previous example was created by issuing INSERT statements, so the IDs are out of order.

See also:

• The PAFGI statement to display a field group's content.
• The function $FieldgroupId to show the field group ID.

Support for field group name variables

Field group name variables are supported; for example:

IN POLICIES FRN %RECNO %FIELD = ’SURCHARGE%’ ADD FIELDGROUP VEHICLE MAKE = ’FORD’ MODEL = ’MUSTANG’  %%FIELD = 15 * and so on END ADD FIELDGROUP VEHICLE END FOR

Field name variables that evaluate to something in an invalid field group context result in request cancellation. For example, if BARD is a field in field group PITCHERS and you issue the following statement:

IN FILE REDSOCKS FRN %RECNO %FIELD = ’BARD’ ADD %%FIELD = ’Knuckle ball’

A request cancellation is generated, because you tried to add an occurrence of field BARD outside its appropriate field group context.