3
Synchronization

This document discusses synchronization with the Mobile Server. Topics include:

• Overview

• Mapping Datatypes Between the Oracle Server and Devices

• The Publish and Subscribe Model

• Publish and Subscribe Method Specialized Functions

• Mobile Server System Catalog Views

• Setting Up the Mobile Client on the Device

3.1 Overview

Mobile Server allows new and existing applications and data on handheld devices to be replicated, synchronized, and shared with an Oracle server. Data on the device can be mapped directly to an Oracle8 data server through a replication agent on the Oracle8 database. The Mobile Server uses a publish/subscribe model that manages the data subsetting policy for the mobile devices. Data on the device is mapped to Oracle8 through transports by HTTP.

The Message Generator and Processor (MGP) is a Java background process that uploads transactions from the applications running on the client devices. The MGP then applies the transactions to Oracle databases. It also generates new updates (data) for the client devices to download. Please refer to the Oracle9i Lite Publishing, Managing, and Deploying Guide for more information concerning administration of the MGP.

3.1.1 Publication and Subscription

The Mobile Server uses publications and subscriptions. Publications are (optionally parameterized) queries that are defined against Oracle8 tables or views. A publication is subscribed to by one or more users. The Mobile Server tracks users for devices. Through established subscriptions, the Mobile Server prepares any new data for each client device. Data can be partitioned either horizontally by rows, or vertically by columns. Only the required subset of data is downloaded to each client.

In the Mobile Server publish and subscribe model, each publication can contain multiple publication items. A publication item typically maps to a table on the device. You can rename table columns within publication items. Each publication item can contain multiple parameters. See Section 3.3, "The Publish and Subscribe Model" for more information.

3.1.2 Client Device Database DDL Operations

The first time a client device replicates, the Mobile Server automatically creates the database objects on the client device. By default, the primary key index of a table is automatically replicated from the server. You can create secondary indexes on the device through a publication item. If you do not want the primary index, you must explicitly drop it from the publication items. See the Consolidator Admin API Specification for specific API information.

3.1.3 Database Support

On the server side, the Mobile Server works against Oracle8. On the client side, the Mobile Sync supports replication with the Oracle Lite database on any platform supported by Oracle Lite.

3.1.4 Binding User-Defined PL/SQL Packages

The Mobile Server synchronization process can be customized in many ways. You can attach application logic to the Mobile Server by binding PL/SQL packages to publication items. The packages must expose the BeforeCompose, AfterCompose, BeforeApply, and AfterApply methods. The Mobile Server calls these methods before and after it:

• Applies client changes to server tables on behalf of Mobile Syncs.

• Composes fast-refresh changes for a given publication item.

The Mobile Server passes the current Mobile Sync user to these methods.

User-defined PL/SQL packages can cache or pre-compute data. They can also resolve foreign key constraint violation problems. See Section 3.1.7, "Foreign Key Constraints in Updatable Publication Items" for more information. See Section 3.4.1, "Callback Customization Using Compose and Apply" for details on using these calls.

3.1.5 Flexible Naming

Special characters including spaces are supported in client, table, view, and column names. Callers must provide object names to the Mobile Server exactly as they are stored in the database (in most cases, using uppercase letters).

3.1.6 Fast Refresh and Update Operation for Views

The Mobile Server supports fast refresh and update operations for complex multiple table publications that meet specific criteria.

3.1.6.1 Updatable Parent Tables

For a view to be updatable, it must have a parent table. A parent table contains any of the view's base tables in which a primary key is included in the view's column list and is unique in the view's row set. If you want to make a view updatable, you must provide the Mobile Server with the appropriate hint and the view's parent table before you create a publication item on the view.

3.1.6.2 Using Parent Table Hints and INSTEAD OF Triggers

To make view-based publication items updatable, you must use the following two mechanisms:

• Parent table hints

• INSTEAD OF triggers

Parent table hints define the parent table for a given view. Parent table hints are provided through the ParentHint function in the Mobile Server Admin API. See "Parent Hint" in the Mobile Server Admin API Specification, for more information.

INSTEAD OF triggers are used to execute INSTEAD OF INSERT, INSTEAD OF UPDATE, or INSTEAD OF DELETE commands. INSTEAD OF triggers also map these DML commands into operations that are performed against the view's base tables. INSTEAD OF triggers are a function of Oracle8. See the Oracle8 documentation for details on INSTEAD OF triggers.

3.1.6.3 Fast Refresh for Views

Publication items are created for fast refresh by default. Under fast refresh, only incremental changes are replicated. The advantages of fast refresh are reduced overhead and increased speed when replicating data stores with large amounts of data where there are limited changes between synchronization sessions.

The Mobile Server performs a fast refresh of a view if the view meets the following criteria:

• Each of the view's base tables must have a primary key.

• All primary keys from all base tables must be included in the view's column list.

• If the item is a view, and the item predicate involves multiple tables, then all tables contained in the predicate definition must have primary keys and must have corresponding publication items.

The view requires only a unique primary key for the parent table. The primary keys of other tables may be duplicated. For each base table primary key column, you must provide the Mobile Server with a hint about how this column is called in the view. You can accomplish this by using PrimaryKeyHint in the Mobile Server Admin API. See the Consolidator Admin API Specification for more information.

3.1.6.4 Complete Refresh for Views

Publication items can be created for complete refresh using the CompleteRefresh call from the Mobile Server Admin API. When this mode is specified, client data is completely refreshed with current data from the server after every sync. An administrator can force a complete refresh on an entire publication via an API call. The complete refresh function forces complete refresh of a publication for a given client. See the Consolidator Admin API Specification for more information.

3.1.7 Foreign Key Constraints in Updatable Publication Items

Replicating tables between Oracle8 and client devices in updatable mode can result in foreign key constraint violations if the tables have referential integrity constraints. When a foreign key constraint violation occurs, the server rejects the client transaction.

3.1.7.1 Foreign Key Constraint Violation Example

For example, two tables EMP and DEPT have referential integrity constraints. The DeptNum (department number) attribute in the DEPT table is a foreign key in the EMP table. The DeptNum value for each employee in the EMP table must be a valid DeptNum value in the DEPT table.

A Mobile Server user adds a new department to the DEPT table, and then adds a new employee to this department in the EMP table. The transaction first updates DEPT and then updates the EMP table. However, the database application does not store the sequence in which these operations were executed.

When the user replicates the Mobile Server updates the EMP table first. In doing so, it attempts to create a new record in EMP with an invalid foreign key value for DeptNum. Oracle8 detects a referential integrity violation. The Mobile Server rolls back the transaction and places the transaction data in the Mobile Server error queue. In this case, the foreign key constraint violation occurred because the operations within the transaction are performed out of their original sequence.

3.1.7.2 Avoiding Constraint Violations with BeforeApply and After Apply

You can avoid foreign key constraint violations based on out-of-sequence operations by using DEFERRABLE constraints in conjunction with the BeforeApply and AfterApply functions. DEFERRABLE constraints can be either INITIALLY IMMEDIATE or INITIALLY DEFERRED. The behavior of DEFERRABLE INITIALLY IMMEDIATE foreign key constraints is identical to regular immediate constraints. They can be applied interchangeably to applications without impacting functionality.

The Mobile Server calls the BeforeApply function before it applies client transactions to the server and calls the AfterApply function after it applies the transactions. Using BeforeApply function, you can set constraints to DEFFERED to delay referential integrity checks. After the transaction is applied, call the AfterApply function to set constraints to IMMEDIATE. At this point, if a client transaction violates referential integrity, it is rolled back and moved into error queues.

To prevent foreign key constraint violations using DEFERRABLE constraints:

1. Drop all foreign key constraints and then recreate them as DEFERRABLE constraints or use a setConstraint[s] statement to set them as DEFERRABLE.

2. Bind user-defined PL/SQL packages to publications that contain tables with referential integrity constraints.

3. Define DEFERRED constraints in the BeforeApply function and IMMEDIATE constraints in the AfterApply function as follows:

        procedure BeforeApply(clientname varchar2) is
        cur integer;
        begin
          cur := dbms_sql.open_cursor;
          dbms_sql.parse(cur,'SET CONSTRAINT SAMPLE3.address14_fk
                          DEFERRED', dbms_sql.native);
          dbms_sql.close_cursor(cur);
        end;
        procedure AfterApply(clientname varchar2) is
        cur integer;
        begin
          cur := dbms_sql.open_cursor;
          dbms_sql.parse(cur, 'SET CONSTRAINT SAMPLE3.address14_fk
                          IMMEDIATE', dbms_sql.native);
          dbms_sql.close_cursor(cur);
        end;
   

3.1.7.3 Avoiding Constraint Violations with Table Weights

Table weight is an integer property of association between publications and publication items. Mobile Server uses table weight to determine which order to apply Client Operations to master tables as follows:

1. Client INSERT operations are executed first, from lowest to highest table weight order.

2. Client DELETE operations are executed next, from highest to lowest table weight order.

3. Client UPDATE operations are executed last, from lowest to highest table weight order.

In the example listed in Section 3.1.7.1, "Foreign Key Constraint Violation Example", a constraint violation error could be resolved by assigning DEPT a lower table weight than EMP. For example:

(DEPT weight=1, EMP weight=2)

3.1.8 Replication Errors and Conflicts

With the Mobile Server, a compatibility with Oracle8 advanced replication error occurs when the client updates a row at the same time that the server deletes it. All other errors, such as nullity violations or foreign key constraint violations, are replication errors.

The Mobile Server does not automatically resolve replication errors. Instead, the Mobile Server rolls back corresponding transactions, and moves transaction operations into the Mobile Server error queue. Later, Mobile Server database administrators can change these transaction operations and re-execute or purge the transactions from the error queue.

A Mobile Server replication conflict occurs if:

• The client and the server update the same row.

• The client and server create rows with the same primary key values.

• The client deletes the same row that the server updates.

See Section 3.4.5, "Resolving Conflicts Using the Error Queue" for more information on conflict resolution techniques.

3.1.8.1 Versioning

The Mobile Server uses internal versioning to detect replication conflicts. A version number is maintained for each client record as well as for each server record. When a client's changes are applied to the server, the Mobile Server will detect version mismatches and resolve conflicts according to winning rules.

3.1.8.2 Winning Rules

The Mobile Server uses winning rules to automatically resolve replication conflicts. The following winning rules are supported:

• Client wins

• Server wins

When the client wins, the Mobile Server automatically applies client changes to the server. When the server wins, the Mobile Server automatically composes changes for the client.

You can customize the Mobile Server's conflict resolution mechanism by setting the winning rule to "Client Wins" and attaching BEFORE INSERT, UPDATE, and DELETE triggers to database tables. The triggers compare old and new row values and resolve client changes as specified.

3.2 Mapping Datatypes Between the Oracle Server and Devices

The Oracle8 and Oracle9i Lite tables that the Mobile Server synchronizes must use compatible datatypes. Oracle8 datatypes are compatible with Oracle9i Lite datatypes.

3.2.1 Oracle Lite Datatypes

All Oracle9i Lite-based snapshots are created by the Mobile Sync during replication. The Mobile Server automatically selects Oracle9i Lite datatypes depending on data precision in Oracle8. The following table shows the data conversion. Oracle datatypes appear in the left column and Oracle9i Lite datatypes appear in the top row. "X" indicates unconditionally supported and "-" indicates not supported. Datatypes 1B, 2B, and 4B are specifically OKAPI datatypes. See the Oracle9i Lite Object Kernal API Reference for more information.

Table 3-1 Oracle Lite Datatypes

	1B	2B	4B	FLOAT	DOUBLE	DATETIME	LONG- VARBINARY	VARCHAR
INTEGER	X	X	X	X	X	-	-	-
VARCHAR2	-	-	-	-	-	-	-	X
VARCHAR	-	-	-	-	-	-	-	X
CHAR	-	-	-	-	-	-	-	X
SMALLINT	X	X	X	X	X	-	-	-
FLOAT	X	X	X	X	X	-	-	-
DOUBLE PRECISION	X	X	X	X	X	-	-	-
NUMBER	X	X	X	X	X	-	-	-
DATE	-	-	-	-	-	X	-	-
LONG RAW	-	-	-	-	-	-	X	-
BLOB	-	-	-	-	-	-	X	-

3.3 The Publish and Subscribe Model

The Mobile Server uses a publish and subscribe model to centrally manage data distribution between Oracle servers and handheld devices. The publish and subscribe model can be implemented programmatically to invoke certain specialized features using the Mobile Server Admin API and the Consolidator Admin API both of which are part of the Mobile Server. This model includes the following:

Table 3-2 Publish/Subscribe Model Elements

Item	Description
publication	A publication is a group of publication items.
publication item	A publication item is a SQL select statement that specifies which data subset a user can access. A publication item usually corresponds to a replica table on the client device.
subscription	A subscription associates a user with a publication and may contain subscription parameters.
user	A user is defined by a user name and a password. The Mobile Server synchronizes data according to the client's subscriptions. A user can use a single user name to synchronize data stored on multiple devices. When the user changes devices, the Mobile Server performs a complete refresh of all the user's subscriptions on the new device. A user can use multiple user names to synchronize data on a single device. When the user attempts to change user names, the Mobile Server ignores all client updates and performs a complete refresh of all the client's subscriptions.
subscription parameter	A subscription parameter uses names and string values to define an individual client's subscription to an individual publication. Subscription parameters enable clients to perform data subsetting, and they restrict the number of rows assigned to each client. Typical subscription parameters can include user names and area codes.

To implement the Mobile Server publish/subscribe model, perform the following:

1. Connect to Mobile Server.

2. Create users.

3. Create publications.

4. Create publication items and set winning rules.

5. Add publication items to publications.

6. Create publication item indexes as required.

7. Create sequences.

8. Subscribe users to publications.

9. Partition sequences for clients.

10. Define user subscription parameters to publications.

11. Instantiate subscriptions.

You can implement the Mobile Server publish/subscribe model by using one of the following:

Table 3-3 Methods to Implement Publish and Subscribe Model

Implementation Method	Definition
Packaging Wizard	The preferred method. See Section 4, "Sample Orders Application" for more information.
Pure Java methods	Mobile Server uses the Consolidator Admin and Mobile Server Admin API executed from a Java program using pure Java methods.

3.3.1 Connecting to Mobile Server

This section describes how to connect to the Mobile Server using Java methods.

Java Methods Example

ResourceManager.openConnection("MOBILEADMIN", "MANAGER");

3.3.2 User Functions of the Mobile Server Admin API

This section describes how to create a user, change a password, and drop a user using the Mobile Server Admin API.

3.3.2.1 Create User

You can create Mobile Server users with the CreateUser() function which has the following syntax:

public static boolean createUser(String userName, String password, String 
fullName, String privilege);

The following example creates a user "MOBILE" with the parameters listed in the table:

Table 3-4 Create User Example Parameters

Parameter	Value	Definition
userName	"MOBILE"	Specifies user name for mobile client.
password	"MOBILE"	Specifies the password for user name MOBILE.
fullName	"MOBILEUSER"	Specifies the full name for user MOBILE.
privilege	"C"	Specifies that this user can connect to Mobile Server.

Java Methods Example

ResourceManager.createUser("MOBILE","MOBILE","MOBILEUSER","C");

3.3.2.2 Change Password

You can change passwords for Mobile Server users with the SetPassword() function which as the following syntax:

public static void setPassword(String userName, String newpwd);

The following example changes the password for the user "MOBILE" :

Table 3-5 Set Password Example Parameters

Parameter	Value	Definition
userName	"MOBILE"	Specifies user name for mobile client.
newpwd	"MOBILENEW"	Specifies the new password for the mobile client.

Java Methods Example

ResourceManager.setPassword("MOBILE","MOBILENEW");

3.3.2.3 Drop User

You can drop existing Mobile Server users with the dropUser() function using the following syntax:

public static void dropUser(String userName);

The following example drops the user "MOBILE":

Table 3-6 Drop User Example Parameters

Parameter	Value	Definition
userName	"MOBILE"	Specifies user name for mobile client.

Java Methods Example

ResourceManager.dropUser("MOBILE");

3.3.3 Creating Publications

This section describes how to create a publication.

3.3.3.1 Defining Publication Items

Publication item names are limited to twenty-six characters and must be unique across all publications. Publication items can be defined for both tables and views. When publishing an object, the user is required to specify the publication item in which the object resides. When publishing Updatable multi-table views, there are certain restrictions that apply:

• The view must contain a parent table with a primary key defined.

• INSTEAD OF triggers must be defined for DML operations on the view.

• All base tables of the view must be published.

3.3.3.2 Data Subsetting

When creating publication items, the user can define a parameterized select statement with a character limit of up to an 8k. Subscription parameters can be specified at this time, and will be used during replication to limit the data published to each client. String substitution is used to replace the values of the parameters when a user subscribes.

Using the Consolidator Admin API

You can create publications using the CreatePublication function, which has the following syntax:

public static void CreatePublication(String name, int client_storage_type,
   String client_name_template, String enforce_ri) throws ThrowableConsolidator

The following example creates a publication named "T_SAMPE1" with the parameters listed in the table:

Table 3-7 Create Publication Sample Parameters

Parameter	Value	Definition
client_storage_type	Consolidator.OKPI_EPOC	A constant which defines the platform type. See the Oracle9i Lite Mobile Server Admin API Specification for more information.
client_ name_template	'%s'	The default.
enforce_ri	null	This parameter is always NULL.

Java Methods Example

Consolidator.CreatePublication("T_SAMPLE1", Consolidator.OKPI_EPOC, "%s", null); 

3.3.4 Creating Publication Items

This section describes how to create publication items.

Using the Consolidator Admin API

You can create publication items using the CreatepublicationItem function, which has the following syntax:

public static void CreatePublicationItem(String name, String owner, 

String store, String refresh_mode, String select_stmt, String cbk_owner, 
String cbk_name) throws Throwable

The following examples create a publication item named P_SAMPLE1 with the parameters listed in the table:

Table 3-8 Create Publication Item Sample Parameters

Parameter	Value	Definition
owner	SAMPLE1	Specifies that SAMPLE1 is the base object owner.
store	ADDROLRL4P	Specifies that ADDROLRL4P is the base object name.
refresh_mode	F or C	Defines the refresh mode as fast or complete. See Section 3.1.6, "Fast Refresh and Update Operation for Views" for more information.
select_stmt	See the examples.	Selects data from the specified columns in sample1.addrolrl4p
cbk_owner	null	Specifies the callback package owner as NULL. For more information, see Section 3.4.1, "Callback Customization Using Compose and Apply".
cbk_name	null	Specifies the callback package name as NULL. For more information, see Section 3.4.1, "Callback Customization Using Compose and Apply".

Java Methods Example

Consolidator.CreatePublicationItem("P_SAMPLE1", "SAMPLE1", "ADDROLRL4P", "F" ,
   "SELECT LastName,FirstName, company, phone1, phone2, phone3, phone4,
    phone5, phone1id, phone2id, phone3id, phone4id, phone5id, displayphone,
    address, city, state, zipcode, country, title, custom1, custom2, custom3,
    custom4, note
FROM sample1.addrolrl4p" + " WHERE upper(company) > " + ":COMP", null, null);

3.3.4.1 Null Sync Callout

Mobile Server makes a callout during synchronization indicating whether the client device is attempting a null sync. A null sync refers to the fact that the client has no changes to upload. This callout can be implemented by creating a PL/SQL package within the Mobile Server repository. The package must have the following specification:

create or replace package CUSTOMIZE as procedure

NullSync(p_Client IN varchar2, p_NullSync as boolean);
end CUSTOMIZE;

3.3.5 Retrieving Publication Item Names

This section describes how to retrieve publication item names.

Using the Mobile Server Admin API

public static String getPublicationItemName(String Publication Item, int 
platform);

The constant int platform identifies the platform for which the snapshot was created. The constant argument is "ResourceManager." followed by "WTG", "PALM", "EPOC", or "WINCE" depending on the platform. See the Mobile Server Admin API Specification for more information.

The following example returns a publication item called "DEPT" for an EPOC application.

Table 3-9 Get Publication Item Name Example Parameters

Parameter	Value	Definition
PublicationItem	DEPT	String name identifying a snapshot or table.
platform	ResourceManager.PALM	Constant representing the platform type for which the snapshot was created.

Java Methods Example

ResourceManager.getPublicationItemName("DEPT",ResourceManager.EPOC);

3.3.6 Creating Publication Item Indexes

The Mobile Server supports automatic deployment of indexes in Oracle Lite databases on client devices. The Mobile Server automatically replicates primary key indexes from the server database. The Consolidator Admin API provides calls to explicitly deploy unique, regular, and primary key indexes to client devices as well.

Using the Consolidator Admin API

You can deploy a publication item index with the CreatePublicationItemIndex function, which has the following syntax:

public static void CreatePublicationItemIndex(String name,
                    String publication_item, String pmode,
                    String columns) throws Throwable

The examples in this section create a publication item index named INDEX001 for the publication item PSAMPLE1. The publication item index is a regular index which includes the ZIPCODE column as defined in the table:

Table 3-10 Create Publication Item Index Sample Parameters

Parameter	Value	Definition
name	INDX001	Defines INDX001 as the publication item to be created.
publication_item	P_SAMPLE1	Defines P_SAMPLE1 as the index's publication item.
pmode	I	Defines the index mode as regular.
columns	ZIPCODE	Includes the ZIPCODE column in the index.

Java Methods Example

Consolidator.CreatePublicationItemIndex("INDX001", "P_SAMPLE1", "I", "ZIPCODE");

3.3.6.1 Define Client Indexes

Client-side indexes can be defined for existing publication items. There are three types of indexes that can be specified:

• P - Primary key

• U - Unique

• I - Regular

Note: When an index of type 'U' or 'P' is defined on a publication item, there is no check for duplicate keys on the server. If the same constraints do not exist on the base object of the publication item, Mobile Sync may fail with a duplicate key violation. See the Consolidator Admin API Specification for more information.

3.3.7 Adding Publication Items to the Publication

Once you create a publication item, you must associate it with a publication. To change the definition, you can either remove the publication item and then recreate it with the new definition or use schema evolution depending on your requirements. See "Drop Publication Item" and "Alter Publication Item" respectively in the Consolidator Admin API Specification for more information.

Using the Consolidator Admin API

You can add publication items to the publication using the AddPublicationItem function, which has the following syntax:

public static void AddPublicationItem(String publication, String item,

String columns, String disabled_dml, String conflict_rule, String 
restricting-predicate, String weight) throws Throwable

The following examples add a publication item named P_SAMPLE1 to the publication T_SAMPLE1. The added publication item has the following parameters:

Table 3-11 Add Publication Item Sample Parameter

Parameter	Value	Definition
publication	T_SAMPLE	Defines T_SAMPLE as the publication to receive the new item.
item	P_SAMPLE	Defines P_SAMPLE as the publication item to be added.
columns	null	Specifies that no columns are renamed.
disabled_dml	null	Specifies that no options are selected for disabling DML. See the Consolidator Admin API Specification for other values.
conflict_rule	S	Defines the server as the winner in conflict resolution. See Section 3.3.7.2, "Defining Conflict Rules" for other values.
restricting_predicate	null	Specifies high-priority mode. When a client is synchronizing in high priority mode, the predicate is used to limit data pushed to the device.
weight	null	Specified as null or an integer to determine priority in executing Client Operations to master tables. See Section 3.3.7.3, "Using Table Weight" for more information.

Java Methods Example

Consolidator.AddPublicationItem("T_SAMPLE1", "P_SAMPLE1", null, null, "S",null 
null);

3.3.7.1 Read-only Publication Items

Publication items can be defined as read-only by disabling the DML. See the Consolidator Admin API Specification for more information.

3.3.7.2 Defining Conflict Rules

When adding a publication item to a publication, the user can specify winning rules to resolve replication conflicts in favor of either the client 'C' or the server 'S'. A Mobile Server replication conflict is detected under any of the following situations:

• The same row was updated on the client and on the server.

• Both the client and server created rows with equal primary keys.

• The client deleted a row and the server updated the same row.

• The client updated a row and the server deleted the same row is considered a replication error for compatibility with Oracle8 Advanced replication.

• For systems with delayed data processing, where a client's data is not directly applied to the base table (for instance in a three tier architecture) a situation could occur when first a client inserts a row and then updates the same row, while server has not yet inserted the row into the base table. In that case, if the DEF_APPLY parameter in C$ALL_CONFIG is set to TRUE, an INSERT operation is performed, instead of the UPDATE. It is up to the application developers to resolve the resulting PK conflict. If, however, DEF_APPLY is not set, a "NO DATA FOUND" exception is thrown (see below for the replication error handling).

• All the other errors including nullity violations and foreign key constraint violations are replication errors.

• If replication errors are not automatically resolved, corresponding transactions are rolled back and transaction operations are moved into Mobile Server Error Queue. Mobile Server database administrators later can change these transaction operations and re-execute or purge transactions from the error queue.

See the Consolidator Admin API Specification for more information.

3.3.7.3 Using Table Weight

Table weight is an integer property of association between publications and publication items. Mobile Server uses table weight to determine which order to apply Client Operations to master tables as follows:

1. Client INSERT operations are executed first, from lowest to highest table weight order.

2. Client DELETE operations are executed next, from highest to lowest table weight order.

3. Client UPDATE operations are executed last, from lowest to highest table weight order.

3.3.8 Creating Sequences

The Mobile Server supports sequence partitioning and replication to client devices.

Using the Consolidator Admin API

You can create a sequence with the CreateSequence function, which has the following syntax:

public static void CreateSequence(String name) throws Throwable

The following examples create a sequence named CUSTOM1:

Java Methods Example

Consolidator.CreateSequence("CUSTOM1");

3.3.9 Subscribing Users to a Publication

You can create a user subscription to a publication by using one of the following methods.

Using the Consolidator Admin API

You can subscribe users to a publication using the CreateSubscription function, which has the following syntax:

public static void CreateSubscription(String publication, String clientid)
    throws Throwable

The following examples subscribe the client, DAVIDL, to the publication, T_SAMPLE1, with the parameters listed in the following table.

Table 3-12 Create Subscription Sample Parameters

Parameter	Value	Definition
publication	T_SAMPLE1	Defines the publication as T_SAMPLE1.
clientid	DAVIDL	Defines the client as DAVIDL.

Java Methods Example

Consolidator.CreateSubscription("T_SAMPLE1", "DAVIDL");

3.3.10 Partitioning Sequences for Client Devices

You can use Mobile Server sequences to generate unique primary keys on client devices. To use a replicated sequence, you need to first create it and then partition it for Mobile Sync users.

Using the Consolidator Admin API

You can create sequence partitions using the CreateSequencePartition function, which has the following syntax:

public static void CreateSequencePartition(String name, String clientid,
                     long curr_val, long incr) throws Throwable

The following examples partition the CUSTOM1 sequence using the parameters specified in the table:

Table 3-13 Create Sequence Partition Sample Parameters

Parameter	Value	Definition
name	CUSTOM1	Defines CUSTOM1 as the sequence to be partitioned.
clientid	DAVIDL	Defines DAVIDL as the client to which the sequence is assigned.
curr_val	1000	Defines 1000 as the initial value of the sequence.
incr	1	Defines 1 as the increment value of the sequence.

Java Methods Example

Consolidator.CreateSequencePartition("CUSTOM1", "DAVIDL", 1000, 1); 

3.3.11 Defining Client Subscription Parameters to Publications

Some publications have parameters. When a publication has parameters, you must set those parameters for the publication's subscription.

Using the Consolidator Admin API

You can define subscription instantiation parameters with the SetSubscriptionParameter function, which has the following syntax:

public static void SetSubscriptionParameter(String publication, String clientid,
                     String param_name, String param_value) throws Throwable

The following examples subscribe the client, DAVIDL, to the publication, T_SAMPLE1, with the parameters listed in the table:

Table 3-14 Set Subscription Parameter Sample Parameters

Parameter	Value	Definition
publication	T_SAMPLE1	Defines the publication, T_SAMPLE1.
clientid	DAVIDL	Defines the client as DAVIDL.
param_name	COMP	Defines the parameter name as COMP.
param_value	P	Defines the parameter value as P.

Java Methods Example

Consolidator.SetSubscriptionParameter("T_SAMPLE1", "DAVIDL", "COMP", "'P'");

3.3.12 Instantiating the Subscription

After you set a subscription's publication parameters, you complete the subscription process by instantiating the subscription. When the Mobile Server instantiates a subscription, it creates a complete internal representation of the subscription.

Using the Consolidator Admin API

You can instantiate a subscription with the InstantiateSubscription function, which has the following syntax:

public static void InstantiateSubscription(String publication,
                     String clientid) throws Throwable

The following examples instantiate a client's subscription to a publication, with the values specified in the table:

Table 3-15 Instantiate Subscription Sample Parameters

Parameter	Value	Definition
publication	T_SAMPLE1	Defines the publication as T_SAMPLE1.
clientid	DAVIDL	Defines the client as DAVIDL.

Java Methods Example

Consolidator.InstantiateSubscription("T_SAMPLE1", "DAVIDL"); 

3.3.13 Schema Evolution Using Alter Publication Item

You can add additional columns to existing publication items. These new columns are pushed to snapshots on all subscribing clients the next time they synchronize. This is accomplished through a complete refresh of all changed publication items.

• An administrator can add multiple columns.

• This feature is supported for all client formats.

• The client does not upload snapshot information to the server. This also means the client cannot change snapshots directly on the client database, for example, you could not alter a table using Mobile SQL on EPOC.

• Publication item upgrades will be deferred during high priority synchronizations. This is necessary for low bandwidth networks, such as wireless, because all publication item upgrades require a complete refresh of changed publication items. While the high priority flag is set, high priority clients will continue to receive the old snapshot format.

• The server needs to support maximum of two versions of publication item.

3.3.13.1 Alter Publication Item

This allows additional columns to be added to an existing publication item.

Using the Consolidator Admin API

public static void AlterPublicationItem(String name, String select_stmt)
   throws Throwable 

Table 3-16 Alter Publication Item Sample Parameters

Parameter	Value	Description
name	P_SAMPLE1	A character string specifying the publication item name.
select_stmt	select * from EMP	A new publication item select statement containing additional columns.

Java Methods Example

Consolidator.AlterPublicationItem("P_SAMEPLE1", "select * from EMP"); 

3.4 Publish and Subscribe Method Specialized Functions

The following features include special functions which are not required for most application designs.

3.4.1 Callback Customization Using Compose and Apply

When creating publication items, the user can specify a customizable package to be called during the Apply and Compose phase of the MGP background process. These procedures enable you to incorporate customized code into the process. The clientname and tranid are passed to allow for customization at the user and transaction level.

This procedure must be called before any client's data from inqueue is applied.

procedure BeforeApply(clientname varchar2)

This procedure must be called after all client's data is applied.

procedure AfterApply(clientname varchar2)

This procedure must be called before client's data with tranid is applied.

procedure BeforeTranApply(tranid number)

This procedure must be called after client's data with tranid is applied.

procedure AfterTranApply(tranid number)

This procedure must be called before outqueue is composed.

procedure BeforeCompose(clientname varchar2)

This procedure must be called after outqueue is composed.

procedure AfterCompose(clientname varchar2)

3.4.2 Defining Customized DML Operations

Once a publication item has been created, a user can specify a customized PL/SQL procedure to be called in place of DML operations for that publication item. There can be only one mobile_dml_procedure per publication or pub_item. For example, if you want to have a DML procedure for a publication item A that has the following query:

select A,B,C from pub_item_a_table

where A is a primary key, then your DML procedure should have the following signature:

any_schema.any_package.any_name (DML in CHAR(1), A in TYPE, B in TYPE, C in 
TYPE, A_OLD in TYPE)

This procedure will be called with 'I', 'U', 'D' as the DML type. In case of inserts and deletes, A_OLD will be null. In case of updates, it will be set to the primary key of the row that is being updated.

3.4.3 Virtual Primary Key

You can specify a virtual primary key for publication items where the base object does not have a primary key defined. There are methods for creating and dropping a virtual primary key.

3.4.3.1 Create Virtual Primary Key Column

This creates a virtual primary key column.

Using the Consolidator Admin API

This is used to create an updatable publication item where the primary key is a virtual column.

public static void CreateVirtualPKColumn(String owner, String store, String 
column) throws Throwable 

Table 3-17 Create Virtual Primary Key Column Parameters

Parameter	Value	Description
owner	SAMPLE_1	A string specifying a the owner of the base object.
store	DEPT	A string specifying the base object.
column	DEPT_ID	A string specifying the primary key columns. This string can be comma delimited.

Java Methods Example

Consolidator.CreateVirtualPKColumn("SAMPLE1", "DEPT", "DEPT_ID"); 

3.4.3.2 Drop Virtual Primary Key Column

This allows a primary key to be dropped.

Using the Consolidator Admin API

public static void DropVirtualPKColumn(String owner, String store) throws 
Throwable 

Table 3-18 Drop Virtual Primary Key Column Parameters

Parameter	Value	Description
owner	SAMPLE_1	A string specifying a the owner of the base object.
store	DEPT	A string specifying the base object.

Java Methods Example

Consolidator.DropVirtualPKColumn("SAMPLE1", "DEPT"); 

3.4.4 Restricting Predicate

A restricting predicate can be assigned to a publication item as it is added to a publication. When a client is synchronizing in high priority mode, the predicate is used to limit data pushed to the device. This parameter can be null. This parameter is for advanced use.

3.4.5 Resolving Conflicts Using the Error Queue

For each publication item created, a separate and corresponding error queue is created. The purpose of this queue is to store transactions that fail due to unresolved conflicts. The administrator can attempt to resolve the conflicts, either by modifying the error queue data or that of the server, and then she may attempt to re-apply the transaction via the Execute Transaction API call. The administrator may also purge the error queues through the Purge Transaction API call.

3.4.5.1 Execute Transaction

The execute transaction function re-executes transactions in the Consolidator error queue.

Using the Consolidator Admin API

public static void ExecuteTransaction(String clientid, long tid)
   throws Throwable 

Table 3-19 Execute Transaction Parameters

Parameter	Description
clientid	The Mobile Sync name.
tid	The transaction ID.

Java Methods Example

Consolidator.ExecuteTransaction("DAVIDL", 100002); 

3.4.5.2 Purge Transaction

The purge transaction function purges a transaction from the Mobile Server error queue.

Using the Consolidator Admin API

public static void PurgeTransaction(String clientid, long tid) throws Throwable 

Table 3-20 Purge Transaction Parameters

Parameter	Value	Description
clientid	DAVIDL	The Mobile Server user name.
tid	100001	Transaction ID

Java Methods Example

Consolidator.PurgeTransaction("DAVIDL", 100001); 

3.5 Setting Up the EPOC Emulator

The emulator must be manually configured by copying files into directories used specifically by the emulator. The emulator also requires that transport be configured for HTTP. Directions for installing Oracle Lite database can be found in Section 2.2.1.2, "Setting Up the EPOC SDK".

3.5.1 Installing Mobile Sync on the EPOC Emulator

It is recommended that you install both the release and debug versions of the Mobile Sync on the EPOC emulator.

To install the debug version of the Mobile Sync on the EPOC emulator:

1. Create a new subdirectory CONSCLI in \epoc32\Release\WINS\Deb\Z\System\Apps.

2. Copy the following files from Oracle_Home\Mobile\SDK\epoc\runtime\install into \epoc32\Release\WINS\Deb\Z\System\Apps\CONSCLI:
   • Conscli.app

   • Conscli.aif

   • Conscli.rsc

To install the release version of the Mobile Sync on the EPOC emulator:

1. Create a new subdirectory CONSCLI in \epoc32\Release\WINS\Rel\Z\System\Apps.

2. Copy the following files from Oracle_Home\Mobile\SDK\epoc\runtime\install into \epoc32\Release\WINS\Rel\Z\System\Apps\CONSCLI:
   • Conscli.app

   • Conscli.aif

   • Conscli.rsc

3.5.2 Configuring Transport for the Emulator

Replicating with the EPOC device using HTTP requires the TCP/IP communication protocol. EPOC devices use serial lines for connection to the network. There are multiple ways of configuring TCP/IP over serial lines. This document provides instructions for enabling TCP/IP communications for handheld devices using Windows NT RAS. Since the emulator is using the machine hosting it as a RAS server, you will need to connect two serial ports on the host system with a null-modem cable, assigning one port to the emulator, and one to the server using the steps described in Section 3.7.3, "Configuring Transport on a Device".

3.6 Testing Replication on the Emulator

Start the Mobile Client on the emulator by selecting the Extras menu, and the mSync icon.

Mobile Sync Client for EPOC uses the following parameters to sync:

Table 3-21 Mobile Client Parameters

Parameter	Description
User Name	Mobile Client user name. This field is not case sensitive.
Password	Mobile Client password. This field is case sensitive.
Change	Leave this box clear.
Save Password	Select this check box to save the password.
http://<mobile server>	The Mobile Server IP address.
Use Proxy	Select if appropriate.

Use the following user name and password to make use of the SAMPLE11 sample application. A separate user name and password must be provided for any custom made applications and user snapshots.

• User Name = S11U1

• Password = MANAGER

1. Tap the ORACLE icon to start the Mobile Sync. The "Oracle Mobile Sync" screen appears.

Figure 3-1 Mobile Sync Screen

Enter the information in the necessary fields. Remember to select the Save Password check box. The http:// field can contain a screen name or an IP address.

Tap the 'Apply' button.

Tap the 'Sync' button.

A progress bar appears to indicate the completion of each synchronization task: composing, sending receiving, and processing. The progress bar also displays the time each task took to complete. If synchronization executes successfully, the synchronization Success screen appears. If synchronization fails, a "sync failed" message appears. To determine the cause of a failed synchronization the server administrator can view tracing information in the Mobile Server log file.

3.7 Setting Up the Mobile Client on the Device

Once you configure transport between Mobile Sync and the device, you must install and configure the Mobile Sync on the device. When setting up the Mobile Sync on the EPOC device or the EPOC emulator, you must first install the client, and then configure it.

3.7.1 Installing Oracle Lite on the EPOC Device

To run Oracle Lite applications on the EPOC device, you must install the required .dll files. The .dll files are packaged in an installation file (olite.sis) which is installed on your PC with Oracle Lite for EPOC.

To install the Oracle Lite .dll files on the EPOC device:

1. Connect the EPOC device to a COM port on your PC with the supplied serial cable.

2. Ensure that the EPOC Remote Link is set to Cable. You can change this setting through the System Tools menu on the EPOC device.

3. Select Install New Program from the PsiWin program group.

   The Select Installation File dialog appears.

4. Select olite.sis from the Oracle_Home\Mobile\SDK\epoc\runtime\install directory. Click the OK button.

   The PsiWin utility downloads the files to the System\Libs folder on the EPOC device.

5. On the EPOC device, open Tools and then the Preferences dialog in the System application, and select the Show System Folder check box.

6. Open the System\Libs folder on the EPOC device and ensure that the following .dll files are installed:
   • oljdbc36.dll

   • olodbc.dll

   • sqlrt.dll

   • okapi.dll

   • wisslite.dll

   • ootutil.dll

   • olglob.dll

   • os.dll

   • estlib.dll

7. Tap the Apply button to save the user information.

8. Tap the Sync button to begin synchronization.

   After synchronization, the Mobile Sync does not automatically re-enable PC Link on the EPOC device. To re-enable the PC Link, press Ctrl-L.

3.7.2 Installing Mobile SQL on the EPOC Device

1. Follow steps 1 - 3 listed in Section 3.7.2, "Installing Mobile SQL on the EPOC Device".

2. Select msql.sis from the Oracle_Home\Mobile\SDK\epoc\runtime\install directory. Click the OK button.

   The PsiWin utility downloads the files to the System\Libs folder on the EPOC device.

3. On the EPOC device, open Tools and then the Preferences dialog in the System application, and select the Show System Folder check box.

4. Open the System\Libs folder on the EPOC device and ensure that the msql.exe file was installed.

3.7.3 Configuring Transport on a Device

The Mobile Sync downloads and formats data from the Mobile Server. Before you can synchronize data between the Mobile Server and the Mobile Sync, you must configure the transport between them.

3.7.3.1 Configuring Windows NT RAS for TCP/IP Communications

Mobile Server can use HTTP as a transport method for EPOC devices via Mobile Server. Replicating with the EPOC device using HTTP requires the TCP/IP communication protocol. EPOC devices use serial lines for connection to the network. There are multiple ways of configuring TCP/IP over serial lines including the following: Windows NT RAS, Windows 95/98 dial-up, DigiBoard Port Servers, Unix serial connections, Symbol's multi-cradle boards, and ISP's dial-up over modems. This document provides instructions for enabling TCP/IP communications for handheld devices using Windows NT RAS.

To enable TCP/IP communications between handheld devices and your Windows NT server using Windows NT RAS, perform the following:

1. In the Windows Control panel, double-click the network icon.

2. Click the Add button in the Services tab of the Network window.

3. Select Remote Access Service and then click the OK button.

4. Insert the Windows NT CD into the disk drive and specify the location where you want the setup program to search for existing Windows NT RAS files. Click the Continue button. The setup program copies the Windows NT RAS files into the appropriate directories. If the setup program fails to detect a modem, it prompts you to add a modem.

5. Click the Yes button and enter the required information to install a modem. The Add RAS Device window appears.

6. Select COM1-Dial-Up Networking Serial Cable from the drop down list and click the OK button. The Remote Access Setup window appears.

7. Under Port, select COM1 and click the Configure button. The Configure Port Usage window appears.

8. Select the "Receive calls only" radio button and click the OK button.

9. Click the Network button in the Remote Access Setup window. The Network Configuration window appears.

10. Select TCP/IP and click the OK button. The RAS Server TCP/IP Configuration window appears.

11. Select "Entire network" and "Use static address pool".

12. You can specify a range of TCP/IP addresses for multiple devices. Enter the beginning of a TCP/IP address range in the Begin field and enter the ending of a TCP/IP address range in the End field. The range should be equal to the number of clients plus 1. For Example, if you have fifty devices you could enter the following range:
    • Begin: 10.1.0.1

    • End: 10.1.0.51

Important: You must ensure that the TCP/IP addresses in your selected range are not already in use by other computers in your network.

13. Click the OK button.

14. In the Network Configuration window, select "Any authentication including clear text" and click the OK button. Click the Continue button. The Setup Message window appears. Click the OK button.

15. In the Network window, click the Close button. The Network Settings Change window appears. Click the Yes button.

16. Once the system reboots, click the Services icon in the Windows Control Panel. The Services window appears.

17. Select Remote Access Server and click the Startup button.

18. Select Automatic and click the OK button. Click the Close button.

19. From the Windows Start menu select Administrative Tools and then select User Manager. The User Manager window appears.

3.7.3.2 Create a RAS User Account for the Device

1. Select New User from the User menu. The New User window dialog appears.

2. Enter a user name, password, and password confirmation in the required fields.

3. Select "Password Never Expires".

4. Click the Dialin button. The Dialin Information dialog appears.

5. Select "Grant dial-in permission to user". Click the OK button.

6. Click the OK button to exit the New User dialog.

7. Exit the User Manager screen.

8. From the Windows Start menu select Administrative Tools and then select Remote Access Administration. The Remote Access Admin window appears.

9. Verify that the new RAS user is granted remote access permission and click the OK button.

3.7.3.3 Configuring Transport on EPOC Devices

You can configure transport for the EPOC device using HTTP. This section describes the process for configuring HTTP transport with Windows NT RAS using a serial cable connection.

To configure the EPOC device for HTTP:

1. You must have access to an installation of Mobile Server, and Mobile Server must be running.

2. Ensure that Message Suite is installed on the EPOC device. Message Suite can be installed from the PsiWin CD or downloaded from the Symbian Web site.

3. From the system screen on the device, go to the Control Panel by pressing Ctrl-S.

4. Select Internet. The Internet Service Settings dialog appears.

5. Select New to create a new service. The Create New Internet service dialog appears.

6. Ensure that Based upon Standard Settings is selected. Tap the OK button.

7. Select the Service tab.

8. Enter a service name in the Name field. For example, Windows NT RAS. Select Direct.

9. Select the Account tab.

10. Clear the Manual Login check box.

11. Enter the user name and password as entered in the Windows NT RAS User Manager.

12. Select the Login tab.

13. Select the Use Login Script check box.

14. In the Port Settings field, select "8 data bits, noparity, 1 stop bit". A login script appears in the text box at the bottom of the screen.

15. Tap the Done button.

16. Tap the OK button.

17. Open the Control Panel by pressing Ctrl-S.

18. Select the Modem. The Modem settings dialog appears.

19. Select Direct Cable Connection in the Current Modem field.

20. Select Edit.

21. Select the Modem tab.

22. Set Speed to 19200 baud. Select the Connect via Serial option.

23. Set Fax Class to Auto.

24. Select the Initialisation tab, and ensure that all Init String fields are empty. Tap the OK button.

    The EPOC device is now configured for HTTP transport.

25. To test the configuration, open the EPOC browser and try to connect to a Web site. The connection fails, but the Windows NT RAS Manager should indicate an active connection.

26. Open the Control Panel by pressing Ctrl-S.

27. Select the Dialing check box.

28. Select Home under Current Location.

29. Tap the OK button.

3.7.3.4 Configuring the Client

To configure the Mobile Sync on the EPOC device:

1. On the EPOC device, select Extras->mSync.

   The Mobile Sync application starts.

2. Enter a user name and password.

3. Enter the URL of the Mobile Server.

3.8 Mobile Server System Catalog Views

Mobile Server contains system catalog views for locating Mobile Server components. You should not change the system catalog views without using the Mobile Server Admin API. Use the Mobile Server system catalog views to locate the following:

• Mobile Server users

• publications

• subscriptions

• sequences

• sequence partitions

• stand-alone publication items

• publication items added to publications

• publication item indexes

• subscription parameters

See Appendix A, "Mobile Server System Catalog", for more information.