In procedures we have a command called New, to insert records in a table. Using this command, we can assign values to the attributes of one physical table.
It is a low-level command, used to insert a New Record in a table. Since New modifies the database, it is only suitable to Procedure object , not even Transaction object where the insertion happens by other means.
Note: Reading the "GeneXus and Relational Databases: the Essence" paper by Breogán Gonda & Nicolás Jodal is highly recommended. Also, for those who want to learn or refresh knowledge of relational databases (primary key, foreign keys, indexes, referential integrity, etc.) a little distance course "Fundamentals of Relational Databases" is available at the GXtechnical download center.
We have a “Product” transaction for storing the information concerning the products we manage in our system, with its corresponding price list. The structure is as follows:
 |
From this, GeneXus creates two tables: PRODUCT and PRODUCTPRICELIST, each one corresponding to a level in the structure. The last one, PRODUCTPRICELIST, will have three attributes: ProductId, ProductPriceListDate and ProductPriceListPrice, with Primary Key {ProductId, ProductPriceListDate}.
Let's say we want to implement a procedure that: for the product whose code is received as a parameter, it adds the new price (also received as a parameter) to its price list, for the date corresponding to the day the procedure is executed. Therefore, within its rules section, the procedure will include:
Parm( in: &ProductId, in:&price );
|
Then, in Source we will type:
New
ProductId = &ProductId
ProductPriceListDate = &Today
ProductPriceListPrice = &price
EndNew
Note that within the New command there are assignment commands, where each attribute in the table where the record is intended to be inserted is given a value. In this case, we want to insert a record in the PRODUCTPRICELIST table.
See also an in-depth explanation of the concepts behind the example.
New
[Defined by attributeList]
[Blocking NumericExpression]
BodyCode
[ When duplicate
{ AnotherCode |
For each
{att = exp}…
Endfor
| AnotherCode } ]
EndNew
Where:
attributeList
A list of attributes (comma or space separated), used to determine (along with the attributes on the left in an assignment inside the BodyCode) the table where the record will be added.
NumericExpression
The 'blocking factor'. Its presence activates the Block (Batch) insertion mechanism and controls the number of records to be added per block.
BodyCode
A sequence of commands, most of which, if not all, will be assignation of the form:
att = exp
where:
att
Attribute to assign (for the record to be added)
exp
The expression assigned can be: an Attribute, a Variable, a Constant, a Function and an Arithmetic Expression (Date and Numeric type only).
Specify the code to be executed when a duplicate record is detected (when a record with the same primary key or candidate key has already exist in the table). Most of the times an update of some attributes of that existing record is needed. In these cases a For Each command is needed. Inside, the attributes to be updated are assigned. Although not so common, other commands can be executed as well (AnotherCode).
Note: If the Blocking clause exists, then the When Duplicate commands are executed after the insertion of the hole block is tried and a duplicate record is found. In this case, an attempt of insertion is done per each record of the block, being able to successfully insert the record in each case, or having to execute the 'When duplicate' commands. See an in-depth explanation on Block (Batch) insertion mechanism.
The extended table concept is not used here – the command works by adding a single record in a single physical table, determined by the attributes in the BodyCode along with, if exists, the ones in the 'Defined by' clause. Duplicate keys are checked (primary as well as candidate keys). Even If the command has the Blocking clause, it adds a single record in a single table. The blocking clause only works improving the performance, when the New command is inside a repetitive structure. Not looped itself.
If an assign to an attribute that does not belong to the base table is detected within a New, an error is displayed in the Specification report and the program is not generated.
The New command can be within other iterative structures, such as Do while, For Each command, For to step. That allows for batch insertions (here works the Blocking clause to improve the performance). As it is obvious, many New commands could be concatenated one after another as another way to insert multiple records. Also, New commands are nestable.
Every time GeneXus encounters a “new” command, it must identify the table where the records will be inserted. This table is identified on the basis of the attributes that appear within the BodyCode block of the “new” command (not the When Duplicate clause), on the left in an assignment, along with the attributes apperaing in the 'Defined by' clause, if exists, and it’s referred to as the base table of the "new" command. GeneXus will look for a physical table that contains all these attributes. Should such table not exist, upon specification of the procedure, an error message regarding the situation will be displayed in the navigation report and the object will not be generated.
Not neccesarily. It depends on the context what happens to a record attempted to be added, with the attributes of the table not explicitly assigned inside the BodyCode of the New. If, in the context where the New is written:
- The attribute is not instantiated: then will be empty (or null, depending on the corresponding property of the attribute) for the record to be added.
- The attribute is instantiated: the attribute, in the record to be added, will take the value that attribute has in the context, at the moment the insertion is going to happen.
What is meant by context?
- if an attribute is recived as parameter, then, its scope is the hole code. Everywhere is mentioned, or needed, it has its value.
- if a command (like a New) is inside another (that is, is in the scope of the latter), then all the attributes 'instantiated' in the latter, are in the 'context' of the former. For example, if a new command is inside a for each, then all attributes of the for each's extended table are in the context of the new. What does it mean? That if a For each is iterating the Customers table, and after did some calculations, it has a new whithin its code, attempting to insert for the current customer a record in a sumarizing customer table, then, if inside the new there is not an explicit assignment for the CustomerId attribute, the value for it will be taken from the for each (its context).
- The commands specified within the BodyCode block of the New command are not executed for that record.
- The When duplicate command can be used to specify the action to be taken when duplicates are detected. All commands, between the When duplicate and the Endnew, are executed when a Duplicate Key is detected during insertion.
- A GeneXus variable: &ERR, could be created and if so, is set to code = 1.
- A GeneXus variable: &ERRMSG could be created, and if so, is set to a message.
- If the record to be inserted already exists and you want to update data in this record, a For each command must be specified within the 'When duplicate'.
- See the above note about having the 'Blocking clause'.
Depends on the presence of the 'Blocking clause'. If it doen't exist, then the insertion will be performed at the end of the command (when the Endnew is reached). If it does exist, the real insertion will be performed at the end of the block. Suppose the new command has a blocking factor N and is inside a repetitive structure (as a for each). Every time an execution of the for each body is done (and the new inside it), the record of the new is not actually inserted, but is added into a memory block (a buffer) of size N. After that, if the buffer is filled, a special insert (of many rows) is sent to the database, in order to insert the hole block. Then, if some of the N records to be added is found duplicated, the special insertion fails, and a one by one insertion is done, traveling the N block, using the simple insert command.
If you use the Call command or the Udp method function to call another program within a New, the calling is always executed, even if the key already existed.
- Referential Integrity is NOT checked during insertion.
- Redundancy will NOT be automatically maintained during insertion in Procedures. This maintenance is the programmer's responsibility. That is, if a redundancy has been defined for an attribute in the table you are inserting, GeneXus will not search or calculate data to store in that attribute. In case of a Formula Redundancy, the Formula will not be available and the value must be calculated explicitly and assigned to the associated attribute.
Example
When is the Insertion performed?
New
A = 1
B = 1
PCalc.Call()
When Duplicate
For each
B = 2
Endfor
EndNew
The “Calc” procedure is always called even if the key of the record to be inserted already existed in the table and the call command is placed before the When duplicate clause. This behavior occurs because the call command can be used to fetch some values to assign to the attributes of the record to be inserted and these values may determine whether the insertion should be performed or not (duplicated keys).
Objects Procedure object
When duplicate clause
Blocking clause in a 'For each' command