Understanding Multi-level Insert Operations

A multi-level insert operation (or chained insert operation) allows insert operations for multiple mobile business objects that have a defined relationship during one synchronization, provided the insert operations have parameters that match the MBO relationship.

Some business processes require multiple related enterprise information system (EIS) operations, for example, creating a sales order with line items. The parent/child relationship is often represented by primary key(PK) / foreign key(FK) attributes in the parent and child MBOs. When you construct these types of MBOs in an offline client application, the PK/FK key values are transitory. When EIS operations are called to create real data, the EIS systems generate the actual key values, and the primary key of the parent is copied across to the related child MBO creation operations. These types of operations are known as "chained insert" or "multilevel insert".

For JDBC MBOs using Sybase databases, dragging and dropping a table that contains autoincrement columns (one mechanism for generating primary keys) automatically creates the appropriate operations for obtaining the parent's generated keys and applying them to the children.
For other EIS types (non-Sybase databases, and applications where key generation does not use the autoincrement technique), you must define the insert operations in such a way that allows the child to obtain the generated keys.

Typically in a chained insert operation, you:

Create the parent MBO, and indicate the attributes that constitute that MBO's primary key.
Create the child MBO and draw a relationship from the parent MBO's primary key attributes to the child's foreign key attributes.
When defining multi-level insert operations, ensure the child MBO is non-syncable, unless you are sure that the child MBO will be synced either independently, or through the parent MBO. If not, when the device application designed from these MBOs runs, the child MBOs display on the "Synchronize" screen. If the device application user invokes "Synchronize" on any of the child MBOs, a "Missing-Sync-Param" exception occurs.
Define the insert operations for these two MBOs.
The insert operation for the parent MBO must return a single row, and among the columns it must contain the primary key values. The column labels must match the attribute names of the parent MBO.
With this information, and the relationship mapping data, Unwired WorkSpace modifies the input parameters for the insert operation of the child MBOs by replacing the foreign key attributes with the ones returned by the parent MBO's insert operation. For example:
```
CREATE TABLE parent(pk int autoincrement primary key, p1 varchar(30),...)
CREATE TABLE child(fk int references parent.pk, ...)
```
The parent insert MBO is defined as:
```
INSERT INTO parent(p1, ...) VALUES(?, ...); SELECT * FROM parent WHERE pk = @@IDENTITY;
```
This batch query inserts the new parent row and then returns a single row containing the newly generated primary key value.

You must understand the key generation mechanism used by the EIS application from which you are developing, and be able to determine how to retrieve the newly generated keys during the insert operation (frequently, this logic is wrapped in a stored procedure).

This same technique applies to Web service, SAP, or other EIS systems, though the insert operation definitions differ.

Note:

The from attribute of the insert operation parameter is used to infer the foreign key information of the Insert operation parameter. So the name of the attribute (which is the target of the association from the primary key of the parent) and parameter of the Insert operation need not be the same.
The Insert query returns the complete newly generated row and not just the identity column.
The single row that is returned must contain all of the columns referenced in the relationship from the parent MBO to the child MBO, and the labels of the columns must match the from attribute names of the parent MBO.

All columns from the inserted row are not strictly required. For example, not all columns are selected or required for a drag-and-drop database operation.

Potential errors occur if:

The client sends the parent ID which does not correspond to the server’s interpretation of the parameters of the insert operation.
The customer's primary key consists of more than one attribute.
If the child has multiple foreign key attributes pointing to the parent, the relationship should list all relevant parent-to-child attributes. As long as the row returned from the parent insert contains all those columns, the child insert should work; all the foreign key fields are populated from the parent insert result set.
The insert operation of the parent fails at the back-end.
There is no association relationship between customer and order in which the source attribute/parameter in customer is a primary key and the target parameter in order is a foreign key to customer.
The result set generated in the parent’s insert operation does not have the required single row having the newly created primary key of that operation.

Note: Unwired Server does not report the specific reason of a multilevel insert failure. If you receive errors, or if the insert fails, check each of these items to try and identify the problem.