|
Oracle® Database Lite Developer's Guide
10g (10.2.0) Part No. B15920-01 |
|
 Previous |
 Next |
|
Oracle® Database Lite Developer's Guide
10g (10.2.0) Part No. B15920-01 |
|
|
Previous |
Next |
This document guides you through the Mobile application development process for the Win32 platform through a tutorial. Topics include:
To demonstrate the steps involved in building offline Mobile applications for the Win32 platform, this tutorial presents a simplified Mobile field service example.
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.
TASK(ID number(4) primary key
Description varchar(40) not null
CustName varchar(30) not null
CustPhone varchar(12)
CustStAddr varchar(40) not null
CustCity varchar(40) not null
Notes varchar(100)
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.
The Mobile Server is installed on the machine called mserver.
The test Oracle database that is used to store the application data and the Mobile Server Repository is installed on the machine oradbserver with the listener on port 1521. The Oracle database instance name is orcl. We will assume that you can log in to the database with the user name master and password master. You can substitute any user for master so long as the user has the right privileges.
You have already installed the Mobile Development Kit on your development machine.
Our implementation plan is as follows. The exact sequence of commands for each step is given later.
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.
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.
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.
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.
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.
Synchronize the changes you made in the snapshot with the server database by running the Mobile Sync application again.
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.
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.
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
Use the Packaging Wizard to update the application to include the above program.
The Mobile Server is now ready for real life testing.
The following sections describe the command sequence.
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 (<ORACLE_HOME>\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.
<ORACLE_HOME>\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
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.
Client INSERT operations are executed first, from lowest to highest table weight order.
Client DELETE operations are executed next, from highest to lowest table weight order.
Client UPDATE operations are executed last, from lowest to highest table weight order.
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.
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.
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.
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.
Enter the following information.
Display Name: Tom Jones
User Name: Tom
Password: tomjones
Password Confirm: tomjones
System Privilege: User
Click the Save button. The Mobile Manager displays a confirmation message. Click OK.
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.
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.
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).
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.
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.
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
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.
An example ODBC program called MFS.exe is provided with the Mobile Development Kit in the following directory:
<ORACLE_HOME>\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.
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, as follows:
D:\MFS\Mobile\oldb40\TOM\mfs.exe TOM_MFS system manager
When TOM is the user. Enter notes for one of the tasks. Then execute D:\MFS\Mobile\bin\msync.exe to synchronize your changes with the server.
