Skip Headers
Oracle® Database Lite Developer's Guide
10g (10.0.0)
Part No. B13788-01
  Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
Next
Next
 

10 Building Offline Mobile Applications for Win32: A Tutorial

This document guides you through the mobile application development process for the Win32 platform through a tutorial. Topics include:

10.1 Overview

To demonstrate the steps involved in building offline mobile applications for the Win32 platform, this tutorial presents a simplified mobile field service example.

10.2 Developing Offline Mobile Applications for Win32

Let us assume that you have a TASK table on the server that contains information about tasks that must be accomplished by your mobile field service technicians for a day. Listed below is the TASK table structure. Each row in the TASK table describes work to be done at a customer site.

Let us also assume that you have three service technicians, Tom, Dick, and Harry. You want to assign all the tasks in the City of Cupertino to Tom, those in the City of Mountain View to Dick, and those in the City of Palo Alto to Harry. You envision your application to work as follows:

Each service technician has a laptop that he uses to obtain his task list in the morning. He will perform the task during the day and will update the Notes column of a task with information about its status or what he has done. At the end of his work day, the service technician uploads his changes to the server.

We will assume the following environment for your application.

Our implementation plan is as follows. The exact sequence of commands for each step is given later.

  1. Create the TASK table in the oradbserver and insert some rows into it. This step is not needed if you already have a database that contains a table similar to TASK.

  2. Use the Packaging Wizard to define an application called Mobile Field Service. Create one publication item based on the TASK table for the application. Publish the application (which has no application files) to the Mobile Server.

  3. Use the Mobile Manager to create users Tom, Dick, and Harry on the Mobile Server. Grant all users the privilege to execute the Mobile Field Service application and create a subscription for each of them.

  4. Install the Oracle Database Lite 10g client on your development machine in a separate directory (emulating a technician's machine). Run the Mobile Sync application to download the Mobile Field Service application (which is currently empty) and data.

  5. On your development machine, use MSQL to look at the rows in the TASK snapshot and update the rows by entering notes in the Notes column.

  6. Synchronize the changes you made in the snapshot with the server database by running the Mobile Sync application again.

  7. Connect to the server database and check that your changes are there. Modify the Description of one of the rows for the customer in Cupertino.

  8. Run the Mobile Sync application again. You will see the changes that you made on the server are in the snapshot in the client database.

  9. Develop a C or C++ program against Oracle Database Lite to:

    • show the tasks to the technician, and

    • let the technician choose a task and enter notes for it

  10. Use the Packaging Wizard to update the application to include the above program.

The Mobile Server is now ready for real life testing.

10.2.1 Command Sequence

The following sections describe the command sequence.

10.2.1.1 Step 1. Create TASK Table on the Server Database

We will use the Oracle9i thin JDBC driver to connect to the Oracle database running in the oradbserver machine. Ensure that the thin JDBC driver (&fmv683;\jdbc\lib\classes12.zip) file is included in your CLASSPATH environment variable. We will connect as master with password master.

D:>msql master/master@jdbc:oracle:thin:@oradbserver:1521:orcl

Now create the TASK table in this database. The SQL script to create and populate the server database is provided in the following directory.

&fmv684;\mobile\sdk\samples\odbc\MFS

SQL>create table TASK(
 
1> ID number(4) primary key,
2> Description varchar(40) not null, 
3> CustName varchar(30) not null,
4> CustPhone varchar(12),
5> CustStAddr varchar(40) not null, 
6> CustCity varchar(40) not null,
7> Notes varchar(100));

We will now insert four rows into this table.

SQL> insert into task values(1,'Refrigerator not working','Able','408-999-9999','123 Main St.','Cupertino',null);
SQL> insert into task values(2,'Garbage Disposal broken','Baker','408-888-8888','234 Central Ave','Cupertino',null);
SQL> insert into task values(3,'Refrigerator makes noise','Choplin','650-777-7777','1 North St.','Mountain View',null);
SQL> insert into task values(4,'Faucet leaks','Dean','650-666-6666','10 University St.','Palo Alto','Beware of dogs');
SQL> commit;
SQL> exit

10.2.1.2 Step 2. Define a Publication Item and Publish the Application

We will now use the Packaging Wizard to create a publication item for your application.

To use the Packaging Wizard, type the following command at the Command Prompt.

d:\> wtgpack

The Packaging Wizard appears.

Select the 'Create a new application' option and click OK.

In the next panel, select the 'Oracle Lite WIN32:US' platform from the list of 'Available Platforms'. This action prompts the Packaging Wizard to create a Windows 32 application. Click Next.

The next screen is for entering application information. We will call our application "Mobile Field Service". We will publish it in the /MFS directory on the Mobile Server. All our application files will be stored in the directory D:\MFSDEV\Win32 on the development machine. We need to use the Win32 sub-directory under the development directory for the Windows 32 application. The Oracle Database Lite Tools and Utilities Guide discusses the directory naming convention used by the Packaging Wizard.

Enter the following information on the screen.

Application Name: Mobile Field Service

Virtual Path: /MFS

Description: Field Service Task Assignment

Local Application Directory: D:\MFSDEV (note: you don't specify the Win32 subdirectory here)

Click Next.

The next screen allows you to include any files such as the executable and image files that the application will need. It will read the D:\MFSDEV\Win32 directory and will display all the files found there. For now, we only want to create snapshots and so we will not include any files yet. Click Next.

The next panel is used to enter the database name that is for the client database. Enter the following:

Client Side

Database Name: MFS

Click Next.

The next screen enables you to define publication items that will become snapshots on the client database. We will create the publication based on the TASK table that we have defined on the server. We do this by importing the table into the Packaging Wizard. Click the "Import" button towards the bottom of the screen. You will be prompted to enter the server login information. Enter the following:

User Name: master

Password: master

Database URL: jdbc:oracle:thin:@oradbserver:1521:orcl

Click the OK button.

A dialog appears listing all the tables that are available. Select the TASK table and click the Add button. Click the Close button. The TASK table is now listed in the "snapshots" table. Select the row for the TASK table and click the Edit button.

The next screen enables you to enter the subsetting query for the snapshot. We will create an updatable snapshot that can be refreshed incrementally (fast refresh). If there is a conflict in updates, we want the server changes to win. So we enter the following information.

Set the value of Weight to 1 and then click the tab "Win32".

To explain how table weight works. Table weight is an integer property of association between publications and publication items. The Mobile Server uses table weight to determine the order in which to apply client Operations to master tables within each publication as listed below.

  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.

  4. The value assigned must be an integer between 1 and 1023.

Table weight is applied to publication items within a specific publication. For example, a publication can have more than one publication item of weight "2", which would have INSERT operations performed for any publication item of a lower weight within the same publication.

Continuing with the steps to package and publish the application, in the next screen after setting the value of Weight to 1 and clicking the tab "Win 32", enter the following:

Create on Client: check

Updatable?: check

Conflict resolution: select the "Server Wins" option

Refresh type: select the "Fast Refresh" option

Template: select * from master.task where CustCity = :city

Click OK. This brings you back to the previous screen. The template query contains a variable (subscription parameter) named "city". Later, when you provision the application to a user, you will be prompted to enter the value for it.

Click Finish. A dialog appears. Select "Publish the current application" option and click OK. A dialog appears prompting you to enter information about the Mobile Server.

Enter the following:

Mobile Server URL: mserver

Mobile Server User Name: Administrator

Mobile Server Password: admin

Repository path: /MFS

Click OK. If you get the message "Application Published Successfully", click the OK button and then click the EXIT button. You have successfully published an application that has no files and one publication item.

10.2.1.3 Step 3. Create Users and Subscriptions

To create users on the Mobile Server, you use the Mobile Manager. To use the Mobile Manager, you must log in to the Mobile Server as administrator. To log in to the Mobile Server, perform the following actions.

  1. Using a browser, browse the Mobile Server page by entering the following URL.

    http://<mobile_server>/webtogo

    (For historical reasons, the term "webtogo" instead of "mobileserver" is used in the URL.)

    The Logon page appears. Enter the "administrator" as the User Name and "admin" as the password.

    Click the Logon button.

  2. The Mobile Server farms page appears. Click your Mobile Server's link. Your Mobile Server's home page appears. To display your applications, click the Applications link. Click the Application Name link for which you will add users. On the Users page, click the Add User button. The Add User page appears.

    You will use this screen to create users Tom, Dick and Harry. We will only show how to create user Tom in the following commands.

  3. Enter the following information.

    Display Name: Tom Jones

    User Name: Tom

    Password: tomjones

    Password Confirm: tomjones

    System Privilege: User

  4. Click the Save button. The Mobile Manager displays a confirmation message. Click OK.

  5. To provide access to these users, click the Access link. The Access page lists existing applications. Select the Mobile Field Service application by checking the "Access" box for it. Click the Save button. A message box appears. Click the OK button on the message box. You have just granted user Tom the permission to execute the Mobile Field Service application.

  6. To create a data subset in your database during application installation, you will now define subscriptions for these users. Click the Data Subsetting link. The Data Subsetting parameters page appears. For the Mobile Field Service application, we have only one publication item and it has only one subscription parameter called "city". Enter the value "Cupertino" (without the quotes) for the value of "city" and click the Save button. The Mobile Manager displays a confirmation message. Click the OK button.

You have successfully created the user Tom, granted him the privilege to execute the application "Mobile Field Service", and assigned him all the tasks in the City of Cupertino.

Repeat the above steps for users Dick and Harry.

10.2.1.4 Step 4. Install the Oracle Database Lite 10g Client and the Mobile Field Service Application and Data

In a production system, mobile users such as Tom, Dick, and Harry would visit the setup page of the Mobile Server and download the Oracle Database Lite 10g Windows 32 client. They will then run the Mobile Sync application to download the Mobile Field Service application and the corresponding data subsets. After downloading the application and data, they will use the Mobile Field Service application and occasionally run Mobile Sync to synchronize the data with the server.

However, we are still in the development process and the developer has not yet developed the real Mobile Field Service application. So far, the installation and initial synchronization will only create a client Oracle Database Lite database that has a snapshot called TASK in it.

The developer will install and perform the initial synchronization as user Tom to retrieve an Oracle Database Lite database with a snapshot in it. He will then test the synchronization process before he develops the application.

On the machine where you installed the Mobile Development Kit, browse the setup page of the Mobile Server. The URL is http://<your_mobile_server/webtogo/setup. The setup page displays a list of supported platforms. Download the Mobile Client for Win32 by clicking on the appropriate link. To install the client, choose a directory, say D:\MFS. Browse the directory and familiarize yourself with its structure. Start the Commend Prompt and enter the following:

C:>D:

D:>cd MFS\Mobile\bin

D:\MFS\Mobile\bin>msync

This will run the Mobile Sync application, downloaded as part of the application installation. (You can also run the Mobile Sync application in your \sdk\bin directory.) A dialog appears. Enter the following information:

User Name: Tom

Password: tomjones

Server: mserver

Click the Sync button. A message box appears showing the progress of synchronization. When the synchronization process is complete, click the Cancel button on the Mobile Sync application dialog.

You now have an Oracle Database Lite database on your development machine. It contains a snapshot called TASK which has two rows in it; both rows have "Cupertino" for the CustCity column. These are the service requests by customers in Cupertino and Tom has been assigned these tasks.

The initial synchronization process also created an ODBC data source name (DSN) called tom_mfs (the user name followed by the underscore character followed by the database name).

10.2.1.5 Step 5. Browse the TASK Snapshot and Update a Row

Start the Command Line and enter the following:

D:>MFS\Mobile\bin>msql system/manager@jdbc:polite:tom_mfs

SQL> select * from task;

The following two rows are displayed.

SQL> update task set Notes ='Replaced the motor:$65' where ID = 1;

1 row(s) updated

SQL> commit;

commit complete

SQL> exit

You have successfully updated a row of the TASK snapshot.

10.2.1.6 Step 6. Synchronize the Change with the Server

Before you synchronize your change with the server, you must ensure that the MGP process is running. To ensure that the MGP process is running, follow the directions given in Step 3 and log on to the Mobile Server as administrator. Navigate to your Mobile Server home page and click the Applications link. Click the Job Scheduler link in the bottom section of this page and click the MGP Data Synchronization link. Click the MGP/Apply Compose Cycles link and schedule the MGP process on the MGP/Apply Compose Cycles page.

On your development machine, run the Mobile Sync application as described in Step 4. When the synchronization is successfully completed, your changes will be reflected in the server database.

10.2.1.7 Step 7. Check your changes on the server and modify a server record

Connect to the server database and issue the following SQL statements:

D:> msql master/master@jdbc:oracle:thin:@oradbserver:1521:orcl

SQL> select * from task;

You will see your changes reflected in the table. Now we will make a change in this table.

SQL> update task set description = 'Garbage Disposal Leaking', Notes= 'Urgent: house is getting flooded' where id = 2;

1 row(s) updated

SQL> commit;

Commit complete

SQL> exit

10.2.1.8 Step 8. Synchronize again to get the server changes

On your development machine, run the Mobile Sync application as described in Step 4. When the synchronization is successfully completed, perform the following:

D:>MFS\Mobile\bin>msql system/manager@jdbc:polite:tom_mfs

SQL> select * from task;

You will see two rows displayed. The second row displays the changes that you made on the server.

10.2.1.9 Step 9. Develop your Mobile Field Service Application Using Oracle Database Lite

An example ODBC program called MFS.exe is provided with the Mobile Development Kit in the following directory:

&fmv715;\Mobile\Sdk\samples\odbc\mfs

(The\src directory contains the source and the makefile for it.)

This example is very simple and does not use any UI widgets. It displays the task list and prompts the user to enter the Task ID for the chosen task, before entering notes. When the user enters the Task ID value as -1, the program terminates. For any valid Task ID, the MFS application prompts the user to enter notes. Enter notes without using quotes. You can try to improve the example as required.

To publish this program to the Mobile Server, copy the mfs.exe file into the directory D:\MFSDEV\Win32.

10.2.1.10 Step 10. Republish the Application with the Application Program

Use the Packaging Wizard to republish the application. From the Command Line, enter the following:

D:>wtgpack

On the first screen, select the "Edit an existing application" option. From the drop down list, select "Mobile Field Service" and click the OK button.

In the next screen, click the Files tab. You should see the MFS.exe file listed in the "File Name" window. Click OK.

In the next screen, select the "Publish the current application" option and click OK. You will be prompted to enter the login information for the Mobile Server. Click the OK button after entering the information. You will then see a message box warning you that the application already exists on the Mobile Server and whether you want to overwrite it. Click the YES button.

If you get the message "Application Published Successfully", click OK and then click EXIT. You have successfully republished an application that has a file called mfs.exe and one publication item.

Test your application by using a fresh Windows 32 machine. Follow Step 4 to install the Oracle Database Lite 10g client and the Mobile Field Service application on the machine. Then execute the Mobile Field Service application by executing the D:\MFS\Mobile\oldb40\TOM\mfs.exe program. Enter notes for one of the tasks. Then execute D:\MFS\Mobile\bin\msync.exe to synchronize your changes with the server.