In this section: |
The Adapter for Adabas is designed to support SQL update commands for an Adabas data source without the use of remote procedures. These topics describe the methods and rules related to write capability.
Before you can use any commands that write to an Adabas data source, you must make the following changes to the Master and Access Files:
In the Master File:
Note: For the best performance, you can change ACTUAL format Z to ACTUAL format P in the Master File. In this case, you must also change the ALIAS attribute to contain the length and type. If the ALIAS is ff, and the field length is lll, the ALIAS attribute should be coded as:
ALIAS='ff,lll,P'
FIELD=LEAVE_DUE ,ALIAS=AU ,USAGE=P2 ,ACTUAL=P2 ,$
FIELD=LEAVE_DUE ,ALIAS='AU,2,P' ,USAGE=P2 ,ACTUAL=P2 ,$
(FOC4565) IGNORED ATTEMPT TO CHANGE NONUPDATABLE FIELD
In the Access File:
UNQKEYNAME=name
where:
Is the name of the elementary or group field to be used as the unique key.
The UNQKEYNAME attribute does not necessarily define the key described in the ADABAS FDT (the UQ option). The adapter uses it to decide which rules to apply in an INSERT, DELETE, or UPDATE command. If the UNQKEYNAME attribute does not correspond to the key described with the UQ option in the ADABAS FDT, Adabas and the adapter may not agree on whether a segment instance is unique. This can affect the results of INSERT commands. If this attribute is not present, the adapter uses the rules for modifying a segment with a non-unique key or no key. If it is present, the adapter uses the rules for modifying a segment with a unique key. Subsequent sections describe these rules.
When using the Write Adapter for Adabas, the adapter automatically sets the values of the following two options:
The Adapter for Adabas supports the following commands:
Note: Certain types of fields listed in the Master File cannot be updated. For more information, see Fields That Cannot be Updated.
The adapter issues a COMMIT after each INSERT, UPDATE, or DELETE call. If the Adabas process fails, the adapter issues a ROLLBACK. User rollback of the Adabas process is not supported for SQL commands.
While processing SQL transactions, you can issue the PASSRECS command to obtain counts of rows affected by each successfully executed SQL Passthru INSERT, UPDATE, or DELETE command.
ENGINE ADBSINX INT SET PASSRECS {ON|OFF}
where:
Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.
Indicates that the PASSRECS setting in this command will be applied globally to all adapters that support SQL INSERT, UPDATE, and DELETE commands.
Provides the number of rows affected in the application program SCB count member after the successful execution of an SQL Passthru INSERT, UPDATE, or DELETE command. ON is the default value.
Provides no information after the successful execution of an SQL Passthru INSERT, UPDATE, or DELETE command.
In addition, the adapter updates the &RECORDS system variable with the number of rows affected. You can access this variable using Dialogue Manager.
The SQL UPDATE command for the following types of fields is ignored:
The SQL UPDATE command for the following types of fields produces an error:
In a Master File, two or more field declarations can refer to the same Adabas field. Each duplicate field declaration after the first is called a synonym field. Such synonym fields can be used in report commands, but cannot be used in write commands. The following actions occur as a result of using synonyms in write commands:
(FOC4565) IGNORED ATTEMPT TO CHANGE NONUPDATABLE FIELD
INSERT INTO mfname [(field1, field2, ...)] VALUES ('value1','value2', ...)
where:
Is the name of the Master File to use.
Is an optional list of fields to be inserted. If you omit the list, the value list must contain values for all fields in all segments in the Master File as long as the Master File only specifies a single path Adabas file. If the Master File is multi-path, the field list is required and can specify only a single path. Elementary fields not included in the field list have empty values in the data source. The field list can contain only elementary fields. Groups, subdescriptors, superdescriptors, and hyperdescriptors, cannot be used in the field list.
Is the list of values to be inserted. Each alphanumeric value must be enclosed in single quotation marks. If you specify the field name list, the value list only needs to specify, in field name list order, the values for the field names supplied. At a minimum, you must supply all the key fields for all the segments in the path to the target.
If a segment is not present, default values are generated for all fields that are not supplied, and the missing path segments are inserted along with the target segment.
The key field value is used to insert the target segment. If any additional fieldname=value pairs are supplied for a segment in the path, they are used to qualify that path segment. If a segment with ACCESS=ADBS has a unique key or group of keys, only these key fields should be used in the INSERT command. All other fields should be added to the record using the UPDATE command.
If you want to insert an additional record for an existing key field, you must have at least one field in addition to the key field in the field list in order to make the record unique. If you do not, the insert is rejected.
(FOC4564) THIS OCCURRENCE ALREADY EXISTS. USE UPDATE COMMAND
You should use the UPDATE command instead of INSERT in this case.
The UNQKEYNAME attribute in the Access File determines how the adapter presents an INSERT command to Adabas. The UQ option in the ADABAS FDT and the specific field values listed in the INSERT command determine whether Adabas actually inserts the segment instance. The following table describes how these factors affect the result of the INSERT command. Assume that the Access File specifies UNQKEYNAME=EMPLOYEE_ID and that the employee ID value EMPID005 already exists in the data source:
Result of the INSERT Command for Existing EMPLOYEE_ID EDMPID005
EMPLOYEE_ID has the UQ Option in FDT |
Fields in INSERT Command |
Instance Inserted |
---|---|---|
No |
EMPLOYEE_ID only. |
No - rejected duplicate |
Yes |
EMPLOYEE_ID only. |
No - rejected duplicate |
No |
EMPLOYEE_ID plus fields with values that do not already exist. |
Yes |
Yes |
EMPLOYEE_ID plus fields with values that do not already exist. |
No - message (FOC4561), RC=198 |
Result of INSERT Command when EMPLOYEE_ID is not in the field list
UNQKEYNAME = EMPLOYEE_ID |
Instance Inserted |
---|---|
Yes |
No - message (FOC4563) |
No |
Yes, with empty EMPLOYEE_ID value. |
UPDATE mfname SET field1 ='value1' [,field2 ='value2'...] WHERE kfield1 ='kvalue1' [AND kfield2 ='kvalue2'...]
where:
Is the name of the Master File to use.
Are the names of the fields to be updated.
Are the new values for the updated fields, enclosed in single quotation marks.
Are, at a minimum, all of the key fields for all the segments in the path to the target. Only one target segment is updated. You can include additional fieldname=value pairs for the target or any segment in the path. When additional fieldname=value pairs are present for the target segment, these are used for change verification protocol processing. If all target fieldname=value pairs that are present match the Adabas segment field values, the segment is updated with the SET fieldname values. The field list can contain only elementary fields. Groups, subdescriptors, superdescriptors, and hyperdescriptors, cannot be used in the field list.
Are the values that identify the target segment, enclosed in single quotation marks.
DELETE FROM mfname WHERE field1 ='value1' [AND field2 ='value2'...]
where:
Is the name of the Master File to use.
Are, at a minimum, the names of all of the key fields for all the segments in the path to the target. Only one target segment is deleted; Adabas cascades the delete to dependent segments. Additional fieldname=value pairs may be included for the target segment and any segment in the path that has no key or a non-unique key. It is recommended to use Adabas descriptor fields for any additional fieldname=value pairs.
Are the values that identify the target segment to be deleted.
iWay Software |