Accessing Non-SQL-Defined Databases › SQL DML Statements Operating on Non-SQL-defined Records

SQL DML Statements Operating on Non-SQL-defined Records

INSERT

CA IDMS allows INSERT statements where the target table represents a non-SQL-defined record only if all sets with a membership option of AUTOMATIC in which the record participates as a member have been defined with a primary/foreign key declaration.

Note: For more information about primary and foreign keys in set definitions, see the CA IDMS Database Administration Guide.

Additionally, you cannot include the control field of an OCCURS DEPENDING ON structure in the insert column list. On an INSERT, its value is automatically set to 0.

Effect of INSERT on a Record

• Causes an occurrence of the record represented by the table named in the INTO parameter to be stored on the database
• Columns whose values are not supplied on the insert are given standard default values according to their data types (that is, 0 for numeric, spaces for character and binary zeros for binary)
• Has the following effect on system indexes defined on the record:
  • Connects the record into every such index defined as AUTOMATIC
  • Does not connect the record into any index defined as MANUAL
• Connects the record into every set for which the values of all columns representing foreign key fields of the set relationship are not null. The set occurrence to which the record is connected is that owned by the occurrence of the owner record whose primary key value matches the member's foreign key value.
• Returns an error if:
  • In attempting to connect the record into a set, no owner record occurrence can be found with a matching primary key value
  • A null value has been specified for any column other than one representing a nullable foreign key of some set where the record participates as a member
  • An invalid data value is detected during the operation
  • The operation attempts to store a duplicate row when duplicates are not allowed

UPDATE

UPDATE statements where the target table is a non-SQL-defined database record are allowed. Successful execution of an update operation, however, depends on both the definition options chosen and the current state of the database.

The control field of an OCCURS DEPENDING ON structure cannot be updated.

Effect of UPDATE on a Record

• Causes one or more occurrences of the record represented by the table to be modified
• Only fields represented by columns named in the SET parameter of the UPDATE statement for which the update value is not NULL are changed
• Has the following effect on system indexes defined on the record:
  • If a record occurrence being updated is connected to such an index and one or more index key fields are changed, the index is updated.
  • If a record occurrence being updated is not connected to such an index, the index is not updated.
• Has the following effect on sets where the record is a member:
  • If one or more foreign key fields of such a set are changed or are set to NULL, the following operations are performed:
    • The record occurrence is disconnected from its current owner if it participates as a member of the set.
    • The record occurrence is connected to a (new) owner if the value of all foreign key fields are not null and an occurrence of the owner record can be found with a matching primary key.
    • The record's set membership with owner records whose keys are defined in the member record as foreign keys will be affected regardless of the membership options of the set.
  • If one or more sort key fields of such a set are changed, and the record occurrence is a member of the set, the set occurrence is updated to maintain correct ordering.
• Returns an error if:
  • In attempting to connect the record into a set, no owner record occurrence can be found with a matching primary key value
  • The record being modified is an owner of a non-empty set that was defined with a primary/foreign key declaration and one or more of the primary key fields are changed
  • A null value is specified for a column other than one representing a nullable foreign key field of some set where the record participates as a member
  • An invalid data value is detected during the operation
  • The operation attempts to store a duplicate row when duplicates are not allowed

DELETE

DELETE statements where the target table represents a non-SQL-defined record, are allowed. Successful execution of such a statement depends both on the definition options chosen and the current state of the database.

Effect of DELETE on a Record

• Causes one or more occurrences of the record represented by the table to be erased from the database
• Disconnects a record occurrence from all indexes where it participates
• Disconnects a record occurrence from all sets where it participates as a member
• Returns an error if the record occurrence being erased participates as an owner in one or more non-empty sets

SELECT

SELECT statements where one or more tables named in the FROM parameter represent non-SQL defined records are always allowed.

Column values are established as follows:

• If the column represents a nullable foreign key field, its value is:
  • The value of the field in the record occurrence, if the record occurrence is a member of at least one set where the field is a foreign key field
  • Null if the record occurrence is not a member of any set where the field is a foreign key field
• Otherwise, the value of the column is the value of the record element it represents

  An exception is raised if a value in the result table is null and an indicator variable is not specified for the host-variable to which the value is to be returned.