Creating Multi-level Insert Operations Using Autoincrement Primary Keys

When creating multi-level (chained) insert operations where the primary key of the parent MBO is set to autoincrement, use the "@@identity" parameter in the select statement to provide the chained insert value.

This method of creating a chained insert operation is useful if the primary key is set to autoincrement, you are making the relationship between two related Adaptive Server Enterprise/SQL Anywhere database mobile business objects, and you are creating them from the tool palette within the Mobile Application Diagram.

Create two MBOs (for example, Customer and Sales_order). The Customer table has an "id" column which is a primary key of int type with autoincrement default values (or identify type). Sales_order is another table, which has a column named "cust_id" ( a primary key of int type).
Since "id" is set to autoincrement, each new row added to the table is uniquely identified. ("id" becomes "@@identity" from the first SQL insert statement).
From the Mobile Application Diagram, define a relationship between the MBOs, by linking the Customer table's "id" attribute to Sales_order's "cust_id". Ensure the child MBO is non-syncable, unless you are sure that the child MBO will be synced either independently, or through 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.
Use a create statement to insert into the Sales_order MBO and add a select statement that returns the "@@identity" row, which is the ID used for the chained insert statement into the Customer MBO.

Example: Chained insert SQL statement

INSERT INTO sampledb.dba.customer
(
fname,
lname,
address,
city,
state,
zip,
phone,
company_name)
VALUES
('@OP["fname"=""]',
'@OP["lname"=""]',
'@OP["address"=""]',
'@OP["city"=""]',
'@OP["state"=""]',
'@OP["zip"=""]',
'@OP["phone"=""]',
'@OP["company_name"=""]'
)
SELECT * FROM sampledb.dba.customer WHERE id=@@IDENTITY

Note:

"id" is a primary key column of identity(or autoincrement) type. Notice that the extra select statement and 'id' are not part of the insert statement itself.

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.
Creating Multi-level Insert Operations for Non-autoincrementing Primary Keys
Modify the create statement from the Properties view to support a multi-level (chained) insert operation where the primary key does not autoincrement.

Parent topic: Binding a Database Data Source to a Mobile Business Object