add considerations for SQL

In the context of the SQL query language, the add statement adds a record or set of records to a database.

EGL adds the information based on the values in the key fields of the record variable.

EGL creates an SQL INSERT statement in your generated code based on the values and properties of the SQL record variable in your EGL add statement. For more information about this implicit SQL code, see SQL data access. Alternatively, you can use an #sql directive to write your own embedded SQL INSERT statement (see sql directive).

If the add statement specifies a single SQL record variable, the statement creates a single row in the database. You can also specify a dynamic array of SQL record variables, in which case the statement creates a row for each member of the array.

Syntax


Syntax diagram for the add statement
SQLRecordVariable
Refers to an area of memory that contains a single row of data that you want to add to the database.
with #sql{ sqlStatement }
If you are an experienced SQL programmer, you can override the SQL code EGL generates by embedding your own SQL INSERT statement (see sql directive). Do not leave space between #sql and the left brace.
SQLDynamicArray
The name of a dynamic array of SQL records. The elements are inserted into the database, each at the position that is specified by the element-specific key values. The operation stops at the first error or when all elements are inserted.
Atomic
Support Atomic and Atomic(no).
Atomic means that all rows have to be inserted successfully or the multiple row insert will fail. This is the default.
Atomic(no) means that individual rows within the rowset can be successfully inserted or rejected. You can use GET DIAGNOSTICS to determine which rows were not successful. See get diagnostics considerations for SQL.
Note:
  1. add Atomic is not supported in Informix® if it is not in logging mode, it still adds using the non-atomic way.
  2. add Atomic(no) acts differently across databases. Oracle, DB2® for i5/OS™, SQL Server will not insert the rows once fail happens. Other databases will insert all rows except the fail ones.
Restriction:
For Multiple Row, the add statement does not support an explicit SQL statement.

Implicit SQL INSERT statement

When you specify an SQL record variable or array in the add statement but do not specify an embedded SQL statement with the #sql directive, you create implicit SQL code. The format of the implicit SQL INSERT statement that EGL generates looks like the following code: 
  INSERT INTO tableName
    (column01, ... columnNN)
    values (:recordItem01, ... :recordItemNN)

The key values in the record determine the logical position of the data in the table. The database management system handles records that do not have keys in accordance with the SQL table definition and the rules of the database. The Record part on which you base your record variable associates record fields with columns in the database table. EGL uses these associations to determine the value assignments in the INSERT statement. The use of host variables (variables defined in the language hosting SQL, in this case, EGL), is indicated by an initial colon character. For more information, see Host variables.

If you declared a record field to be read only, the generated SQL INSERT statement does not include that field, 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.

Examples

The following example shows the add statement:

if (userRequest == "A")
   try
      add record1;
   onException(sqlEx SQLException)
      myErrorHandler(12);
   end
end
An example that uses a dynamic array of SQL records is as follows: 
try
   add employees;
onException(sqlEx SQLException)
   sysLib.rollback();
end

Error conditions

The following situations can result in errors:
  • You specify an SQL statement of a type other than INSERT in your #sql directive.
  • You specify some but not all the required clauses of the SQL INSERT statement in your #sql directive.
  • You specify an SQL INSERT statement (or your EGL code generates an implicit SQL statement) that has any of these characteristics:
    • It is related to more than one SQL table.
    • All of your host variables were declared as read only.
    • It is associated with a column that either does not exist or is incompatible with the related host variable.