2
Synchronization

This document discusses replication 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 Sync API

• Setting Up Mobile Client on the Device

• Mobile Server System Catalog Views

2.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 Mobile Server 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 Oracle8i through transport 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.

2.1.1 Publication and Subscription

The Mobile Server uses publications and subscriptions. Publications are (optionally parameterized) queries that are defined against Oracle8i 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 2.3, "The Publish and Subscribe Model" for more information.

2.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.

2.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.

2.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 Sync Clients.

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

The Mobile Server passes the current Mobile Sync Client 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 2.1.7, "Foreign Key Constraints in Updatable Publication Items" for more information. See Section 2.4.1, "Callback Customization Using Compose and Apply" for details on using these calls.

2.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).

2.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.

2.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.

2.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 Consolidator Admin API. See "Parent Hint" in the Consolidator 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 Oracle8i documentation for details on INSTEAD OF triggers.

2.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 Consolidator Admin API. See the Consolidator Admin API Specification for more information.

2.1.6.4 Complete Refresh for Views

Publication items can be created for complete refresh using the CompleteRefresh call from the Consolidator 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 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.

2.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.

2.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 with the Mobile Server, the EMP table is updated 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.

2.1.7.2 Avoiding Constraint Violations with BeforeApply and After Apply

PL/SQL allows you to 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.

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;
   

2.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 2.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)

2.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 2.4.5, "Resolving Conflicts Using the Error Queue" for more information on conflict resolution techniques.

2.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.

2.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.

2.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.

2.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 2-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	-

2.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 2-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 2-3 Methods to Implement Publish and Subscribe Model

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

2.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");

2.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.

2.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 2-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");

2.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 2-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");

2.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 2-6 Drop User Example Parameters

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

Java Methods Example

ResourceManager.dropUser("MOBILE");

2.3.3 Creating Publications

This section describes how to create a publication.

2.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.

2.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 2-7 Create Publication Sample Parameters

Parameter	Value	Definition
client_storage_type	Consolidator.OKPI_WINCE	A constant which defines the platform type. See the Oracle9i Lite Consolidator 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_WINCE, "%s", 
null); 

2.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 2-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 2.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 2.4.1, "Callback Customization Using Compose and Apply".
cbk_name	null	Specifies the callback package name as NULL. For more information, see Section 2.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);

2.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;

2.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 Specification for more information.

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

Table 2-9 Get Publication Item Name Example Parameters

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

Java Methods Example

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

2.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 2-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");

2.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.

2.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 2-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 2.3.7.2, "Defining Conflict Rules" for other values.
restricting_predicate	null	Specifies that high-priority mode. 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.
weight	null	Specified as null or an integer to determine priority in executing Client Operations to master tables. See Section 2.3.7.3, "Using Table Weight" for more information.

Java Methods Example

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

2.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.

2.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 Oracle8i 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.

2.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.

2.3.8 Creating Sequences

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

Using the Java 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");

2.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 2-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");

2.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 Client 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 2-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); 

2.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 2-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'");

2.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 2-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"); 

2.3.13 Schema Evolution Using Alter Publication Item

You can add additional columns to existing publication items. These new columns are pushed to 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 a snapshot directly on the client database, for example, you could not alter a table using Mobile SQL on Windows CE.

• 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.

2.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 2-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");

2.4 Publish and Subscribe Method Specialized Functions

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

2.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)

2.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.

2.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.

2.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 updateable publication item where the primary key is a virtual column.

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

Table 2-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"); 

2.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 2-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"); 

2.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.

2.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.

2.4.5.1 Execute Transaction

The execute transaction function re-executes transactions in the Mobile Server Error Queue.

Using the Consolidator Admin API

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

Table 2-19 Execute Transaction Parameters

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

Java Methods Example

Consolidator.ExecuteTransaction("DAVIDL", 100002); 

2.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 2-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); 

2.5 Mobile Sync API

The Mobile Sync API allows an application to initiate and monitor synchronization with a database from a client device rather than requiring that it be started from the Mobile Server. The default transport mechanism is HTTP, but other forms of transport can be specified if they are available.

Mobile Sync consists of five function calls and a control structure.

2.5.1 ocSessionInit

Initialize the synchronization environment.

Syntax

int ocSessionInit( ocEnv *env );

Parameters

Env

Pointer to an ocEnv structure buffer to hold the return synchronization environment.

Comments

This call initializes the ocEnv structure and restores any user settings that are saved in the last ocSaveUserInfo() call. ocEnv structure is passed as a parameter, and should be initialized by the caller. If the caller wants to overwrite the user preference information after the ocSessionInit() call, that can be done by calling ocSaveUserInfo().

2.5.2 ocSessionTerm

Frees and performs a cleanup of the synchronization environment.

Syntax

int ocSessionTerm( ocEnv *env );

Parameters

Env

Pointer to the environment structure returned by ocSessionInit.

Comments

De-initializes all the structures and memory created by the ocSessionInit() call. They should always be called in pair.

2.5.2.1 ocSaveUserInfo

Saves user settings.

Syntax

int ocSaveUserInfo( ocEnv *env );

Parameters

Env

Pointer to the synchronization environment.

Comments

This saves or overwrites the user settings into a file or database on the client side. The following information provided in the environment structure is saved:

• Username

• Password

• SavePassword

• NewPassword

• Priority

• Secure

• PushOnly

• SyncApps

• SyncNewPublication

For usage of these fields, please refer to the section Section 2.6, "Mobile Sync API Data Structures".

2.5.3 ocDoSynchroize

Starts the synchronization process.

Syntax

int ocDoSynchronize( ocEnv *env );

Parameters

Env

Pointer to the synchronization environment.

Comments

This starts the synchronization cycle. A round trip synchronization is activated if syncDirection is 0 (default). If syncDirection is 1, only upload (send) is performed. If syncDirection is 2, only download (receive) is performed. Performing an upload only synchronization is useful if client device does not want to download data from the server.

2.5.4 ocSetTableSyncFlag

Update the table flags for Selective Sync.

Syntax

ocSetTableSyncFlag(ocEnv *env, const char* publication_name, 
const char* table_name, short sync_flag)

Parameters

Env

Pointer to the synchronization environment.

publication_name

Used on CreatePublication(), which is part of the Consolidator Admin API. See the Oracle9i Lite Consolidator Admin API Specification for more information.

If publication_name is NULL, sync_flag apply for all items in the publication.

table_name

This string is same as client_name_template of CreatePublication(). In OKAPI case, it is database_name + '.' + store, where store is CreatePublicationItem()'s third parameter. See the Oracle9i Lite Consolidator Admin API Specification for more information on CreatePublication() and CreatePublicationItem().

sync_flag

If 1 do synchronize, If 0 do not synchronize. Sync_flag is not stored persistently. Each time before ocDoSynchronize() you need to call ocSetTableSyncFlag().

Comments

This function allows the client applications to select the way specific tables are synchronized.

Set sync_flag for each table or each publication. If sync_flag = 0, the table is not synchronized.

2.6 Mobile Sync API Data Structures

2.6.1 ocEnv

ocEnv is the data structure used by all the Mobile Sync functions to hold internal memory buffers and state information. Before using the structure, the application must pass it to ocSessionInit to initialized the environment.

The environment structure also contains fields that the caller can update to change program the way Mobile Sync functions work.

typedef struct ocEnv_s {
                       // User infos
char username[MAX_USERNAME];     // Mobile Sync Client id 
char password[MAX_USERNAME];     // Mobile Sync Client password for 
                       // authentication during sync 
char newPassword[MAX_USERNAME];     // resetting Mobile Sync Client password 
                       // on server side if this field is not blank 
short savePassword;      // if set to 1, save 'password' 
char appRoot[MAX_PATHNAME];     // dir path on client device for deploying files
short priority;     // High priority table only or not
short secure;     // if set to 1, data encrypted over the wire 
enum {
OC_SENDRECEIVE = 0,     // full step of synchronize
OC_SENDONLY,     // send phase only
OC_RECEIVEONLY,     // receive phase only

                       // For Palm Only
OC_SENDTOFILE,     // send into local file | pdb
OC_RECEIVEFROMFILE     // receive from local file | pdb
}syncDirection;     // synchronize direction

enum {
OC_BUILDIN_HTTP = 0,     // Use build-in Http transport method
OC_USER_METHOD     // Use user defined transport method
}trType;           // type of transport

ocErrorexError;     // extra error code

ocTransportEnvtransportEnv;     // transport control information 

                       // GUI related function entry
progressProcfnProgress;     // callback to track progress; this is optional

                 // Values used for Progress Bar. If 0, progress bar won't show.
longtotalSendDataLen;      // set by Mobile Sync API informing transport total 
number of
                       // bytes to send; set before the first fnSend() is called
long totalReceiveDataLen;     // to be set by transport informing Mobile Sync 
API 
                       // total number of bytes to receive; 
                       // should be set at first fnReceive() call. 
void* userContext;     // user defined context
void* ocContext;     // internal use only
short logged;     // internal use only
long bufferSize;     // send/receive buffer size, default is 0
short pushOnly;     // Push only flag
short syncApps;     // Application deployment flag
} ocEnv;

2.6.1.1 ocTransportEnv

This structure is used to override the built-in transport functions. By providing the list of functions in the structure, applications can define their own implementation for the transport layer used by the synchronization engine.

typedef struct ocTransportEnv_s {
void* ocTrInfo;            // transport internal context

                  // for built-in Http, maped to ocTrHttp

connectProc fnConnect;     // plug-in callback to establish a connection from

                  // device to server

disconnectProc fnDisconnect;     // plug-in callback to dismantle connection 
from

                   // device to server

sendProc fnSend;           // plug-in callback to send data 
receiveProc fnReceive;     // plug-in callback to receive data 
}ocTransportEnv;

2.7 Setting Up Mobile Client on the Device

Once you configure transport between Oracle8 and the Windows, you must install and configure Mobile Sync on the device. Setting up the Mobile Client on the Windows CE device is a two step approach:

• Run CESETUP.EXE from the Windows development system to install Mobile Client for Windows CE libraries.

• Configure HTTP transport between the Windows CE device and Mobile Server.

2.7.1 Directory Structure

The Mobile Development Kit for Windows CE is installed in the Oracle_Home\Mobile\SDK\wince directory. Inside this directory are five folders labelled samples, wce200, wce211, wce212, and wce300.

The following table lists and describes the contents of this directory.

Directory	Description
samples	Contains the following folders: ADOCE - ActiveX Data Objects for CE sample code. JDBC - JDBC sample code. ODBC - ODBC sample code. OKDEMO - OKdemo resource files. Consolidator - Conslidator Admin API sample code.
wce200	Contains CESETUP and other necessary .dll and .exe files for Windows CE version 2.0. This folder also contains folders with the necessary library and executable files for ARM, MIPS, SH3 and SH4 processors running under this version.
wce211	Contains CESETUP and other necessary .dll and .exe files for Windows CE version 2.1.1. This folder also contains folders with the necessary library and executable files for ARM, MIPS, SH3 and SH4 processors running under this version.
wce212	Contains CESETUP and other necessary .dll and .exe files for Windows CE version 2.1.2. This folder also contains a folder with the necessary library and executable files for the x86 processor running under this version.
wce300	Contains CESETUP and other necessary .dll and .exe files for Windows CE version 3.0. This folder also contains folders with the necessary library and executable files for ARM, MIPS, SH3 and SH4 processors running under this version.

2.7.2 Run the CESETUP.EXE file.

Run the appropriate CESETUP.EXE file in the Oracle_Home\Mobile\SDK\wince directory matching your processor and operating system combination. This program copies the necessary files to the Windows CE device, configures the Windows CE data source name (DSN), and sets up the environment on the device. Unless you modify it, the installation places the default database file, POLITE.ODB, in the \orace directory on the device. The setup initialization file, CESETUP.INI lists the files that are loaded to the device.

2.7.3 Configuring Transport

Replicating with the Windows CE devices using HTTP requires the TCP/IP communication protocol. This document provides instructions for enabling TCP/IP communications for Windows CE devices using Windows NT RAS.

2.7.3.1 Configuring NT RAS

To enable TCP/IP communications between Windows CE device and your Windows NT server using Windows NT RAS, perform the following steps:

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.

5. 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.

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

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

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

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

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

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

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

13. 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

14. Click the OK button.

15. 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.

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

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

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

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

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

2.7.3.2 Create a RAS User account for the device as follows:

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. Installing the Sample Application on the Device.

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

2.7.3.3 Windows Desktop RAS Settings

When you configure RAS verify that:

• The modem setting is "dial out and receive calls".

• You select the option to allow the Windows CE device to connect to any computer in the domain.

• The static IP addresses for the Windows CE device are in the same subnet as the desktop connected to the device. Otherwise, you cannot make an HTTP connection and replication fails.

• The ending IP address for the Windows CE device is not in use on the network.

• The Domain Logon server is available to the Windows CE device.

• The IP address is set correctly for the Windows CE device before running Mobile Sync Client application.

2.7.3.4 Windows Desktop Settings

When you configure the Windows desktop settings, verify that:

• The baud rate is set correctly in the Modems and Ports configuration in the Control Panel. Also, the baud rate for the Windows CE device matches the baud rate on the desktop.

• You reinstall your current Windows NT service pack after installing the Windows CE services.

2.8 Installing the Sample Application on the Device

The Mobile Development Kit provides a sample application called "Sample Orders" which can be installed using CEServices. Run CEServices and select the okdemo file from the Oracle_Home\Mobile\SDK\wince\samples folder corresponding to your processor into the \orace folder on your Windows CE device.

2.9 Testing Synchronization

Start the Mobile client application by opening Oracle_Home\Moble\SDK\wince\ followed by the folder containing the version of Windows CE and the processor your device uses, and start ConsolidatorClient.exe from that folder. For information on how to determine which version of Windows CE and which processor your device uses, see the Oracle9i Lite Installation and Configuration Guide.

Mobile Sync requires the following parameters to sync:

Table 2-21 Mobile Sync 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 mSYNC icon to start the Mobile Sync Client. The "Oracle Mobile Sync" screen appears.

Figure 2-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.

Select the 'Apply' button.

Select 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.

2.10 Mobile Server System Catalog Views

Consolidator contains system catalog views for locating Consolidator components. Use the Consolidator system catalog views to locate the following:

• Mobile 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 Views", for more information.