add

The EGL add statement places a record in a file, message queue, or database; or places a set of records in a database.

Syntax diagram for the add statement

record name: Name of the I/O object to add: an indexed, MQ, relative, serial, or SQL record
with #sql{ sqlStatement }: An explicit SQL INSERT statement. Leave no space after #sql.
SQL dynamic-array name: The name of a dynamic array of SQL records. The elements are inserted into the database, each at the position specified by the element-specific key values. The operation stops at the first error or when all elements are inserted.

An example is as follows:

  if (userRequest = "A")
    try 
      add record1;
    onException
      myErrorHandler(12);
    end
  end

The behavior of the add statement depends on the record type. For details on SQL processing, see SQL record.

Indexed record

When you add an indexed record, the key in the record determines the logical position of the record in the file. Adding a record to a file position that is already in use results in the hard I/O error UNIQUE or (if duplicates are allowed) in the soft I/O error DUPLICATE.

MQ record

When you add a MQ record, the record is placed at the end of the queue. This placement occurs because the add invokes one or more MQSeries calls:

MQCONN connects the generated code to the default queue manager and is invoked when no connection is active
MQOPEN establishes a connection to the queue and is invoked when a connection is active but the queue is not open
MQPUT puts the record in the queue and is always invoked unless an error occurred in an earlier MQSeries call

Relative record

When you add a relative record, the key item specifies the position of the record in the file. Adding a record to a file position that is already in use, however, results in the hard I/O error UNIQUE.

The record key item must be available to any function that uses the record and can be any of these:

An item in the same record
An item in a record that is global to the program or is local to the function that is running the add statement
A data item that is global to the program or is local to the function that is running the add statement

Serial record

When you add a serial record, the record is placed at the end of the file.

If the generated program adds a serial record and then issues a get next statement for the same file, the program closes and reopens the file before executing the get next statement. A get next statement that follows an add statement therefore reads the first record in the file. This behavior also occurs when the get next and add statements are in different programs, and one program calls another.

It is recommended that you avoid having the same file open in more than one program at the same time.

SQL record

Some error conditions are as follows:

You specify an SQL statement of a type other than INSERT
You specify some but not all clauses of an SQL INSERT statement
You specify an SQL INSERT statement (or accept an implicit SQL statement) that has any of these characteristics--
- Is related to more than one SQL table
- Includes only host variables that you declared as read only
- Is associated with a column that either does not exist or is incompatible with the related host variable

The result is as follows when you add an SQL record without specifying an explicit SQL statement:

The format of the generated SQL INSERT statement is like this--

  INSERT INTO tableName
    (column01, ... columnNN)
    values (:recordItem01, ... :recordItemNN)

The key value in the record determines the logical position of the data in the table. A record that does not have a key is handled in accordance with the SQL table definition and the rules of the database.
As a result of the association of record items and SQL table columns in the record part, the generated code places the data from each record item into the related SQL table column.
If you declared a record item to be read only, the generated SQL INSERT statement does not include that record item, and the database management system sets the value of the related SQL table column to the default value that was specified when the column was defined.

An example that uses a dynamic array of SQL records is as follows:

  try
    add employees;
    onException
      sysLib.rollback();
  end

Related tasks
Syntax diagram