Data maintenance: Difference between revisions

From m204wiki
Jump to navigation Jump to search
mNo edit summary
 
(69 intermediate revisions by 7 users not shown)
Line 1: Line 1:
<div class="toclimit-2">
<div class="toclimit-3">


==Overview==
==Overview==
<p>
<p>
<var class="product">Model&nbsp;204</var> data are maintained and updated with a variety of statements. This topic describes data maintenance statements and special conditions regarding their usage. </p>
<var class="product">Model&nbsp;204</var> data are maintained and updated with a variety of [[SOUL]] statements. This topic describes data maintenance statements and special conditions regarding their usage. </p>
   
   
===Data maintenance statements===
===Data maintenance statements===
Line 15: Line 15:
   
   
<tr>
<tr>
<td><var>ADD</var> </td>
<td><var>Add</var> </td>
<td>Place a new field-value pair on a record.</td>
<td>Place a new field-value pair on a record.</td>
</tr>
</tr>
   
   
<tr>
<tr>
<td><var>CHANGE</var> </td>
<td><var>Change</var> </td>
<td>Alter the value of fields in a record.</td>
<td>Alter the value of fields in a record.</td>
</tr>
</tr>
   
   
<tr>
<tr>
<td><var>DELETE</var> </td>
<td><var>Delete</var> </td>
<td>Remove fields from a record.</td>
<td>Remove fields from a record.</td>
</tr>
</tr>
   
   
<tr>
<tr>
<td><var>DELETE RECORD</var> </td>
<td><var>Delete Record</var> </td>
<td>Remove a record from a <var class="product">Model&nbsp;204</var> file; this statement reclaims space occupied by the deleted record.</td>
<td>Remove a record from a <var class="product">Model&nbsp;204</var> file; this statement reclaims space occupied by the deleted record.</td>
</tr>
</tr>
   
   
<tr>
<tr>
<td><var>DELETE RECORDS</var> </td>
<td><var>Delete Records</var> </td>
<td>Remove sets of records from a <var class="product">Model&nbsp;204</var> file; this statement executes faster than the DELETE RECORD statement but does not reclaim the space occupied by the deleted records.</td>
<td>Remove sets of records from a <var class="product">Model&nbsp;204</var> file; this statement executes faster than the <var>Delete Record</var> statement but does not reclaim the space occupied by the deleted records.</td>
</tr>
</tr>
   
   
<tr>
<tr>
<td nowrap><var>FILE RECORDS UNDER</var> </td>
<td nowrap><var>File Records Under</var> </td>
<td>Save retrieved or collected sets of record numbers for reference in later requests. </td>
<td>Save retrieved or collected sets of record numbers for reference in later requests. </td>
</tr>
</tr>
   
   
<tr>
<tr>
<td><var>STORE RECORD</var> </td>
<td><var>Store Record</var> </td>
<td>Put a new record to a <var class="product">Model&nbsp;204</var> file.</td>
<td>Put a new record into a <var class="product">Model&nbsp;204</var> file.</td>
</tr>
</tr>
   
   
<tr>
<tr>
<td><var>UPDATE RECORD </var></td>
<td><var>Update Record </var></td>
<td><var>Perform a series of field-level updates in a single call. This statement is intended for use with Parallel Query Option/204.</var></td>
<td>Perform a series of field-level updates in a single call. This statement is intended for use with <var class="product">Parallel Query Option/204.</var></td>
</tr>
</tr>
</table>
</table>


===Using FOR EACH RECORD loops===
====Example data====
<p>
<p>
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.</p>
Each statement is discussed separately in the sections that follow. To illustrate their usage, assume that the following two records have been stored:</p>
===Data used in examples in this topic===
<p>
Each statement is discussed separately on the pages that follow. To illustrate their usage, assume that the following two records have been stored:</p>
<p class="code">VIN = A99999998E          VIN = X99999999Z
<p class="code">VIN = A99999998E          VIN = X99999999Z
MAKE = FORD                MAKE = FORD
MAKE = FORD                MAKE = FORD
Line 68: Line 64:
MODEL = FOCUS              MODEL = MUSTANG
MODEL = FOCUS              MODEL = MUSTANG
</p>
</p>
===Using For Each Record loops===
<p>
The SOUL data maintenance statements handle one record at a time, therefore the data maintenance statements are always part of a <var>For Each Record</var> loop. The data maintenance may involve a field-value pair for the field.</p>
   
   
==ADD statement==
==Add statement==
====Purpose====
<p>
<p>
Add a new occurrence of a field and/or value to a record.</p>
The <var>Add</var> statement adds a new occurrence of a field and/or value to a record.</p>
   
   
====Syntax====
====Syntax====
<p>
<p>
The basic format of the ADD statement is:</p>
The basic format of the <var>Add</var> statement is:</p>
<p class="syntax">ADD <span class="term">fieldname</span> = {<span class="term">value</span> <span class="squareb">|</span> (<span class="term">expression</span>)}
{{Template:Add fieldname statement syntax}}
</p>
<p>
<p>
Where:</p>
Where:</p>
<p>
<ul>
<span class="term">fieldname</span> identifies the field in a record.</p>
<li><span class="term">fieldname</span> identifies the field in a record.</li>
<p>
 
<span class="term">value</span> specifies the value you want to store.</p>
<li><span class="term">value</span> specifies the value you want to store.</li>
<p>
 
(<span class="term">expression</span>) can be used in place of value to specify the resolved value at the time of evaluation. (<span class="term">expression</span>) 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.</p>
<li>(<span class="term">expression</span>) can be used in place of value to specify the resolved value at the time of evaluation. (<span class="term">expression</span>) 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.</li>
</ul>
 
<p class="note"><b>Note:</b> For Large Object data in <var class="product">Model&nbsp;204</var> V7.5 and later, a file must be defined with the <var>[[FILEORG parameter|FILEORG]]</var> X'100' bit in order to use the above syntax.  With LOB fields, the assigned value is typically held in a <var>[[Longstrings|Longstring]]</var> variable. [[Large Object field processing for non-FILEORG X'100' files|In non-FILEORG X'100' files, a special syntax must be used to Add a LOB field.]]</p>
   
   
====Example====
====Example====
Line 111: Line 111:
MODEL = FOCUS            MODEL = MUSTANG
MODEL = FOCUS            MODEL = MUSTANG
</p>
</p>
<p>
 
You can also add Large Object data to <var class="product">Model&nbsp;204</var>, as shown using the following syntax:</p>
====Usage====
====Large Object field syntax====
<p class="syntax">ADD <span class="term">lob-name</span>=BUFFER,<span class="term">position</span>,<span class="term">length</span> [RESERVE <span class="term">n</span> [BYTES]]
</p>
<p>
Where:</p>
<ul>
<ul>
<li><var class="term">lob-name</var> specifies the field name of the Large Object data.  
<li>The <var>Add</var> statement places an additional occurrence of a field-value pair on the record. </li>
<p class="note"><b>Note: </b>you cannot use subscripts on ADD statements.</p>
 
</li>
<li>You can use the <var>ADD</var> statement to add any field to a record except for a sort or hash key field. You can use this statement only within a <var>For Each Record</var> loop. </li>
 
<li>The <var>Add</var> statement is supported in remote file and scattered group contexts. </li>
 
<li>To use the <var>Add</var> statement with multiply occurring fields, see the [[Processing multiply occurring fields and field groups#ADD statement|ADD statement for multiply occurring fields]]. </li>
 
<li>To add a field whose value you do not explicitly specify, see [[#Using VALUE IN to reference a NOTE value|Using VALUE IN to reference a NOTE value]], below. </li>


<li>BUFFER specifies the Universal Buffer</li>
<li>For Large Object data in non-[[FILEORG parameter|FILEORG X'100']] files, a compiler error is issued for <var>Add</var> (and <var>Store</var>) statements if the context to the right of the equal sign (<tt>=</tt>) is not a <var>BUFFER</var> reference:
<p class="code">M204.0037: Invalid syntax
<li><var class="term">position</var> 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.</li>
</p></li>
<li><var class="term">length</var> is a positive number specifying the length to move. The length can be a %variable or a constant.</li>
<li>RESERVE <var class="term">n</var> 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. </li>
<li>BYTES, optional, specifies that <span class="term">n</span> applies to bytes.
</li>
</ul>
</ul>
<b>Example</b>
<p class="code">BEGIN
IMAGE XYZ
  BUFF_DATA IS STRING LEN 200
END IMAGE
IDENTIFY IMAGE XYZ
<b></b>* 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
<b></b>* 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
</p>
<p>
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:</p>
<p class="code">A99999998E CURREC0
X99999999Z CURREC1
</p>
====Usage====
<p>
The ADD statement places an additional occurrence of a field-value pair on the record.</p>
<p>
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.</p>
<p>
The ADD statement is supported in remote file and scattered group contexts.</p>
<p>
To use the ADD statement with multiply occurring fields, see [[Operations on multiply occurring fields#ADD statement|ADD statement]].          </p>
<p>
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.</p>
<p class="code">M204.0037: INVALID SYNTAX
</p>


==CHANGE statement==
==Change statement==
====Purpose====
<p>
<p>
Alter a record by adding a field and value pair, or altering the value of an existing field within a record. </p>
The <var>Change</var> statement alters a record by adding a field and value pair, or altering the value of an existing field within a record. </p>
   
   
====Syntax====
====Syntax====
<p>
<p>
The basic format of the CHANGE statement is: </p>
The basic format of the <var>Change</var> statement is: </p>
<p class="syntax">CHANGE <span class="term">fieldname</span> [<b></b>= <span class="term">value</span> |(<span class="term">expression</span>)] TO (<span class="term">newvalue</span> |(<span class="term">expression</span>))
{{Template:Change fieldname statement syntax (basic)}}
</p>
<p>
<p>
Where:</p>
Where:</p>
<ul>
<ul>
<li><var class="term">fieldname</var> specifies the name of the field to add to the record, or identify the field where the value is changed.</li>
<li><var class="term">fieldname</var> specifies the name of the field to add to the record, or identify the field where the value is changed. </li>
   
   
<li><var class="term">value</var> is required only if the field has the INVISIBLE attribute. See the discussion on the [[Field attributes#INVISIBLE attribute|INVISIBLE attribute]].     </li>
<li>(<var class="term">subscript</var>) specifies an ordinal number that is used to select a particular occurrence of a multiply occurring field. See the discussion in [[Processing multiply occurring fields and field groups#Subscripts|Subscripts]]. </li>
 
<li><var class="term">value</var> is required only if the field has the <var>INVISIBLE</var> attribute. See the discussion of the [[Field attributes#INVISIBLE attribute|INVISIBLE attribute]]. </li>
   
   
<li><var class="term">newvalue</var> specifies the value that overwrites the existing value for the field.</li>
<li><var class="term">newvalue</var> specifies the value that overwrites the existing value for the field. </li>
   
   
<li>(<var class="term">expression</var>) is resolved by the expression compiler and overwrites the existing value for the field. (<var class="term">expression</var>) 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.  
<li>(<var class="term">expression</var>) is resolved by the expression compiler and overwrites the existing value for the field. (<var class="term">expression</var>) 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. </li>
</li>
</ul>  
</ul>
 
====Usage====
====Large Object field syntax====
<ul>
<p>
<li>You can use the <var>Change</var> statement to change any field in a record except for a sort or hash key field. </li>
You can also change large object fields with the CHANGE statement using the following syntax:</p>
 
<p class="syntax">CHANGE  <span class="term">lob-fieldname,position1,length</span> TO <span class="term">BUFFER position2,length</span>
<li>You can use this statement only within a <var>For Each Record</var> loop. </li>
</p>
 
<li>The <var>Change</var> statement is supported in remote file and scattered group contexts.</li>
 
<li>If a <var>Change</var> 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. </li>
 
<li>To use the <var>Change</var> statement with multiply occurring fields, see the [[Processing multiply occurring fields and field groups#Change statement|Change statement for multiply occurring fields]]. </li>
 
<li>To use the <var>Change</var> statement with fields containing Large Object data, where the assigned value is typically held in a <var>[[Longstrings|Longstring]]</var> variable:
<ul>
<ul>
<li><var class="term">lob-fieldname</var> specifies the name of the field</li>
<li>Use the syntax above <em>only</em> for a <var class="product">Model&nbsp;204</var> V7.5 and later file that is defined with the <var>[[FILEORG parameter|FILEORG]]</var> X'100' bit. </li>
 
<li><var class="term">position, position1</var> and <var class="term">position2</var> are positive numbers specifying the offset of the first character in the buffer or Large Object data. If <var class="term">position, position1,</var> or <var class="term">position2</var> is set to a negative value, an error occurs. Any positions can be a %variable or a constant.</li>
<li>For pre-7.5 or non-FILEORG X'100' files, use the [[Large Object field processing for non-FILEORG X'100' files|special syntax for changing a LOB field.]] </li>
</ul></li>
<li><var class="term">length</var> 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.
<p>
If <var class="term">position</var> plus <var class="term">length</var> 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.</p>
<p>
For example, if the buffer contains "ABCDEFGHIJKL" and the field is initially stored with:</p>
<p class="code">ADD LOB.FLD=BUFFER,1,3 RESERVE 500 BYTES
</p>
<p>
The field would contain "ABC". If it was subsequently changed as follows:</p>
<p class="code">CHANGE LOB.FLD,5,10 TO BUFFER,1,10
</p>
<p>
The field would then contain "ABC ABCDEFGHIJ" with position 4 being a binary zero. </p>
</li>


<li>TO BUFFER specifies the Universal Buffer. You can use BUFFER only in conjunction with a Large Object field.  
<li>To change a field whose value you do not explicitly specify, see [[#Using VALUE IN to reference a NOTE value|Using VALUE IN to reference a NOTE value]], below. </li>
</li>
</ul>
</ul>
====Example====
<p>
Referring to the two sample stored records (see [[#Data used in examples in this topic|Data used in examples in this topic]]), these statements:</p>
<p class="code">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
</p>
<p>
cause <var class="product">Model&nbsp;204</var> to change the value of the COLOR field in each record to BLUE. The two records then appear as:</p>
<p class="code">VIN = A99999998E          VIN = X99999999Z
MAKE = FORD              MAKE = FORD
COLOR = BLUE              COLOR = BLUE
BODY = 2DR                BODY = 2DR
YEAR = 98                YEAR = 04
MODEL = FOCUS            MODEL = MUSTANG
</p>
====Example for Large Object====
<p class="code">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
<b></b>* 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
</p>
<p>
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. </p>
<p class="code">A99999998E This is the data for VIN A99999998E
X99999999Z This is the data for VIN X99999999Z
</p>
====Usage====
<p>
You can use the CHANGE statement to change any field in a record except for a sort or hash key field. </p>
<p>
You can use this statement only within a FOR EACH RECORD loop.                </p>
<p>
The CHANGE statement is supported in remote file and scattered group contexts.</p>
<p>
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. </p>
<p>
To use the CHANGE statement with multiply occurring fields, see [[Operations on multiply occurring fields#CHANGE statement|CHANGE statement]]. </p>
====Changing Large Object fields and data====
<p>
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.</p>
<p>
Extending a LOB field requires that the final length must fit within the RESERVE clause length specified when the LOB field was added.</p>
<p class="note"><b>Note:</b> 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:</p>
<p class="code">M204.2693: SOURCE AND TARGET LENGTH MUST BE EQUAL
</p>
<p>
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:</p>
<p class="code">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
</p>


==DELETE statement==
==Delete statement==
====Purpose====
<p>
<p>
Remove fields from a record. </p>
The <var>Delete</var> statement removes fields from a record. </p>
   
   
====Syntax====
====Syntax====
<p>
<p>
The format of the DELETE statement is:</p>
The format of the <var>Delete</var> statement is:</p>
<p class="syntax">DELETE <span class="term">fieldname</span> [<b></b>= <span class="term">value</span> | (<span class="term">expression</span>)]
<p class="syntax">Delete <span class="term">fieldname</span> [<b></b>= <span class="term">value</span> | (<span class="term">expression</span>)]
</p>
</p>
<p>
<p>
Line 344: Line 187:
<li><var class="term">fieldname</var> specifies the name of the field to remove from the record.</li>
<li><var class="term">fieldname</var> specifies the name of the field to remove from the record.</li>


<li><var class="term">value</var> is required only if the field has the INVISIBLE attribute. (See the discussion in [[Field attributes]].)  </li>
<li><var class="term">value</var> is required only if the field has the <var>INVISIBLE</var> attribute. (See the discussion in [[Field attributes]].)  </li>


<li>(<var class="term">expression</var>) can be used in place of value to specify the resolved value at the time of evaluation. (<var class="term">expression</var>) 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.</li>
<li>(<var class="term">expression</var>) can be used in place of value to specify the resolved value at the time of evaluation. (<var class="term">expression</var>) 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.</li>
</ul>
</ul>
   
   
====Example====
====Example====
<p>
<p>
For example, the request on the next page directs <var class="product">Model&nbsp;204</var> to remove the field BODY from the records retrieved by the FIND.RECS statement.</p>
This request directs <var class="product">Model&nbsp;204</var> to remove the field <code>BODY</code> from the records retrieved by the <code>FIND.RECS</code> statement:</p>
<p class="code">BEGIN
<p class="code">BEGIN
FIND.RECS: FIND ALL RECORDS FOR WHICH
FIND.RECS: FIND ALL RECORDS FOR WHICH
Line 363: Line 206:
<p>
<p>
The records then appear as:</p>
The records then appear as:</p>
<p class="code">VIN = A99999998E          VIN = X99999999Z
<p class="output">VIN = A99999998E          VIN = X99999999Z
MAKE = FORD              MAKE = FORD
MAKE = FORD              MAKE = FORD
COLOR = BLUE              COLOR = BLUE
COLOR = BLUE              COLOR = BLUE
Line 371: Line 214:
   
   
====Usage====
====Usage====
<p>
<ul>
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. </p>
<li>You can use the <var>Delete <i>fieldname</i></var> statement on any field in a record except for a sort or hash key field. This statement can be used only within a <var>For Each Record</var> loop. </li>
<p>
 
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.         </p>
<li>If the <var>Delete <i>fieldname</i></var> statement is applied to a record that does not contain the field to be deleted, no action is taken on that record. </li>
<p>
 
The DELETE fieldname statement is supported in remote file and scattered group contexts.</p>
<li>The <var>Delete <i>fieldname</i></var> statement is supported in remote file and scattered group contexts.</li>
<p>
 
The DELETE fieldname statement supports Large Object data. Processing this statement frees the Table B and Table E data.</p>
<li>The <var>Delete <i>fieldname</i></var> statement supports Large Object data. Processing this statement frees the Table B and Table E data.</li>
<p>
 
To use with multiply occurring fields, see [[Operations on multiply occurring fields#DELETE statement|DELETE statement]]. </p>
<li>To use with multiply occurring fields, see [[Processing multiply occurring fields and field groups#DELETE statement|Deleting multiply occurring fields]]. </li>
 
<li>To use with field groups, see [[Processing multiply occurring fields and field groups#Deleting a field group|Deleting field groups]]. </li>
 
<li>For information about space recovered from deletions and about attempting to delete missing records, see [[#Deleting fields and records|Deleting fields and records]], below.</li>
</ul>
 
==DELETE RECORD statement==
==DELETE RECORD statement==
====Purpose====
<p>
<p>
Remove a record or sets of records from a <var class="product">Model&nbsp;204</var> file. </p>
The <var>DELETE RECORD</var> statement removes a record or sets of records from a <var class="product">Model&nbsp;204</var> file. </p>
   
   
====Syntax====
===Syntax===
<p>
<p>
The format of the DELETE RECORD statement is:</p>
The format of the <var>DELETE RECORD</var> statement is:</p>
<p class="syntax">DELETE RECORD
<p class="syntax">DELETE RECORD
</p>
</p>
   
   
====Example====
===Example===
<p>
<p>
This request deletes all records found by the FIND statement</p>
This request deletes all records found by the <var>FIND</var> statement: </p>
<p class="code">BEGIN
<p class="code">BEGIN
FIND.RECS: FIND ALL RECORDS FOR WHICH
FIND.RECS: FIND ALL RECORDS FOR WHICH
Line 408: Line 254:
</p>
</p>
   
   
====Usage====
===Usage===
<p>
<ul>
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|Reused space]]. </p>
<li>When you delete records with the <var>DELETE RECORD</var> statement, the space those records occupy may be reclaimed depending on the file order. For more information on reclaiming space, refer to [[#Reused space|Reused space]]. </li>
<p>
 
You can use this statement only inside a FOR EACH RECORD loop.</p>
<li>You can use this statement only inside a <var>FOR EACH RECORD</var> loop.</li>
<p>
 
The DELETE RECORD statement is supported in remote file and scattered group contexts. </p>
<li>The <var>DELETE RECORD</var> statement is supported in remote file and scattered group contexts. </li>
 
<li>For information about space recovered from deletions and about attempting to delete missing records, see [[#Deleting fields and records|Deleting fields and records]], below.</li>
</ul>
   
   
====Limitation of the date/time stamp feature deleting records====
====Limitation of the date-time stamp feature deleting records====
<p>
<p>
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.</p>
The [[Adding a date-time stamp field#Overview of the date-time stamp feature|date-time stamp feature]] does not include support for <var>DELETE RECORD</var> or <var>DELETE RECORDS</var> processing. <var>DELETE RECORD</var> or <var>DELETE RECORDS</var> processing must be handled by your application software.</p>
<p>
<p>
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.</p>
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.</p>
 
==DELETE ALL RECORDS statement==
==DELETE ALL RECORDS statement==
====Purpose====
<p>
<p>
Delete sets of records from a <var class="product">Model&nbsp;204</var> file. </p>
The <var>DELETE ALL RECORDS</var> statement deletes sets of records from a <var class="product">Model&nbsp;204</var> file. </p>
   
   
====Syntax====
===Syntax===
<p>
<p>
The forms of this statement are:</p>
The forms of this statement are:</p>
Line 436: Line 283:
</p>
</p>
<ul>
<ul>
<li>The DELETE ALL RECORDS IN statement deletes a set of records located by a FIND statement. </li>
<li><var>DELETE ALL RECORDS IN</var> deletes a set of records located by a <var>FIND</var> statement. </li>
   
   
<li>The DELETE ALL RECORDS ON LIST deletes the set of records on the named list from the file.  
<li><var>DELETE ALL RECORDS ON LIST</var> deletes the set of records on the named list from the file.  
</li>
</li>
</ul>
</ul>
   
   
====Example====
===Example===
<p>
<p>
This request deletes the set of records located by the FIND statement.</p>
This request deletes the set of records located by the <var>FIND</var> statement:</p>
<p class="code">BEGIN
<p class="code">BEGIN
FIND.RECS: FIND ALL RECORDS FOR WHICH
FIND.RECS: FIND ALL RECORDS FOR WHICH
Line 454: Line 301:
</p>
</p>
   
   
====Usage====
===Usage===
<ul>
<li>The <var>DELETE ALL RECORDS</var> statement initiates fewer internal operations and therefore executes faster than the <var>DELETE RECORD</var> statement. However, use the <var>DELETE RECORD</var> statement rather than <var>DELETE ALL RECORDS</var> for records with <var>ORDERED</var> or <var>UNIQUE</var> fields, to ensure that values in the Ordered Index accurately reflect the contents of the data stored in Table B.
<p>
In addition, when records are deleted with <var>DELETE ALL RECORDS IN</var>, 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 <var>DELETE RECORD</var> statement. </p></li>
 
<li>The <var>DELETE ALL RECORDS</var> statement is supported in remote file and scattered group contexts.</li>
 
<li>For information about space recovered from deletions and about attempting to delete missing records, see [[#Deleting fields and records|Deleting fields and records]], below.</li>
</ul>
 
==Deleting fields and records==
<p>
<p>
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.</p>
This section expands on the detail of use for the <var>DELETE</var> statements. Some general issues related to deleting fields and records are presented.</p>
===Reused space===
<p>
<p>
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.   </p>
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.
<var class="product">Model&nbsp;204</var> inserts new records in space recovered from deleted records only on unordered or hash files, or on sort files for which the <var>Reuse Record Number</var> option of the <var>[[FILEORG parameter|FILEORG]]</var> parameter is set active.   </p>
<p>
<p>
The DELETE ALL RECORDS statement is supported in remote file and scattered group contexts.</p>
If the <var>Reuse Record Number</var> option is active for an unordered, hash, or sort file, you must explicitly delete any <var>INVISIBLE</var> fields associated with a record in the file when deleting the record itself. If an <var>INVISIBLE</var> field is not deleted, it becomes part of any new record that is put into the old record's space. </p>
 
===Deleting from a record list===
<p>
Error messages might be generated when a <var>FOR EACH RECORD</var> loop is performed on a list of records from which some of the records have been deleted from the file. For example:</p>
<p class="code">BEGIN
<b></b>*
<b></b>* FIND ALL STATE CONTROL RECORDS
<b></b>*
STATES:    FIND ALL RECORDS FOR WHICH
              REC = STATE
            END FIND
            PLACE RECORDS IN STATES ON LIST FOUND
<b></b>*
<b></b>* EXCLUDE MASS. AND N.H. BECAUSE
<b></b>* THEIR SURCHARGE RATE HAS NOT CHANGED
<b></b>*
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
<b></b>*
<b></b>* CHANGE SURCHARGE RATE FOR ALL OTHER STATES
<b></b>*
SURCHARGE:  FOR EACH RECORD ON LIST FOUND
              CHANGE SURCHARGE RATE TO .50
            END FOR
END
</p>
<p>
This request would produce these messages:</p>
<p class="code"><b></b>*** M204.1266: NONEXISTENT RECORD REFERENCED - 23 IN FILE INSURE
<b></b>*** M204.1266: NONEXISTENT RECORD REFERENCED - 24 IN FILE INSURE
</p>
<p>
Depending upon the intent of the request, these messages may or may not indicate an error. </p>


==FILE RECORDS statement==
==FILE RECORDS statement==
====Purpose====
<p>
<p>
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.        </p>
The <var>FILE RECORDS</var> statement files a set of records that were retrieved by a <var>FIND</var> statement or that were collected on a list. You can reference the set of records in later requests.        </p>
   
   
====Syntax====
===Syntax===
<p>
<p>
The forms of this statement are:</p>
The forms of this statement are:</p>
Line 478: Line 375:
</p>
</p>
   
   
====Usage====
===Usage===
<p>
<ul>
The FILE RECORDS statement adds the pair:</p>
<li>The <var>FILE RECORDS</var> statement adds one of these pairs to the specified records:
<p class="code"><i>fieldname</i> = <i>value</i>
<p class="code"><i>fieldname</i> = <i>value</i>
</p>
<p>
<i>fieldname</i> = (<i>expression</i>)
or</p>
</p></li>
<p class="code"><i>fieldname</i> = (<i>expression</i>)
 
</p>
<li>The <var>FILE RECORDS</var> statement is supported in remote file and scattered group contexts.</li>
<p>
 
to the specified records. </p>
<li>The field used in a <var>FILE RECORDS</var> statement must have the <var>INVISIBLE KEY</var> or <var>INVISIBLE ORDERED</var> field attributes. Refer to [[Field attributes]] for more information.  
<p>
The FILE RECORDS statement is supported in remote file and scattered group contexts.</p>
<p>
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.     </p>
<p>
<p>
In addition, the <var class="term">fieldname</var> = <var class="term">value</var> 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 <var>FILE RECORDS</var> statement, inconsistencies in the file can occur. The <var>FILE RECORDS</var> statement creates new index entries for the
In addition, the <var class="term">fieldname</var> = <var class="term">value</var> 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
<var class="term">fieldname</var> = <var class="term">value</var> pair, eliminating existing references.</p>
<var class="term">fieldname</var> = <var class="term">value</var> pair, eliminating existing references.</p>
<p class="note"><b>Note:</b> The index update generated by a FILE RECORDS UNDER statement is never deferred.</p>
<p class="note"><b>Note:</b> The index update generated by a <var>FILE RECORDS UNDER</var> statement is never deferred.</p></li>
<p>
 
<var class="term">expression</var> is enclosed in parentheses and is one of following expression types: function call, string concatenation, arithmetic operation, User Language construct, or Boolean expression.</p>
<li>The <var class="term">expression</var> 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.</li>
</ul>
   
   
====Example of using an expression====
====Example of using an expression====
Line 542: Line 435:
====Locating filed record sets====
====Locating filed record sets====
<p>
<p>
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:</p>
<var>FIND</var> statements in later requests can locate the filed set of records by using the <var class="term">fieldname</var> = <var class="term">value</var> pair as the retrieval condition. For example, if a set of records were filed with the statement:</p>
<p class="code">SAVE.RECS: FILE RECORDS IN FIND.RECS UNDER SAVE = 1
<p class="code">SAVE.RECS: FILE RECORDS IN FIND.RECS UNDER SAVE = 1
</p>
</p>
<p>
<p>
then:</p>
Then to locate the records again, you use the following statement in the same request or in a later one:</p>
<p class="code">GET.RECS: FIND ALL RECORDS FOR WHICH SAVE = 1
<p class="code">GET.RECS: FIND ALL RECORDS FOR WHICH SAVE = 1
</p>
</p>
<p>
appearing either in the same request or in a later one would locate the records again.  </p>
   
   
====Using lists for filed record sets====
====Using lists for filed record sets====
<p>
<p>
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:     </p>
Two sets of records retrieved by different <var>FIND</var> statements can be filed together under the same <var class="term">fieldname</var> = <var class="term">value</var> pair only if both sets are first placed on a list, and then the list is filed by one statement, as in the following: </p>
<p class="code">BEGIN
<p class="code">BEGIN
FIND.RECS:  FIND ALL RECORDS FOR WHICH
FIND.RECS:  FIND ALL RECORDS FOR WHICH
Line 570: Line 461:
</p>
</p>
<p>
<p>
If the SAVE.DOYLE statement were replaced with:</p>
If the <code>SAVE.DOYLE</code> statement were replaced with:</p>
<p class="code">SAVE.DOYLE: FILE RECORDS IN FIND.RECS UNDER SAVE = T3S
<p class="code">SAVE.DOYLE: FILE RECORDS IN FIND.RECS UNDER SAVE = T3S
</p>
</p>
<p>
<p>
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.</p>
the original references to <code>SAVE = T3S</code> would be lost as soon as the <code>SAVE.LIST</code> was executed. Thus, a second use of the same <var class="term">fieldname</var> = <var class="term">value</var> pair replaces the previous one.</p>
   
   
====Simulating the FILE RECORDS UNDER statement====
====Simulating the FILE RECORDS UNDER statement====
<p>
<p>
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:</p>
You can simulate the <var>FILE RECORDS</var> statement by explicitly adding a <var class="term">fieldname</var> = <var class="term">value</var> pair to a set of records. For example, if the <code>SAVE.LIST</code> statement in the previous example is replaced by:</p>
<p class="code">SAVE.LIST: FOR EACH RECORD ON LIST COMPLIST
<p class="code">SAVE.LIST: FOR EACH RECORD ON LIST COMPLIST
               ADD SAVE = T3S
               ADD SAVE = T3S
Line 584: Line 475:
</p>
</p>
<p>
<p>
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.   </p>
Then the index references to existing records that contain that <var class="term">fieldname</var> = <var class="term">value</var> pair are not invalidated. You are responsible for deleting such references, if deletion is desired. </p>


==STORE RECORD statement==
==<b id="storec"></b>STORE RECORD statement==
====Purpose====
<p>
<p>
Use the STORE RECORD statement to add new records to a <var class="product">Model&nbsp;204</var> file. The <var class="term">fieldname</var>=<var class="term">value</var> pairs that constitute the new record must follow the STORE RECORD statement, one to a line, and must not be labeled. </p>
The <var>STORE RECORD</var> statement is used to add new records to a <var class="product">Model&nbsp;204</var> file. The <var class="term">fieldname</var>=<var class="term">value</var> pairs that constitute the new record must follow the <var>STORE RECORD</var> statement, one to a line, and must not be labeled. </p>
   
   
====Syntax====
===Syntax===
<p>
<p>
The format of the STORE RECORD statement is: </p>
The format of the <var>STORE RECORD</var> statement is: </p>
<p class="syntax">STORE RECORD
<p class="syntax">[<span class="term">label:</span>] [IN <span class="term">filename</span>] STORE RECORD
   <span class="term">fieldname</span> =[<span class="term">value1</span> <span class="squareb">|</span> (<span class="term">expression1</span>)]
   <span class="term">fieldname</span> =[<span class="term">value1</span> <span class="squareb">|</span> (<span class="term">expression1</span>)]
   [<span class="term">fieldname2</span>=[<span class="term">value2</span> <span class="squareb">|</span> (<span class="term">expression2</span>)]]
   [<span class="term">fieldname2</span>=[<span class="term">value2</span> <span class="squareb">|</span> (<span class="term">expression2</span>)]]
       .
       ...
      .
      .
[THEN CONTINUE
[THEN CONTINUE
   <span class="term">statement</span>
   <span class="term">statement</span>
   <span class="term">statement</span>
   <span class="term">statement</span>
       .
       ...]
      .
      .]
END STORE [<span class="term">label</span>]
END STORE [<span class="term">label</span>]
</p>
<p>
Where:</p>
<p>
(<var class="term">expression</var>) 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.</p>
<p>
You can also store Large Object fields to <var class="product">Model&nbsp;204</var>.</p>
<p class="syntax">STORE RECORD
  <span class="term">lob-name</span>=BUFFER,<span class="term">position</span>,<span class="term">length</span> [RESERVE <span class="term">n</span> [BYTES]]
  .
  .
  .
END_STORE [<span class="term">label</span>]
</p>
</p>
<p>
<p>
Where:</p>
Where:</p>
<ul>
<ul>
<li><var class="term">lob-name</var> specifies the field name of the Large Object field.
<li>(<var class="term">expression</var>) 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.</li>
<p class="note"><b>Note: </b>you cannot use subscripts on statements for any field type.</p>
</li>


<li>BUFFER specifies the Universal Buffer</li>
<li><var>THEN CONTINUE</var> maintains the record context of the <var>STORE RECORD</var> statement, while allowing additional work to happen on the record.  <var>THEN CONTINUE</var> is useful for adding variable numbers of multiply occurring fields, and it is <em>required syntax</em> for adding fieldgroups inside a <var>STORE</var> statement.</li>
<li><var class="term">position</var> 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.</li>
<li><var class="term">length</var> is a positive number specifying the length to move. The length can be a %variable or a constant.</li>
   
<li>RESERVE ><var class="term">n</var> 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. </li>
<li>BYTES, optional, specifies that n applies to bytes.  
</li>
</ul>
</ul>
 
====Examples====
===Examples===
<p class="code">BEGIN
<p class="code">BEGIN
STORE RECORD
STORE RECORD
Line 663: Line 524:
END STORE
END STORE
</p>
</p>
 
<b>Storing Large Object data</b>
===Usage===
<p class="code">STORE RECORD
<ul>
  NOVEL=BUFFER,%POSITION,%LENGTH
<li>Use an <var>END STORE</var> statement or another label to end the <var>STORE RECORD</var> statement. Do not end a <var>STORE RECORD</var> statement with an <var>END BLOCK</var> statement.  </li>
[RESERVE n [BYTES]]
 
  AUTHOR_PIC=BUFFER,%POSITION2,%LENGTH2
<li>This form of the <var>STORE RECORD</var> statement is used to add new records to any file that does not have the sorted or hashed option.</li>
END STORE
 
</p>
<li>The <var>STORE RECORD</var> statement is supported in remote file and scattered group contexts.</li>
 
<b>Using an expression</b>
<li>To store a field whose value you do not explicitly specify, see [[#Using VALUE IN to reference a NOTE value|Using VALUE IN to reference a NOTE value]], below. </li>
 
<li>The <var>THEN CONTINUE</var> statement allows for the conditional building of a <var class="product">Model&nbsp;204</var> record. You can use any intervening statements after <var>THEN CONTINUE</var> and before <var>END STORE</var>.
<p>
The statements following the <var>THEN CONTINUE</var> statement of the <var>STORE RECORD</var> block operate as if they were coded within a <var>FRN $Currec</var> block, which immediately follows the <var>END STORE</var> statement. This is easier for coding because you do not need to repeat the file specification of the <var>STORE RECORD</var> statement. It is also more efficient because an actual <var>FRN</var> statement is not necessary.</p></li>
 
<li>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: <br />
<code>M204.2840: STATEMENT HAS TOO MANY INTERMEDIATE RESULTS</code>
 
<p>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.</p>
 
<p>Example:</p>
<p class="code">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</p>
</li>
</ul>
 
<div id="Lob fields"></div>
====Large Object storage====
<!--Caution: <div> above-->
<p>
[[Field design#BLOB, CLOB, and MINLOBE attributes|Binary Large Objects]] &mdash; fields defined with a <var>BLOB</var> or <var>CLOB</var> attribute (often referred to collectively as LOBs) &mdash; are stored in Table E, and they can hold content longer than the 255 limit of regular <var class="product">Model&nbsp;204</var> fields. They are useful for holding blocks of text, images, documents, etc.  </p>
 
<p class="note"><b>Note:</b> Prior to the <var class="product">Model&nbsp;204</var> version 7.5 introduction of the [[FILEORG parameter|FILEORG X'100']] bit, LOB fields required a special <var>STORE</var> syntax referencing the "universal buffer."  This pre-V7.5 syntax is documented in [[Large Object field processing for non-FILEORG X'100' files]].</p>
<p>
As of <var class="product">Model&nbsp;204</var> V7.5, LOB fields can be stored using normal <var>STORE</var> syntax, typically with a [[Longstrings|Longstring]] variable holding the content:</p>
 
<p class="code">B
<p class="code">B
%REC IS STRING LEN 3
%REC IS STRING LEN 3
Line 699: Line 597:
END
END
</p>
</p>
====Usage====
<p>
<p>
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.   </p>
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 &mdash; unless the field is preallocated.
See [[File Load utility#Building a Large Object descriptor|Building a Large Object descriptor]]
for a description of how to build a Large Object data descriptor.</p>
<p>
<p>
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.</p>
The following compiler error is issued when the right side of the equal sign is expected to contain a <var>BUFFER</var> expression and it does not.</p>
<p>
<p class="code">M204.0037: INVALID SYNTAX
The STORE RECORD statement is supported in remote file and scattered group contexts.</p>
</p>
<p>
 
The THEN CONTINUE statement allows for the conditional building of a <var class="product">Model&nbsp;204</var> record. You can use any intervening statements after THEN CONTINUE and before END STORE.</p>
 
<p>
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.</p>
====Sort or hash key files====
====Sort or hash key files====
<p>
<p>
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: </p>
If you are adding a record to a file that has the sort or hash option, the sort or hash key value follows the <var>STORE RECORD</var> on the same line, as shown below: </p>
<p class="syntax">STORE RECORD [<span class="term">sort-or-hash-key-value</span>]
<p class="syntax">STORE RECORD [<span class="term">sort-or-hash-key-value</span>]
</p>
</p>
<p>
<p>
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]].  </p>
The sort or hash key must be provided if the <var>FILEORG</var> parameter was set to indicate that the sort or hash key is required in every record. </p>
<p>
<p>
For example, the request to store a record in a file that requires the vehicle identification number as the sort key can be written:</p>
For example, the request to store a record in a file that requires the vehicle identification number as the sort key can be written:</p>
Line 731: Line 626:
</p>
</p>
<p>
<p>
When this record is stored, the field VIN = A99999998E is added to it.     </p>
When this record is stored, the field <code>VIN = A99999998E</code> is added to it. </p>
<p>
<p>
You can also specify the sort or hash key as an expression:</p>
You can also specify the sort or hash key as an expression:</p>
Line 746: Line 641:
====Files with a UNIQUE field====
====Files with a UNIQUE field====
<p>
<p>
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. </p>
If a record is added to the file that has a <var>UNIQUE</var> field, and a uniqueness conflict is detected during the <var>STORE RECORD</var> processing, the partially stored record is backed out. For files without the <var>Reuse Record Number</var> (<var>RRN</var>) option, this results in the use of a record number which cannot be reclaimed. </p>
   
   
====IN GROUP MEMBER clause====
====IN GROUP MEMBER clause====
<p>
<p>
You can use the IN GROUP MEMBER clause to restrict the STORE RECORD statement to one member file in a group context. See [[Files, groups, and reference context#IN GROUP MEMBER clause|IN GROUP MEMBER clause]] for more information.</p>
You can use the <var>IN GROUP MEMBER</var> clause to restrict the <var>STORE RECORD</var> statement to one member file in a group context. See [[Files, groups, and reference context#IN GROUP MEMBER clause|IN GROUP MEMBER clause]] for more information.</p>
   
   
====FIND ALL VALUES options====
====FIND ALL VALUES options====
<p>
<p>
Like other FIND statements, you can specify a range of values for the FIND ALL VALUES statement by using the FROM and TO clauses. </p>
Like other <var>FIND</var> statements, you can specify a range of values for the <var>FIND ALL VALUES</var> statement by using the <var>FROM</var> and <var>TO</var> clauses. </p>
<p>
<p>
In addition, you can select values based upon a pattern by using the LIKE clause.     </p>
In addition, you can select values based upon a pattern by using the <var>LIKE</var> clause. </p>
 
====Storing field groups (FIELDGROUP)====
<p>
<p>
You can store Large Object data in <var class="product">Model&nbsp;204</var> using a STORE RECORD statement as shown in the following example:</p>
Introduced with <var class="product">Model&nbsp;204</var> version 7.5, field groups add a formalized internal structure for sets of repeating fields &mdash; 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.</p>
====Handling Large Object data====
<p>
<p>
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 &mdash; unless the field is preallocated.
Field groups can be added, changed, and deleted on a record after the record is created, but storing them on the initial <var>STORE RECORD</var> statement <em>requires</em> the use of <var>THEN CONTINUE</var>. The following example demonstrates this, both for fieldgroups and for <em>nested fieldgroups</em> (a fieldgroup contained inside another fieldgroup). Take this set of field definitions:</p>
See [[File Load utility#Building a Large Object descriptor|Building a Large_Object descriptor]]
 
for a description of how to build a Large Object data descriptor.</p>
<p class="code">IN FILE LIBRARY DEFINE FIELDGROUP BOOK
<p>
IN FILE LIBRARY DEFINE FIELDGROUP CHAPTER WITH FG BOOK
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.</p>
IN FILE LIBRARY DEFINE FIELD RECTYPE          WITH ORD CHAR
<p class="code">M204.0037: INVALID SYNTAX
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
</p>
</p>
====Storing Large Object data in segmented fashion====
<p>
<p>
If you have Large Object data that is larger than the greatest amount of data that you want to transport to your application at one time, consider using code similar to the following.</p>
Note that the fieldgroup <code>CHAPTER</code> is defined with an attribute of fieldgroup <code>BOOK</code>, which causes <code>CHAPTER</code> to be nested inside <code>BOOK</code>. To add records to this file, a <var>STORE RECORD</var> statement stores a base record, and then fieldgroups are added inside a <var>THEN CONTINUE</var> clause:</p>
<p class="code">BEGIN
 
IMAGE BLOBDATA
<p class="code">in file library store record 
   ARRAY OCCURS 25
  rectype          = 'AUTHOR'
    SEG IS STRING LEN 255
  author_firstname = 'Jane'
   END ARRAY
   author_lastname  = 'Austen'
END IMAGE
then continue
%DATASIZE=6144
   add fieldgroup book
%TOTAL.LEN=61440
    book_title      = 'Sense and Sensibility'
/?*---------------------------------------------------*?/
    book_subtitle    =  
/? Get a buffer of data %DATASIZE bytes from someplace ?/
    book_isbn        = %isbn(%x)
/? e.g. an IMAGEAssume the blob data has been      ?/
    book_price      = %price(%x)
/? segmented into 6144 byte segments and the complete ?/
    book_readercomment = 'Loved it. Better than "Cats". Mom'
/? blob of 61,440 bytes is contained in ten records in ?/
    book_readercomment = 'An inspired, blistering novelThe New York Times' 
/? an external sequential data set - INPUTDD.           ?/
    book_readercomment = 'Excellent!  GB Shaw'
/?*---------------------------------------------------*?/
  then continue  
READ IMAGE BLOBDATA FROM INPUTDD
    add fieldgroup chapter
%FIRST = 1
        chapter_no    = 1
FRN 56789                      /? GET SOME RECORD       ?/
        chapter_title = 'The Dashwoods of Sussex' 
   REPEAT WHILE $STATUS = 0
    end add 
    WRITE IMAGE BLOBDATA ON BUFFER POSITION 1 MAXLEN 6144
    add fieldgroup chapter 
    IF %FIRST=1 THEN
        chapter_no    = 2
       ADD lob-field-name=BUFFER,1,%DATASIZE -
        chapter_title = 'Mrs. John Dashwood' 
      RESERVE %TOTAL.LEN BYTES
    end add 
    ELSE
    add fieldgroup chapter
      %OFFSET=(%FIRST*%DATASIZE) - %DATASIZE + 1
        chapter_no    = 3
      CHANGE lob-field-name,%OFFSET,%DATASIZE -
        chapter_title = 'Freddy and Elinor' 
         TO BUFFER,1,%DATASIZE
    end add 
    END IF
  end add        
    %FIRST = %FIRST + 1
   add fieldgroup book     
    READ IMAGE BLOBDATA FROM INPUTDD
    book_title      = 'Pride and Prejudice'
  END REPEAT
    book_subtitle    = 
END FOR
    book_isbn        = %isbn(%y)
END
    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                     
</p>
</p>
<p class="note"><b>Note:</b>
In the above example there is no specific "end" statement for the <var>THEN CONTINUE</var> statements. Rather, the context for the continue ends when the containing context ends. In the case above, the context of the outermost <var>THEN CONTINUE</var> is the <var>STORE RECORD</var> statement, which is being continued in order to provide <var>ADD FIELDGROUP</var> statements for field group <code>BOOK</code>. The context for the inner <var>THEN CONTINUE</var> statements &mdash; used to add <code>CHAPTER</code> field groups &mdash; is the <var>ADD FIELDGROUP</var> statement above it. After adding <code>CHAPTER</code> information for a <code>BOOK</code>, that block is ended with <var>END ADD</var>, and <var>STORE RECORD</var> context returns, where if needed, another <code>BOOK</code> field group can be added.</p>
====Nested STORE RECORD statements====
<p>
<p>
Remember when using the RESERVE n BYTES clause that each Large Object data implicitly has the contiguous characteristic.</p>
The <var>THEN CONTINUE</var> block allows for the coding of a nested <var>STORE..END STORE</var> block within the body of the outer <var>STORE</var>, so that related records may be built together. A nested <var>STORE</var> can refer to a different file context, without compromising the file context of the outer <var>STORE</var>. </p>
<p>
So, if you have only a piece of the data that represents only a portion of the entire object, when you add or store the object you must reserve the full amount of contiguous space that the complete object will consume. The STORE or ADD support knows how long the piece of initial data is from the BUFFER reference, but also needs to know what the total length of the complete object will be.</p>
====Support for nested STOREs====
<p>
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. </p>
<p>
<p>
The following example stores an order header record along with an order line record.</p>
The following example stores an order header record along with an order line record:</p>
<p class="code">IN ORDHDR STORE RECORD
<p class="code">IN ORDHDR STORE RECORD
   ORDER_NUMBER = 1000568
   ORDER_NUMBER = 1000568
Line 831: Line 743:
</p>
</p>
<p>
<p>
The results of this would be the following record stored in the ORDHDR file:</p>
The results of this would be the following record stored in the <code>ORDHDR</code> file:</p>
<p class="code">ORDER_NUMBER = 1000568
<p class="output">ORDER_NUMBER = 1000568
CUSTOMER_NUMBER = 111456
CUSTOMER_NUMBER = 111456
ORDER_STATUS = A
ORDER_STATUS = A
</p>
</p>
<p>
<p>
and the following record stored in the ORDLINE file:</p>
and the following record stored in the <code>ORDLINE</code> file:</p>
<p class="code">ORDER_NUMBER = 1000568
<p class="output">ORDER_NUMBER = 1000568
ITEM_ID = F004
ITEM_ID = F004
ITEM_QTY = 3
ITEM_QTY = 3
</p>
</p>
   
   
====Support for multiply occurring fields====
====Multiply occurring fields====
<p>
<p>
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:</p>
In the following example, a <var>For Each Occurrence</var> loop is driven, based on occurrences of the field <code>SALES_MM</code> previously stored, to store occurrences of <code>MONTHLY_TOTAL</code>:</p>
<p class="code">B
<p class="code">B
%MONTHLY_SALES IS FLOAT ARRAY (3)
%MONTHLY_SALES IS FLOAT ARRAY (3)
Line 865: Line 777:
</p>
</p>
<p>
<p>
The resultant record in the SALES file is:</p>
The resultant record in the <code>SALES</code> file is:</p>
<p class="code">RECTYPE = TOT_SALES
<p class="output">RECTYPE = TOT_SALES
SALES_MM = 01
SALES_MM = 01
SALES_MM = 02
SALES_MM = 02
Line 875: Line 787:
</p>
</p>
   
   
====Support for COMMIT and BACKOUT====
====COMMIT and BACKOUT====
<p>
<p>
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:</p>
The <var>COMMIT</var> and <var>BACKOUT</var> statements can be used following a <var>THEN CONTINUE</var> 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:</p>
<p class="code">%CUSTNO = '100639'
<p class="code">%CUSTNO = '100639'
IN ORDERS STORE RECORD
IN ORDERS STORE RECORD
Line 897: Line 809:
   
   
   COMMIT /? Save the customer address ?/
   COMMIT /? Save the customer address ?/
   ADD DELIV_DATE = ($DATECHG('YYYYMMDD',$DATE(1,''),10))
   ADD DELIV_DATE = ($datechg('YYYYMMDD',$DATE(1,&apos;&apos;),10))
   IF %ORDER_DELAYED = 'Y' THEN
   IF %ORDER_DELAYED = 'Y' THEN
       BACKOUT /? Back out deliv date if delay detected ?/
       BACKOUT /? Back out deliv date if delay detected ?/
Line 904: Line 816:
</p>
</p>
<p>
<p>
if %ORDER_DELAYED is not 'Y' then the record is stored as follows:</p>
If <code>%ORDER_DELAYED</code> is not <code>Y</code>, the record is stored as follows:</p>
<p class="code">RECTYPE = ORDER
<p class="output">RECTYPE = ORDER
ORDER_NUMBER = 1000234
ORDER_NUMBER = 1000234
CUSTOMER_NUMBER = 100639
CUSTOMER_NUMBER = 100639
Line 913: Line 825:
</p>
</p>
<p>
<p>
otherwise the DELIV_DATE f-v pair is backed out.</p>
Otherwise, the <code>DELIV_DATE</code> <var class="term">fieldname</var>=<var class="term">value</var> pair is backed out.</p>
 
====Known restrictions or limitations====
====Known restrictions or limitations====
<ul>
<ul>
<li>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.</li>
<li>Be cautious of using the <var>JUMP TO</var> statement following <var>THEN CONTINUE</var> to jump to a label outside the <var>STORE..END STORE</var> block, as this may lead to the storing of a partial record.</li>
   
   
<li>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:  
<li>It is possible to call a subroutine after the <var>THEN CONTINUE</var> statement, as you might in a <var>FOR RECORD NUMBER</var> loop. Additional update statements to the current record are allowed in the subroutine but only in a <var>FRN $Currec</var> loop. Otherwise, record context is not established and any additional updating statements within the subroutine would be rejected with the following compilation error:  
<p class="code">M204.0229: INVALID STATEMENT
<p class="code">M204.0229: INVALID STATEMENT
</p></li>
</p></li>


<li>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:  
<li>A <var>DELETE RECORD</var> statement following <var>THEN CONTINUE</var>, but before <var>END STORE</var>, causes the current record context to be lost. Any further update statements cause the request to be cancelled with one of the following messages:  
<p class="code">M204.1233: DFAV, BAD RECORD NUMBER <i>n</i> FOR FILE <i>filename</i>
<p class="code">M204.1233: DFAV, BAD RECORD NUMBER <i>n</i> FOR FILE <i>filename</i>  
M204.1266: NONEXISTENT RECORD REFERENCED - <i>n</i> IN FILE DSNLIST
M204.1266: NONEXISTENT RECORD REFERENCED - <i>n</i> IN FILE DSNLIST
</p></li>
</p></li>
</ul>
</ul>
<p class="note"><b>Note:</b> 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.</p>
<p class="note"><b>Note:</b> When using <var>THEN CONTINUE</var>, 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.</p>


==UPDATE RECORD statement==
==UPDATE RECORD statement==
====Purpose====
<p>
<p>
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.</p>
The <var>UPDATE RECORD</var> statement improve performances in remote context by using only one network call to perform all of a group of field-level updates (<var>ADD</var>, <var>CHANGE</var>, <var>DELETE</var>) against the current record in a record loop.</p>
   
   
====Syntax====
===Syntax===
<p>
<p>
The syntax of the UPDATE RECORD statement is as follows:</p>
The syntax of the <var>UPDATE RECORD</var> statement is as follows:</p>
<p class="syntax">UPDATE RECORD
<p class="syntax">UPDATE RECORD
  <span class="term">update-statement-1</span>
  <span class="term">update-statement-1</span>
Line 951: Line 860:
Where:
Where:
<p>
<p>
update-statements are one of the following:</p>
An <var class="term">update-statement</var> is one of the following:</p>
<ul>
<ul>
<li>ADD</li>
<li><var>ADD</var></li>
</li>
 
<li>DELETE</li>
<li><var>DELETE</var></li>
</li>
 
<li>CHANGE</li>
<li><var>CHANGE</var></li>
</li>
 
<li>INSERT</li>
<li><var>INSERT</var></li>
</li>
</ul>
</ul>
   
   
====Usage====
===Usage===
<p>
<ul>
The UPDATE RECORD statement, while supported in all reference contexts, is intended for use with Parallel Query Option/204.</p>
<li>The <var>UPDATE RECORD</var> statement, while supported in all reference contexts, is intended for use with <var class="product">Parallel Query Option/204</var>.</li>
<p>
 
If a series of update statements is executed individually, each one requires a separate network call.</p>
<li>If a series of update statements is executed individually, each one requires a separate network call.</li>
<p>
 
All forms of the update-statements are supported. Except, a DELETE EACH statement is not allowed within an UPDATE RECORD statement.</p>
<li>All forms of the update statements are supported. Except, a <var>DELETE EACH</var> statement is not allowed within an <var>UPDATE RECORD</var> statement.</li>
<p>
 
If a field constraint violation occurs, the entire UPDATE statement is backed out.</p>
<li>If a field constraint violation occurs, the entire <var>UPDATE</var> statement is backed out.</li>
<p>
 
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.</p>
<li>If an <var>ON</var> unit invoked during the processing of an <var>UPDATE RECORD</var> statement is run with a <var>BYPASS</var> statement, the processing of the request continues with the statement that follows the <var>END UPDATE</var> statement.</li>
<p>
 
If no updates are found between UPDATE RECORD and END UPDATE, the statement is ignored.</p>
<li>If no updates are found between <var>UPDATE RECORD</var> and <var>END UPDATE</var>, the statement is ignored.</li>
</ul>
   
   
==Deleting fields and records==
==Using VALUE IN to reference a NOTE value==
<p>
This section expands on the detail of use for the DELETE statements. Some general issues related to deleting fields and records are presented.</p>
===Reused space===
<p>
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.
<var class="product">Model&nbsp;204</var> 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 <var>[[FILEORG parameter|FILEORG]]</var> parameter is set active.    </p>
<p>
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.    </p>
===Possible error messages===
<p>
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:</p>
<p class="code">BEGIN
<b></b>*
<b></b>* FIND ALL STATE CONTROL RECORDS
<b></b>*
STATES:    FIND ALL RECORDS FOR WHICH
              REC = STATE
            END FIND
            PLACE RECORDS IN STATES ON LIST FOUND
<b></b>*
<b></b>* EXCLUDE MASS. AND N.H. BECAUSE
<b></b>* THEIR SURCHARGE RATE HAS NOT CHANGED
<b></b>*
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
<b></b>*
<b></b>* CHANGE SURCHARGE RATE FOR ALL OTHER STATES
<b></b>*
SURCHARGE:  FOR EACH RECORD ON LIST FOUND
              CHANGE SURCHARGE RATE TO .50
            END FOR
END
</p>
<p>
would produce these messages:</p>
<p class="code"><b></b>*** M204.1266: NONEXISTENT RECORD REFERENCED - 23 IN FILE INSURE
<b></b>*** M204.1266: NONEXISTENT RECORD REFERENCED - 24 IN FILE INSURE
</p>
<p>
Depending upon the intent of the request, these messages may or may not indicate an error. </p>
 
==Using NOTE values in data maintenance statements==
   
   
===VALUE IN label clause===
===VALUE IN label clause===
<p>
<p>
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]].</p>
The clause <var>VALUE IN <i>label</i></var> can replace an explicit field value in the <var>ADD</var>, <var>CHANGE</var>, and <var>STORE RECORD</var> statements. This also applies to the special forms of these statements that are discussed in [[Processing multiply occurring fields and field groups]].</p>
====Syntax====
<p>
<p>
The forms of the VALUE IN statement are:</p>
The forms of the <var>VALUE IN</var> statement are:</p>
<p class="syntax">ADD <span class="term">fieldname</span> = VALUE IN <span class="term">label</span>
<p class="syntax">ADD <span class="term">fieldname</span> = VALUE IN <span class="term">label</span>
   
   
Line 1,042: Line 900:
</p>
</p>
   
   
====Example====
===Example===
<p>
<p>
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.   </p>
The following request finds all records in the <code>CLIENTS</code> 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 <code>VEHICLES</code> file. The vehicle premium for the policy on the <code>VEHICLES</code> file is then changed to the total premium amount noted for the policy on the <code>CLIENTS</code> file. </p>
<p class="code">BEGIN
<p class="code">BEGIN
FIND.RECS:  IN CLIENTS FIND ALL RECORDS FOR WHICH
FIND.RECS:  IN CLIENTS FIND ALL RECORDS FOR WHICH
Line 1,066: Line 924:
==Storing data in fields==
==Storing data in fields==
   
   
===Storing null values===
===<b id="storNull"></b>Storing null values===
<p>
<p>
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:  </p>
If the new value in an <var>ADD</var>, <var>CHANGE</var>, or <var>STORE</var> 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:  </p>
<p class="code">ADD VEHICLE PREMIUM = ''
<p class="code">ADD VEHICLE PREMIUM = &apos;&apos;
 
CHANGE AGENT TO &apos;&apos;
CHANGE AGENT TO ''
</p>
</p>
<p>
<p>
Line 1,082: Line 939:
</p>
</p>
<p>
<p>
because the old value of FULLNAME is deleted, but no new value is added.</p>
because the old value of <code>FULLNAME</code> is deleted, but no new value is added.</p>


===Using the FIND statement to select fields with null values===
===Using the FIND statement to select fields with null values===
<p>
<p>
You can use the FIND statement to select records that have a field whose value is the null string, as illustrated below:</p>
You can use the <var>FIND</var> statement to select records that have a field whose value is the null string, as illustrated below:</p>
<p class="code">FIND.RECS: IN CLIENTS FIND ALL RECORDS FOR WHICH
<p class="code">FIND.RECS: IN CLIENTS FIND ALL RECORDS FOR WHICH
               FULLNAME = ''
               FULLNAME = &apos;&apos;
END FIND
END FIND
</p>
</p>
<p>
<p>
However, the FIND statement does not select records for a particular field that is missing altogether from the record. See [[Record retrievals#IS PRESENT condition|IS PRESENT condition]] and [[Value loops#Locating records missing a particular field|Locating records missing a particular field]] for examples of finding records without a particular field.  </p>
However, the <var>FIND</var> statement does not select records for a particular field that is missing altogether from the record. See [[Record retrievals#IS PRESENT condition|IS PRESENT condition]] and [[Value loops#Locating records missing a particular field|Locating records missing a particular field]] for examples of finding records without a particular field.  </p>


===Storing values in preallocated fields===
===Storing values in preallocated fields===
<p>
<p>
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. </p>
The file manager can indicate in a field definition the length of the field (<var>LENGTH</var> attribute) and/or the number of times that field can occur in a record (<var>OCCURS</var> attribute). Space for fields with the <var>LENGTH</var> and <var>OCCURS</var> attributes is preallocated in each record in a file, and this space cannot be expanded. </p>
<p>
<p>
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. </p>
If you attempt to store more values (an <var>OCCURS</var> violation) or longer values (a <var>LENGTH</var> violation) than a field's definition permits, an error message is displayed or the request is cancelled. </p>
   
   
====LENGTH violations====
====LENGTH violations====
<p>
<p>
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:         </p>
If a field is defined as having a particular length (<var>LENGTH</var> <i>m</i>), that field can store only values that are between one and <var class="term">m</var> bytes long. Other values are rejected. If you explicitly specify a field name and value in a SOUL statement, as in this request:   </p>
<p class="code">ADD YEAR = 90
<p class="code">ADD YEAR = 90
</p>
</p>
<p>
<p>
<var class="product">Model&nbsp;204</var> 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. </p>
<var class="product">Model&nbsp;204</var> checks the length of the value during the compilation phase. A length violation detected in an update statement (<var>ADD</var>, <var>CHANGE</var>, <var>FILE</var>, or <var>STORE</var>) results in a compilation error. A length violation also can be detected for a <var>STORE</var> statement for a sort or hash key defined with <var>LENGTH</var> <i>m</i>. </p>
<p>
<p>
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#$FLDLEN|$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.                                 </p>
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 <var>[[$FldLen#$FldLen|$FldLen]]</var>. Specifying a field value that is too long for a <var>LENGTH</var> field in a retrieval context always causes the retrieval to fail, because the value could not have been stored. </p>
<p>
<p>
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.             </p>
Attempts to locate invalid values are treated as references to a nonexistent value. For example, a selection criterion in a <code>FIND <i>fieldname</i> = <i>value</i></code> fails to locate any records.   </p>
   
   
====OCCURS violations====
====OCCURS violations====
<p>
<p>
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:</p>
If a field is defined as occurring a particular number of times (<var>OCCURS</var> <i>n</i>), it can be stored up to n times in any record. An attempt to add (using <var>ADD</var> or <var>STORE RECORD</var> 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:</p>
<ul>
<ul>
<li>[[Operations on multiply occurring fields#COUNT OCCURRENCES OF statement|COUNT OCCURRENCES OF statement]] </li>
<li>[[Processing multiply occurring fields and field groups#COUNT OCCURRENCES OF statement|COUNT OCCURRENCES OF statement]] </li>
   
   
<li>[[$OCCURS#$OCCURS|$OCCURS]]  
<li><var>[[$Occurs|$Occurs]]</var>
</li>
</li>
</ul>
</ul>
 
===Storing values in FLOAT fields===
===Storing values in FLOAT fields===
   
   
====Exponent notation====
====Exponent notation====
<p>
<p>
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 [[Record retrievals#Exponent notation|Exponent notation]] for information.             </p>
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 [[Record retrievals#Exponent notation|Exponent notation]] for information. </p>
<p>
<p>
An invalid value is stored as an unconverted string.</p>
An invalid value is stored as an unconverted string.</p>
Line 1,148: Line 1,005:
====Compressed values====
====Compressed values====
<p>
<p>
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.
Fields defined as having the <var>BINARY</var>, <var>OCCURS</var>, and <var>NON-CODED</var> 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 [[Field design (File management)#Data_typing|Data typing]] for additional information on such values. </p>
Refer to [[Field design#Data_typing|Data typing]] for additional information on such values. </p>
   
   
====Value checking====
====Value checking====
<p>
<p>
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. </p>
Values to be stored in <var>BINARY</var> fields are not checked until the request is evaluated. If you attempt to store an incompressible value in a <var>BINARY</var>, <var>OCCURS</var>, and <var>NON-CODED</var> field, the request is cancelled. </p>
   
   
==Updating field groups==
==Updating field groups==
<p class="note"><b>Note: </b>Field groups are supported as of Model 204 version 7.5.</p>
<p class="note"><b>Note:</b> Physical [[Field group (File architecture)|field groups]] are supported as of Model&nbsp;204 version 7.5. </p>
<p>
<p>
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.</p>
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.</p>
Line 1,162: Line 1,019:
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.
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 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 <var>ADD</var> or <var>INSERT FIELDGROUP</var>.
 
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===
===<b id="addFieldgroup"></b>Adding a field group===
To add a field group, issue an ADD FIELDGROUP statement in a record context:
To add a field group, issue an <var>Add Fieldgroup</var> statement in a record context:
<p class="syntax">FR IN LABELX
<p class="syntax">Fr In LabelX
   ADD FIELDGROUP <span class="term">fieldgroupname</span>
   Add Fieldgroup <span class="term">fieldgroupname</span>
       field1 = (<span class="term">value1</span> <span class="squareb">|</span> <span class="term">expression</span>)
       field1 = (<span class="term">value1</span> <span class="squareb">|</span> <span class="term">expression</span>)
       [field2 = (<span class="term">value2</span> <span class="squareb">|</span> <span class="term">expression</span>)
       [field2 = (<span class="term">value2</span> <span class="squareb">|</span> <span class="term">expression</span>)
       ...]
       ...]
   END ADD
   End Add
END FOR
End For
</p>
</p>
The syntax of the <var class="term">fieldname=value</var> pairs inside an ADD FIELDGROUP statement is identical to that of a STORE RECORD statement. See also [[#Inserting a field group|Inserting a field group]].
The syntax of the <var class="term">fieldname=value</var> pairs inside an <var>Add Fieldgroup</var> statement is identical to that of a <var>[[Store Record statement|Store Record]]</var> statement. See also [[#Inserting a field group|Inserting a field group]].
   
   
In place of a value in the <var class="term">fieldname=value</var> pair, you can enter an expression. See [[#Expressions as field name values|Expressions as field name values]] for details.
In place of a value in the <var class="term">fieldname=value</var> pair, you can enter an expression. See [[#Expressions as field name values|Expressions as field name values]] for details.
 
===Inserting a field group===
===Inserting a field group===
In addition to the ADD FIELDGROUP statement, you can also issue an INSERT FIELDGROUP statement:
In addition to the ADD FIELDGROUP statement, you can also issue an <var>INSERT FIELDGROUP</var> statement:
<p class="code">FR WHERE ...
<p class="code">FR WHERE ...
   INSERT FIELDGROUP <var class="term">name</var>(<var class="term">subscript</var>)
   INSERT FIELDGROUP <var class="term">name</var>(<var class="term">subscript</var>)
Line 1,189: Line 1,048:
</p>
</p>
   
   
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 <var>INSERT FIELDGROUP</var> statement has the same semantics as an <var>INSERT FIELD</var> 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.
The inside of the <var>INSERT</var> block has the identical format to the <var>STORE RECORD</var> and <var>ADD FIELDGROUP</var> statements.
   
   
===Deleting a field group===
===Deleting a field group===
Line 1,231: Line 1,090:
====Delete a field group with a subscript====
====Delete a field group with a subscript====
<p>
<p>
To delete the third occurrence of field group PITCHERS, you would issue the following statement:</p>
To delete the third occurrence of field group <code>PITCHERS</code>, you would issue the following statement:</p>
<p class="code">FR IN LABELX
<p class="code">FR IN LABELX
   DELETE FIELDGROUP PITCHERS(3)
   DELETE FIELDGROUP PITCHERS(3)
END FOR
END FOR
</p>
</p>
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:
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 <var>DELETE</var> statement while in a field group context:
<p class="code">FR IN LABELX
<p class="code">FR IN LABELX
   FAO FIELDGROUP PITCHERS
   FAO FIELDGROUP PITCHERS
Line 1,245: Line 1,104:


===Displaying field groups and their fields===
===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:
After you add a field group to a record, you can display the field groups and their fields by issuing a <var>PAI</var> statement, output as follows:
<p class="code">BEGIN
<p class="code">BEGIN
IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100095
IN POLICIES FOR EACH RECORD WHERE POLICY_NUMBER = 100095
Line 1,288: Line 1,147:
/VEHICLE = 8
/VEHICLE = 8
</p>
</p>
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 lines that begin with a backslash (<tt>\</tt>) identify the start of a field group and the lines that begin with a forward slash (<tt>/</tt>) 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 field group IDs are not necessarily in ascending order in a record. The previous example was created by issuing <var>INSERT</var> statements, so the IDs are out of order.
   
   
The new function [[$FIELDGROUPID]] can be used to show the field group ID.
See also:
<ul>
<li>The <var>[[Basic SOUL statements and commands#Print All Fieldgroup Information (PAFGI) statement|PAFGI]]</var> statement to display a field group's content. </li>
 
<li>The function <var>[[$FieldgroupId]]</var> to show the field group ID. </li>
</ul>
   
   
===Support for field group name variables===
===Support for field group name variables===
Line 1,307: Line 1,171:
END FOR</nowiki></p>
END FOR</nowiki></p>
   
   
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:
Field name variables that evaluate to something in an invalid field group context result in request cancellation. For example, if <code>BARD</code> is a field in field group <code>PITCHERS</code> and you issue the following statement:
<p class="code">IN FILE REDSOCKS FRN %RECNO
<p class="code">IN FILE REDSOCKS FRN %RECNO
   %FIELD = &#x2019;BARD&#x2019;
   %FIELD = &#x2019;BARD&#x2019;
   ADD %%FIELD = &#x2019;Knuckle ball&#x2019;
   ADD %%FIELD = &#x2019;Knuckle ball&#x2019;
</p>
</p>
A request cancellation is generated, because you tried to add an occurrence of field BARD outside its appropriate field group context.
A request cancellation is generated, because you tried to add an occurrence of field <code>BARD</code> outside its appropriate field group context.
</div>  <!-- ends toclimit div -->
</div>  <!-- ends toclimit div -->
   
   
[[Category:SOUL]]
[[Category:SOUL]]

Latest revision as of 12:43, 17 August 2019

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:
  • 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:

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.