ADELIA |
VADELIA |
SADELIA |
WADELIA |
MADELIA |
(I/B) |
(I/B) (S) |
(B) (S) |
(I/B) (S) |
(I) (S) |
Warning: In the case of ADELIA programs, this instruction is only compatible with the RPG generator.
Section for use
All
Syntax
CREATE_SQL EntityName NullParameter BLOCKParameter MRParameter
NullParameter |
→ |
*NULL_IND | None |
IdentityValueParameter |
→ |
*GET_GEN_VAL | None |
BLOCKParameter |
→ |
*BLOCK(size) ListName | None |
MRParameter |
→ |
*NO_MR | *MR | None |
Description
This instruction creates a record in the entity EntityName, inserting the values of the entity variables matching the fields.
For programs other than ADELIA, you can also check that the record is not locked, by testing it with the *LOCKED reserved word.
The *NULL_IND parameter allows you to take into account the management of NULL values when writing the lines. These values result from a reading operation or from the use of the SET_INDICATOR instruction.
The *GET_GEN_VAL parameter serves to retrieve the last generated identity value of a table with an auto incremented column. The auto increment type can have certain limits depending on the DBMS used. For further information, see topic Data Type Limitations.
The *BLOCK parameter is used to insert records by block (multi-row inserts).
The size is a numeric constant indicating the maximum number of records in the block.
As each record to insert is an element from the ListName list, the entity zones and the columns in the list match up according to their position (the first column on the list corresponds to the entity's first zone, etc.).
If the NULL values are managed, the list must be declared with the *INDIC parameter.
If the list contains more elements than the size of a block, the necessary number of inserts need to be made per block to process all the elements in the list.
When the *BLOCK parameter is indicated, the implicit management rules are not generated.
The *BLOCK parameter is not available for ADELIA-type programs.
The *MR parameter allows you to generate the creation implicit management rules that are linked to the entity, even if the program is generated without the option to generate implicit management rules.
The *NO_MR parameter allows you not to generate the creation implicit management rules that are linked to the entity, even if the program is generated with the option to generate implicit management rules.
After this instruction has been run, it is possible to test the SQL return code with the *SQLCODE reserved word.
In the case of a block insert, a *SQLCODE of 0 with a DBMS other than DB2/400 ensures that all the lines have been processed.
However, certain rows may not have been inserted if they caused a constraint violation (duplicate key usually).
The reserved word *SQLROW_COUNT returns the number of inserted rows. The *SQLROW_COUNT = &NBR_ELT(ListName) test ensures that all the rows in the list were inserted without any errors.
Examples
CREATE_SQL CUSTOMER
VERIFY WIN_UPD_ITEM
BRING_BACK ITEM
CREATE_SQL ITEM
* These lines allow you to:
* - verify a window's layout fields,
* - transfer the layout fields to the file fields of the ITEM entity,
* - create the ITEM record in the database.
Other examples
1.
SET_INDICATOR *TRUE CUSTOMER ADDRESS2_CUST
CREATE_SQL CUSTOMER *NULL_IND
This example allows you to set the NULL value to the ADDRESS2_CUST column when creating a record in the CUSTOMER array.
2.
LIST ItemLst *REF_LDM(ITEMS)
...
CREATE_SQL ITEM *BLOCK(100) ItemLst
This example makes it possible to carry out multi-row inserts. If the list contained 230 elements, 3 SQL INSERTS were executed (2 INSERTs of blocks of 100 records and 1 INSERT of a block of 30 records).