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 multilevel 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 the relationship between the MBOs as a Composite and One to many, and link the Customer table's "id" attribute to Sales_order's "cust_id".
Be sure that the child MBO will be synchronized either independently, or through the parent MBO.
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
('["id"=":id"]',
'["fname"=:fname"]',
'["lname"=":lname"]',
'["address"=":address"]',
'["city"=":city"]',
'["state"=":state"]',
'["zip"=":zip"]',
'["phone"=":phone"]',
'["company_name"=":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
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.
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