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.

If you drag and drop an Adaptive Server Enterprise (ASE) or SQL Anywhere database table to create the insert/create operation used in a chained insert operation, the Quick Create wizard creates the mobile business object with default generated operation statements:
  • If the database table has a primary key column that is of autoincrement or identity type, then you do not need to modify the insert and select statements, if the MBO is the parent MBO used later for the chained insert.
  • If the primary key is not autoincrement/identity type, modify the insert statement manually after the MBO is created to perform the chained insert operation.
  1. Drag and drop the data source onto the Mobile Application Diagram. For example, drag and drop a table named customer2 onto the Mobile Application Diagram.
  2. Click OK in the Quick Create wizard to generate the MBO with default values. The following create statement is automatically generated for the database table, and can be viewed and modified in the Properties view, by selecting the create operation in the Mobile Application Diagram, then the Definition tab in the Properties view.
    INSERT INTO sampledb.dba.customer2
(id,
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"]'
)
  3. The primary key column "id" is not autoincrement (or identity type), and you must manually enter the appropriate SQL statement, since the Fill from attribute setting in this case is not supplied automatically. From the Definition tab in the Properties view, click Edit and modify the statement as follows:
    INSERT INTO sampledb.dba.customer2
(id,
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=:id

    This serves as the parent MBO's insert statement in the relationship, which returns the inserted row.

  4. Define the relationship to the second MBO used in the chained insert operation as a Composite and One to many.

    Map the child MBO's foreign key attribute to the parent MBO's primary key, "id" in the above example.

Parent topic: Creating Multi-level Insert Operations Using Autoincrement Primary Keys