Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » MobiLink - Getting Started » MobiLink Models » Model mode

Modifying how deletes are handled Next Page

Modifying the download subset


Each MobiLink remote database can synchronize a subset of the data in the consolidated database. You can customize the download subset for each table.

The download subset options are:

To change the download subset
  1. In Model mode, open the Mappings tab.

  2. In the Table Mappings pane, select a remote table.

  3. In the Download Subset dropdown list, choose None, User, Remote, or Custom.

  4. If you chose User, Remote, or Custom, open the Download Subset tab in the lower pane.

  5. If you chose User or Remote, the Download Subset tab allows you to identify the column in the consolidated table that contains the MobiLink user names or remote IDs, or to enter a join of tables to obtain the MobiLink user names or remote IDs.

  6. If you choose Custom, the Download Subset tab has two text boxes where you add information to construct a download_cursor script. You do not have to write a complete download_cursor. You only need to add extra information to identify the join and other restrictions on the download subset.

See also
Example (User)

For example, the ULOrder table in CustDB can be shared between users. By default, orders are assigned to the employee who created them. But there are times when another employee needs to see orders created by someone else. For example, a manager may need to see all the orders created by employees in their department. The CustDB database has a provision for this via the ULEmpCust table. It allows you to assign customers to employees. They download all orders for that employee customer relationship.

To see how this is done, first view the download_cursor script for ULOrder without download subsetting. Select the ULEmpCust table in the Mapping tab. Choose timestamp-based download and no download subset. Right-click the table and choose Go To Events. The download_cursor for the table looks like this:

SELECT "DBA"."ULOrder"."order_id",
 "DBA"."ULOrder"."cust_id",
 "DBA"."ULOrder"."prod_id",
 "DBA"."ULOrder"."emp_id",
 "DBA"."ULOrder"."disc",
 "DBA"."ULOrder"."quant",
 "DBA"."ULOrder"."notes",
 "DBA"."ULOrder"."status"
FROM "DBA"."ULOrder"
WHERE "DBA"."ULOrder"."last_modified" >= {ml s.last_table_download}

Now go back to the Mappings tab. Change the Download Subset column for ULOrder to User. Open the Download Subset tab in the lower pane. Select Use a Column in a Joined Relationship Table. For the table to join, select ULEmpCust. For the column to match, select emp_id. The join condition should be emp_id = emp_id.

Right-click the table in the top pane and choose Go To Events. The download_cursor for the table now looks like this:

SELECT "DBA"."ULOrder"."order_id",
   "DBA"."ULOrder"."cust_id",
   "DBA"."ULOrder"."prod_id",
   "DBA"."ULOrder"."emp_id",
   "DBA"."ULOrder"."disc",
   "DBA"."ULOrder"."quant",
   "DBA"."ULOrder"."notes",
   "DBA"."ULOrder"."status"
FROM "DBA"."ULOrder", "DBA"."ULEmpCust"
WHERE "DBA"."ULOrder"."last_modified" >= {ml s.last_table_download} 
AND "DBA"."ULOrder"."emp_id" = "DBA"."ULEmpCust"."emp_id" 
AND "DBA"."ULEmpCust"."emp_id" = {ml s.username}
Example (Custom)

For example, assume you want to subset the download of a table called Customer by MobiLink user and you also want to only download rows where active=1. The MobiLink user names do not exist in the table you are subsetting, so you need to create a join to a table called SalesRep, which contains the user names.

In the Mappings tab, select a Download Type of Timestamp and a Download Subset of Custom for the Customer table. Open the Download Subset tab in the lower pane. In the first box (Tables to Add to the Download Cursor's FROM Clause), type:

SalesRep

In the second box (SQL Expression to Use in the Download Cursor's WHERE clause), type:

SalesRep.ml_username = {ml s.username} 
   AND Customer.active = 1
   AND Customer.cust_id = SalesRep.cust_id

Right-click the table in the top pane and choose Go To Events. The download_cursor for the table now looks like this:

SELECT "DBA"."Customer"."cust_id",
 "DBA"."Customer"."cust_name"
FROM "DBA"."Customer", SalesRep
WHERE "DBA"."Customer"."last_modified" >= {ml s.last_table_download}
 AND SalesRep.ml_username = {ml s.username}
 AND Customer.active = 1
 AND Customer.cust_id = SalesRep.cust_id

The final line of the WHERE clause creates a key join of Customer to SalesRep.