FOR NEW Statement (CA Datacom/DB Native Access)

Procedure Definition Language Statements › Exclusive Control › FOR NEW Statement (CA Datacom/DB Native Access)

FOR NEW Statement (CA Datacom/DB Native Access)

The FOR NEW statement can insert a new record into a CA Datacom/DB table. This statement uses a native command dataview defined to access the table. The FOR NEW statement is not iterative; to repeat processing of a FOR NEW, you must include it in a looping construct.

This statement has the following format:

<<label>>
     FOR [THE] NEW dataview_name
            statements

   [WHEN DUPLICATE ]
   [    statements ]

   [WHEN ERROR     ] 
   [    statements ]
  ENDFOR

[<<label>>]

An optional 1‑ to 15‑character name of the FOR NEW construct. You can use it to refer to the construct in a QUIT statement.

FOR [THE] NEW

Specifies the action to take to insert or add each new record.

FOR NEW initializes the field values in the new record if the program does not initialize them. The column values are initialized to:

NULL for fields that can have the null value
Zeros for numeric fields
Zero length for variable‑length fields
Spaces for alphanumeric fields
Current time for time fields
Current date for date fields
Current timestamp for timestamp fields

If initial values were specified for the field in the dictionary, they are used.

Note: CA Datacom/DB initializes fields to spaces in the underlying record that are not defined in the dataview without regard to the intended data type of the field. Therefore, dataviews used in FOR NEW should span the entire record.

You can add the reserved word, THE, for readability.

dataview‑name

The name of the dataview that defines the new record inserted. The dataview must be updateable.

statements

PDL statements. Typically, the statements in the scope of the FOR NEW construct are those that place values into the newly created record.

WHEN DUPLICATE (Optional)

The WHEN DUPLICATE clause contains statements that are executed when the key value of a record to add matches the key value of a record existing in the database and when the database does not allow duplicate key field values.

If the WHEN DUPLICATE clause is omitted and duplication is not allowed, control passes to the WHEN ERROR statements when a duplicate record is found. If WHEN ERROR is not coded, control passes to the error procedure.

If the WHEN DUPLICATE clause is included and duplication is allowed, the WHEN DUPLICATE clause is ignored.

Note: Although the file is not updated when a duplicate record is found (the duplicate record is not added), the WHEN DUPLICATE clause does not affect the execution of the statements that precede it. The statements in the WHEN DUPLICATE clause are executed when the duplication is detected at the ENDFOR. At that point, all other statements in the scope of the FOR have already executed. If the FOR construct includes statements that increment counters or set messages, you can correct those values in the WHEN DUPLICATE processing. However, you cannot continue executing the FOR construct.

WHEN ERROR (Optional)

Specifies statements to execute when a dataview error is encountered in the scope of the FOR construct. If WHEN ERROR is not specified, errors are processed by the user‑defined or default error procedure.

The statements specified following a WHEN ERROR clause can access $ERROR functions and should resolve the error with either a PROCESS NEXT or DO ERROR statement. If processing falls through to the ENDFOR, the $ERROR functions are no longer available.

Note: Only dataview errors ($ERROR‑CLASS=DVW) are handled by the WHEN ERROR clause. System and internal errors are handled by the user‑specified or default error processing.

ENDFOR

A reserved word that terminates the FOR construct. If FOR constructs are nested, the most recent unterminated FOR construct is terminated. You can reference any recently added field in the dataview record after ENDFOR unless a QUIT statement is used.

A QUIT in the logical scope of a FOR NEW abandons the creation of the record. Further reference to fields in the dataview outside of the FOR is invalid.

Insertion of a new record into the database occurs at the ENDFOR.

You cannot delete a record defined by the dataview specified in the FOR NEW construct in the logical scope of the FOR NEW construct.

If inserting the new row causes a CA Datacom/DB abnormal error, the WHEN ERROR statements executed. If a WHEN ERROR statement is not coded, the error procedure gets control at the ENDFOR.

You can nest FOR EACH in WHEN DUPLICATE.

If the record contains a SQL DATE, TIME, or TIMESTAMP field, the field is set to the system date, time, or timestamp before it is converted and written to the database.

Example

In the following example, the FOR NEW construct is included in a LOOP construct to process multiple records. Notice that a WHEN DUPLICATE clause is specified to correct the NEW_COUNT total when the duplicate record was not added.

	LOOP UNTIL TRANSCODE = 'Q'
	      TRANSMIT INVEN_PNL
	      FOR THE NEW INVEN_ITEM
    		    MOVE INVEN_PNL TO INVEN_ITEM BY NAME
        		SET NEW_COUNT = NEW_COUNT + 1
	      WHEN DUPLICATE
    		    SET NEW_COUNT = NEW_COUNT ‑ 1
	      ENDFOR
		ENDLOOP