Data maintenance

Overview

Model 204 data are maintained and updated with a variety of 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 to 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.

Using FOR EACH RECORD loops

The User Language 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.

Data used in examples in this topic

Each statement is discussed separately on the pages 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

ADD statement

Purpose

Add 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, 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.

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

You can also add Large Object data to Model 204, as shown using the following syntax:

Large Object field syntax

ADD lob-name=BUFFER,position,length [RESERVE n [BYTES]]

Where:

• lob-name specifies the field name of the Large Object data.

  Note: you cannot use subscripts on ADD statements.

• BUFFER specifies the Universal Buffer
• position is a positive number specifying the offset of the first character in the buffer or Large Object data. If the position is set to a negative value, an error occurs. The position can be a %variable or a constant.
• length is a positive number specifying the length to move. The length can be a %variable or a constant.
• RESERVE n specifies a positive number of bytes to reserve for the Large Object field value. The number of RESERVE bytes is always greater than or equal to maximum length of Large Object.
• BYTES, optional, specifies that n applies to bytes.

Example

BEGIN IMAGE XYZ BUFF_DATA IS STRING LEN 200 END IMAGE IDENTIFY IMAGE XYZ * add LOB field with "CURRECn" to the records: FOR EACH RECORD * enter data into image item: %XYZ:BUFF_DATA = 'CURREC' WITH $CURREC * write image on the buffer: WRITE IMAGE XYZ ON BUFFER POSITION=1 MAXLEN=200 * add LOB field from buffer contents: ADD MY.LOB.FIELD=BUFFER,1,10 RESERVE 200 BYTES END FOR * print LOB data: FOR EACH RECORD * place LOB data into the buffer: BUFFER,1,200=MY.LOB.FIELD,1,200 * place contents of the buffer into image: READ IMAGE XYZ FROM BUFFER * print LOB data that has been placed into the buffer: PRINT VIN AND %XYZ:BUFF_DATA END

This code would result in adding a large object field to each record containing the text "CURREC" followed by the internal record number and this output:

A99999998E CURREC0 X99999999Z CURREC1

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 ADD statement.

For Large Object data, 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

Purpose

Alter 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 [= 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.
• value is required only if the field has the INVISIBLE attribute. See the discussion on 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, 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.

Large Object field syntax

You can also change large object fields with the CHANGE statement using the following syntax:

CHANGE lob-fieldname,position1,length TO BUFFER position2,length

• lob-fieldname specifies the name of the field
• position, position1 and position2 are positive numbers specifying the offset of the first character in the buffer or Large Object data. If position, position1, or position2 is set to a negative value, an error occurs. Any positions can be a %variable or a constant.
• length is a positive number specifying the length to move. The length can be a %variable or a constant. The source and target lengths must be equal.

  If position plus length minus one exceeds the current length of the LOB field, the intervening bytes are filled with binary 0. If the file has FILEORG X'100' on, then any final length of the LOB field is allowed. Otherwise extending a LOB field requires that the final length must fit within the RESERVE clause length specified when the LOB field was added.

  For example, if the buffer contains "ABCDEFGHIJKL" and the field is initially stored with:

  ADD LOB.FLD=BUFFER,1,3 RESERVE 500 BYTES

  The field would contain "ABC". If it was subsequently changed as follows:

  CHANGE LOB.FLD,5,10 TO BUFFER,1,10

  The field would then contain "ABC ABCDEFGHIJ" with position 4 being a binary zero.

• TO BUFFER specifies the Universal Buffer. You can use BUFFER only in conjunction with a Large Object field.

Example

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

BEGIN FIND.RECS: FIND ALL RECORDS FOR WHICH VIN = A99999998E OR X99999999Z END FIND FOR EACH RECORD IN FIND.RECS CHANGE COLOR TO BLUE END FOR END

cause Model 204 to change the value of the COLOR field in each record to BLUE. The two records then appear as:

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

Example for Large Object

BEGIN IMAGE XYZ BUFF_DATA IS STRING LEN 200 END IMAGE IDENTIFY IMAGE XYZ IN JUNK FOR EACH RECORD * place LOB data into the buffer: %XYZ:BUFF_DATA = 'This is the data for VIN ' WITH VIN * write image on the buffer: WRITE IMAGE XYZ ON BUFFER POSITION=1 MAXLEN=200 * change the LOB field to the contents of the buffer: CHANGE MY.LOB.FIELD,1,50 TO BUFFER,1,50 END FOR * print LOB data: FOR EACH RECORD * place LOB data into the buffer: BUFFER,1,200=MY.LOB.FIELD,1,200 * place contents of the buffer into image: READ IMAGE XYZ FROM BUFFER * print LOB data that has been placed into the buffer: PRINT VIN AND %XYZ:BUFF_DATA END

This code results in changing the large object field in each record to the text "This is the data for VIN" followed by the VIN field and this output.

A99999998E This is the data for VIN A99999998E X99999999Z This is the data for VIN X99999999Z

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 CHANGE statement.

Changing Large Object fields and data

If you issue a CHANGE statement on a Large Object field to a record that does not contain the field, nothing happens. Unlike a non-Large Object field, a new occurrence of the field is not added to the record.

Extending a LOB field requires that the final length must fit within the RESERVE clause length specified when the LOB field was added.

Note: You cannot change the number of RESERVE bytes. Furthermore, facilities are not available to delete a portion of data or to insert data: for example, to replace 10 bytes with 25 bytes within Large Object data. When an attempt to insert or delete data is made the following error message is issued:

M204.2693: SOURCE AND TARGET LENGTH MUST BE EQUAL

All Large Object data implicitly has the contiguous characteristic. A User Language procedure can store some amount of initial data and then extend the data up to the RESERVE number of bytes with subsequent CHANGE statements. For example:

FR * add initial 10 bytes: ADD LOB.FLD=BUFFER,1,10 RESERVE 200 BYTES * add increments of 10 bytes at * positions 11, 21, 31, 41 * moving data from the buffer to the field FOR %X FROM 1 TO 4 %Y = %X WITH '1' CHANGE LOB.FLD,%Y,10 TO BUFFER,%Y,10 END FOR PAI PRINT 'LOBLEN' AND $LOBLEN(LOB.FLD) END

DELETE statement

Purpose

Remove 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, 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.

Example

For example, the request on the next page 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 DELETE statement.

DELETE RECORD statement

Purpose

Remove 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.

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

Purpose

Delete 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

• The DELETE ALL RECORDS IN statement deletes a set of records located by a FIND statement.
• The 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 the DELETE ALL RECORDS IN statement, 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.

FILE RECORDS statement

Purpose

To file 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 the pair:

fieldname = value

or

fieldname = (expression)

to the specified records.

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.

expression is enclosed in parentheses and is one of following expression types: function call, string concatenation, arithmetic operation, User Language 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:

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

appearing either in the same request or in a later one would locate the records again.

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

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

Purpose

Use the STORE RECORD statement 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:

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, 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.

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 is required syntax for adding fieldgroups inside a STORE statement.

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 can hold content longer than the 255 limit of regular Model 204 fields. They are useful for holding blocks of text, images, documents, etc. Prior to the Model 204 Version 7.5 introduction of X'0100' files (see the FILEORG parameter for more information) LOB fields required a special STORE syntax referencing the "universal buffer".

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

%myImage is longString ... store record rectype="IMAGE" name="sunset.jpg" image=%myImage end store

Prior to Model 204, or in lieu of the X'0100' FILEORG bit being set, LOB fields require the use of the universal buffer, and special syntax to access the buffer.

STORE RECORD lob-name=BUFFER,position,length [RESERVE n [BYTES]] . . . END_STORE [label]

Where:

• lob-name specifies the field name of the Large Object field.

  Note: you cannot use subscripts on statements for any field type.

• BUFFER specifies the Universal Buffer
• position is a positive number specifying the offset of the first character in the buffer. If the position is less than one, an error occurs. The position can be a %variable or a constant.
• length is a positive number specifying the length to move. The length can be a %variable or a constant.
• RESERVE >n specifies a positive number of bytes to reserve for the Large Object field value. The number of bytes is always greater than or equal to $LOBLEN.
• BYTES, optional, specifies that n applies to bytes.

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

Storing Large Object data

STORE RECORD NOVEL=BUFFER,%POSITION,%LENGTH [RESERVE n [BYTES]] AUTHOR_PIC=BUFFER,%POSITION2,%LENGTH2 END STORE

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 END FOR END

As is obvious from the above sample, the newer Model 204 V7.5 syntax for LOB fields is more natural and much easier to use. It is recommended that files holding LOB fields be reorganized to include FILEORG=X'0100' as soon as a site is upgraded to Version 7.5.

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.

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 again specify the file specification of the STORE RECORD statement. It is also more efficient because an actual FRN statement is not necessary.

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 more information refer to the FILEORG parameter.

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.

You can store Large Object data in Model 204 using a STORE RECORD statement as shown in the following example:

Handling Large Object data

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, is 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

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 has to be very careful if, for instance, an occurrence of a birthday field is deleted, that the correct occurrence of child is also deleted, or else the repeating values would end up 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 to store 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 CHAPTERs to be nested inside BOOK. To add records to this file, we store a base record in a STORE RECORD statement, and then add the fieldgroups 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

A few things of note in the above example: There is no specific "end" statement for the THEN CONTINUE. 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 we're 'continuing' so we can do some ADD FIELDGROUPs for field group BOOK. The context for the inner THEN CONTINUEs - used to add CHAPTER field groups - is the ADD FIELDGROUP statement above it. When we're done adding CHAPTER information for a BOOK, we END ADD that block, which puts us back in the STORE RECORD context, where we can, if needed, add another BOOK field group.

Support for nested STOREs

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. The nested STORE(s) 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

Support for 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

Support for 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. In 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' then 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 f-v 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, will cause the current record context to be lost. Any further update statements will 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

Purpose

Improve performance 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:

update-statements are 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.

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.

Possible error messages

Error messages might be generated when a FOR EACH RECORD loop is performed on a list of records of 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

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.

Using NOTE values in data maintenance statements

VALUE IN label clause

The clause VALUE IN label can replace an explicit field value in any of the following data maintenance statements. This also applies to the special forms of these statements that are discussed in Operations on multiply occurring fields.

Syntax

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 User Language 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 User Language 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: 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.

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.

The new function $FIELDGROUPID can be used 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.