Understanding Multi-level Insert Operations

In a multi-level insert, multiple mobile business objects are synchronized in a single operation. The mobile business objects must be bound to a JDBC datasource, have a defined relationship, and the insert parameters must support the 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 mobile business objects (MBOs). When you construct these types of MBOs in an offline client application, the primary-key and foreign-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 to the related child MBO creation operations. These types of operations are known as "chained insert" or "multilevel insert."

For database 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.

Typically, in a multi-level insert operation, you:

Create the parent MBO, and indicate the attributes that constitute that MBO's primary key.
Create the child MBO and define a relationship from the parent MBO's primary-key attributes to the child's foreign-key attributes.
Synchronization of the child MBO should occur either independently or through the parent MBO. See the Client Object API documentation for details.
Define the insert operations for the parent and child MBOs.
The insert operation for the parent MBO must return a single row that contains 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 from 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  @@IDENTITY AS id;
```
This batch query inserts the new parent row, and returns 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, and other EIS systems, though the insert-operation definitions differ.

Note:

The name of the from attribute of the insert operation parameter and parameter of the insert operation do not need to be the same name.
The insert query returns only the identity column.
The single row that is returned must contain the column referenced in the relationship between the parent MBO and the child MBO, and the label of the column must match the from attribute name of the parent MBO.

Not all columns in the inserted row are required. For example, not all columns are selected or required for a drag-and-drop database operation.
A multilevel insert records all logs under the parent MBO. All pending actions are also listed under the parent MBO.

Errors may 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 by the parent’s insert operation does not have the required single row with 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.