22 Building Offline Mobile Applications for Windows CE: A Tutorial

This document describes how to build a Visual Basic.NET (Visual Studio.NET 2003) application using the Oracle Database Lite 10g ADO.NET interface for Pocket PC. It enables you to implement offline Mobile applications for the Pocket PC using Oracle Database Lite 10g. It provides you with the complete framework to build, deploy, and manage offline Mobile applications. Oracle Database Lite 10g supports various application models for the Pocket PC by supporting industry standard interfaces such as ODBC, JDBC, and ADO.NET. Topics include:

22.1 Overview

This document guides you through the entire offline Mobile application implementation process using a sample Pocket PC application. The tutorial enables you to create, deploy, administer, and use a Pocket PC Windows CE application.

The sample Pocket PC application is based on typical activities of delivery personnel in the Transportation and Logistics industry. The day-to-day operations of such personnel involve package pick-up and delivery. A delivery person collects the complete delivery package list and the package delivery destination information for the day, before he leaves the dispatch center on his Pocket PC. As the truck driver also carries information related to package pick-up and delivery with him, the delivery person can work offline and update the package pick-up and delivery status on his Pocket PC. Later, he can synchronize his updated information with the central server running in the dispatch center over any wireless network.

22.1.1 Before You Start

This tutorial assumes that the Mobile Server is installed on the same desktop that is used for Pocket PC application development. Before starting the offline Mobile application development process, you must ensure that the development computer and the client device meet the requirements specified below.

22.1.1.1 Application Development Computer Requirements

You must configure and install the following components on the development computer.

Table 22-1 lists the configuration and installation requirements for the Mobile application development computer.

Table 22-1 Application Development Computer Requirements

Requirement Description
Windows NT/2000/XP User Login The login user on the Windows NT/2000 development computer must have "Administrator" privileges.
Installed Java Components Java Development Kit 1.4.2 or higher.
Installed Oracle Database Lite 10g Components Oracle Database 8.1.7 or higher.

The Mobile Server (Oracle Database Lite 10g CD-ROM).

The Mobile Development Kit (Oracle Database Lite 10g CD-ROM).

Installed Pocket PC Components Microsoft Active Sync 3.7.1 or higher.

Microsoft eMbedded Visual Toolkit 3.0


22.1.1.2 Client Device Requirements

You must connect the client device to the desktop and install the Oracle Database Lite 10g client for Pocket PC on the device. For more information on how to install the Mobile Client on the device, see Section 22.5.1, "Installing the Oracle Database Lite Mobile Client for Pocket PC".

22.2 Developing the Application

This section explains how to develop and test the Pocket PC Transport application using the Mobile Development Kit for Pocket PC. The Pocket PC Transport application is written in Visual Basic.NET (Visual Studio.NET 2003).

To develop and test the Pocket PC Transport application, perform the following tasks.

  1. Create database objects in the Oracle database.

  2. Write the application code.

  3. Compile the application.

22.2.1 Creating Database Objects in the Oracle Server

During deployment, the Mobile Server automatically creates the Oracle Database Lite 10g database in the client device along with the requisite tables and data. To publish the application, users must create database objects in the Oracle database.

22.2.1.1 The Pocket PC Transport Application Database Objects

The Pocket PC Transport application uses the following database objects.

  1. Packages Table

  2. Routes Table

  3. Trucks Table

Table 22-2 lists the columns of packages that enable you to store all information about the package.

Table 22-2 Packages Table

Column Description
DID Package ID
DDSC Package Description
DWT Package Weight
DSTR Destination Street
DCTY Destination City
DST Destination State
DRTNR Route Number
DRTNM Route Name
DESN Signature
DSTS Package Status
TID Truck Number
PRTY Priority
PTNO Point Number
TIND Delivery 'D', or Pick-up 'P'

Table 22-3 lists the columns of routes that enable you to store all information about a route.

Table 22-3 Routes Table

Column Description
ROUTE_NO Route Number (Primary Key)
ROUTE_NM Route Name
EST_TIME Estimated Time

Table 22-4 lists columns of trucks that enable you to store all information about the availability status and destination information for a truck.

Table 22-4 Trucks Table

Column Description
TRUCK_NO Truck Number (Primary Key)
TRUCK_STATUS Status of the Truck
ALERT_ADDRESS Mobile or Pager address to send alert to (Portal User Interface)
DRIVER_ID ID of the Truck Driver

To Create Database Objects

  1. The master schema is available in the Oracle Database Server. If the master schema is not available, enter the following command using the Command Prompt window.


    Note:

    Ensure that the CLASSPATH includes classes12.jar or classes12.zip.

    > msql system/manager@jdbc:oracle:thin:@<HOST>:<PORT>:<Service_Name>
    SQL> create user master identified by master;
    SQL> grant connect,resource to master;
    
    

    The variable <HOST> refers to the machine name where the Oracle database is installed.

    The variable <PORT> refers to the Oracle database listener port.

  2. Enter the following commands to create database objects in the Oracle Database Server.

    > cd ORACLE_HOME\Mobile\Sdk\samples\ado.net\Transport
    
    > msql master/master@jdbc:oracle:thin:@<HOST>:<PORT>:
          <Service_Name> @create.sql
    

    Note:

    While entering the above command to create database objects, you must include a mandatory space between "<Service_Name>" and "@create.sql".

22.2.2 Writing the Application Code

The Pocket PC Transport application's Visual Basic.NET (Visual Studio.NET 2003) is readily available with the sample application. The following section explains the code written for the Transport application and is presented below.

22.2.2.1 Transport Module (Transport.vb)

To open a database connection, you must declare a connection object. In this tutorial, the connection object is called conn. The scope of the connection object is project level. The Connect sub-routine in the transport.vb module establishes a connection to a DSN named TRANSPORT. This DSN name is mentioned in the Packaging Wizard. For more information refer, Section 22.3.2, "Defining the Application Connection to the Oracle Database Server".

22.2.2.2 Main Form (frmMain.vb)

The frmMain.vb file implements the main form of the Transport Tutorial application. This form connects to Oracle Database Lite on Load time and invokes the Create Package and View Packages forms, using the appropriate command buttons.

22.2.2.3 View Packages (frmView.vb)

This form displays existing packages from the database. It also allows the user to modify and save existing packages. This form demonstrates the usage of the OracleDataAdapter and DataSet classes.

When this form is loaded, it creates an instance of the OracleDataAdapter object and sets the appropriate OracleCommand objects namely, Select, Update, and Delete. These OracleCommand objects are created by the transport.vb module during the main form loading process. Once an OracleAdapter object has been created successfully, this form creates a Dataset object and populates it with data from Oracle Database Lite, using the OracleDataAdapter object that was created.

dba = New OracleDataAdapter
 dba.SelectCommand = cmdSel
 dba.DeleteCommand = cmdDel
 dba.UpdateCommand = cmdUpd
 
 ' Fill dataset
 '
 dset = New DataSet
 dba.Fill(dset)

Once the Dataset is filled with Oracle Database Lite data, this form populates the UI controls using data from the DataSet object.

 Dim table As DataTable = dset.Tables(0)
 Dim rows As DataRowCollection = table.Rows
 Dim row As DataRow = rows.Item(index)
 
 Me.packDesc.Text = row.Item(1).ToString()
 Me.packWeight.Text = row.Item(2).ToString()
 Me.packStreet.Text = row.Item(3).ToString()
 Me.packCity.Text = row.Item(4).ToString()
 Me.packState.Text = row.Item(5).ToString()
 Me.packRoute.Text = row.Item(7).ToString()

When users make changes to the package data, this form uses OracleAdapter's Update method to save the changes to Oracle Database Lite.

 Dim row As DataRow = table.Rows(index)
 row.BeginEdit()
 row(6) = Me.packPriority.SelectedItem.ToString()
 row(8) = Me.packStatus.SelectedItem.ToString()
 row.EndEdit()
 dba.Update(table)

22.2.2.4 Create Package (frmNew.vb)

This form allows users to create a new package entry in Oracle Database Lite. During the form's Load duration, this form creates a unique Package ID and populates the drop down list controls with Truck Numbers and Route Names.

When the user saves this form, it uses the OracleCommand and OracleParameter classes to save user changes in Oracle Database Lite.

cmd = GetConnection().CreateCommand()
rts = Me.packRoute.SelectedItem.ToString()
 
' Obtain route number
'
cmd.CommandText = "SELECT ROUTE_NO FROM ROUTES where ROUTE_NM='" & rts & "'"
res = cmd.ExecuteReader()
 While res.Read() = True
 rtn = res.GetString(0)
 End While
res.Close()
 
cmd.CommandText = "INSERT INTO PACKAGES ( 
 (DID,DDSC,DWT,DSTR,DCTY,DST,DRTNR,DRTNM,DSTS,TID,PRTY,PTNO,TIND) values 
 (?,?,?,?,?,?,?,?,'NEW',?,?,'1','P')"
 
' Set DID
'
par = cmd.CreateParameter()
par.DbType = DbType.String
par.Direction = ParameterDirection.Input
par.Value = id
cmd.Parameters.Add(par)
 
 ' Set DDSC
 '
par = cmd.CreateParameter()
par.DbType = DbType.String
par.Direction = ParameterDirection.Input
par.Value = Me.packDesc.Text
cmd.Parameters.Add(par)
 
 ......................
 ......................
 
cmd.ExecuteNonQuery()
cmd.Dispose()

22.2.3 Compiling the Application

To install the application on the device, you must create a CAB file. The CAB file is uploaded into the Mobile Server Repository during the application's publish phase. You can create a CAB file using the Visual Basic.NET (Visual Studio.NET 2003).

22.2.3.1 Creating CAB Files

To build CAB files for the Transport Tutorial application, right click on the 'Transport' project tree view object on the 'Solution Explorer' window of Visual Studio.NET 2003. Choose the 'Build CAB File' object from the popup menu.

To create the CAB file, select the Application Install Wizard... submenu from the Remote Tools option under the Tools menu in the Visual Basic.NET (Visual Studio.NET 2003) IDE.

  1. Open the Project file ".ebp" of the application by entering the following value.

    <ORACLE_HOME>\Mobile\Sdk\samples\ado.net\Transport

  2. Enter the directory name of the ".vb" file that you created and saved in the previous section.

  3. Enter a directory name to store the CAB files. For example: "C:\Transportinstall.

  4. Select the required processor for which you want to create a CAB file. For example, ARM 1100.

  5. The Application Install Wizard displays default Active X Controls. Accept the default controls and click Next.

  6. The next window prompts you to include additional files such as images to the application. The current application has two image files namely, ipaq.bmp and Olite.bmp. Both files are not system files. Click Next.

  7. Enter "Transport" as the value for all fields in the Application Install Wizard except in the "Company Name" field. Enter "Oracle" as the value for the "Company Name" field.

  8. Click Create Install.

    The Application Install Wizard creates CAB files for the selected processors and saves them under the "C:\Transportinstall\CD1" directory.

    To skip the steps in this section for creating a CAB file, a cab.zip file is provided in the following directory.

    <ORACLE_HOME>\Mobile\Sdk\samples\ado.net\Transport

22.2.3.2 Installing the Application from the CAB File

You can download and install the application on the device after packaging and publishing the application. The following sections describe how to package and publish the application.

22.3 Packaging and Publishing the Application

This section describes how to package the application and prepare it for publishing into the Mobile Server. To package and publish the application, you must perform the following tasks.

  1. Define the application using the Packaging Wizard.

  2. Define the application connection to the Oracle Database Server.

  3. Define the snapshot.

  4. Publish the application.

22.3.1 Defining the Application Using the Packaging Wizard

Using the Packaging Wizard, you can select and describe the Transport application.

22.3.1.1 Creating a New Application

Using the Mobile Server's Packaging Wizard, you can create or modify a Pocket PC application and publish the Pocket PC application into the Mobile Server. For more information on how to use the Packaging Wizard, see the Oracle Database Lite Tools and Utilities Guide.

You can select and describe the Pocket PC Transport application by launching the Packaging Wizard in regular mode.

To launch the Packaging Wizard in regular mode, perform the following steps.

  1. Using the Command Prompt, enter the following.

    cd ORACLE_HOME\mobile\sdk\bin

    wtgpack

    As Figure 22-1 displays, the Packaging Wizard displays the Welcome panel. Select the Create a new application option and click OK.

    Figure 22-1 Welcome Dialog

    The Welcome dialog enables you to create a new application.
    Description of the illustration pw_wel.gif

  2. The Select Platforms panel appears. Choose WinCE from the list displayed and click Next.

  3. The Application panel appears. As Table 22-5 describes, enter the Pocket PC Transport application settings. Figure 22-2 displays the Applications panel.

    Figure 22-2 Applications Panel

    The Applications panel
    Description of the illustration app.gif

    Table 22-5 The Pocket PC Transport Application Settings

    Field Value
    Application Name Transport
    Virtual Path /Transport
    Description Transport and Logistics Management
    Local Application Directory <ORACLE_HOME>\Mobile\Sdk\samples\ado.net\Transport
    Publication Name Leave this field blank.

  4. Click Next. As Figure 22-3 displays, the Files panel appears.

    The Files panel automatically lists all files that reside in the directory, based on the 'Local Application Directory' specified in the previous Application panel. Ensure that you select the correct CAB file from the directory in which you saved the CAB file, using the Application Install Wizard.

    For example, in this tutorial, you must select the Transport_PPC.ARM.CAB because your target device is Pocket PC with the ARM chipset.

22.3.2 Defining the Application Connection to the Oracle Database Server

After selecting the appropriate CAB file, you must define the application connection details to the Oracle Database Server.

On the Files panel, click Next. As Figure 22-4 displays, the Database panel appears. It enables you to define the Transport application's connection information to the Oracle Database Server.

The Client Side Database Name field refers to the Data Source Name (DSN) for the Oracle Database Lite database file, which is automatically created on the device. In this filed, enter the value 'transport'.

22.3.3 Defining Snapshots

After specifying the application connection details, you must define the snapshots used by your Mobile application. The Snapshots panel defines database tables that contain your Mobile application data and is used for periodic synchronization. It enables you to define the synchronization logic for the Transport application. The Packaging Wizard also enables you to import table definitions from the Oracle Database Server.

To define snapshots for the Transport application, perform the following steps.

  1. On the Database panel, click Next. As Figure 22-5 displays, the Snapshots panel appears. To import the table definition from the Oracle Database Server, click Import. The Connect To Database dialog appears. Enter values as specified in Table 22-6.

    Figure 22-5 Snapshots Panel

    The Snapshots panel
    Description of the illustration snapshots.gif

    Table 22-6 Connect to Database Dialog Description

    Field Description Value
    User Name Schema name (database user name) which has the database object master
    Password Password of the schema owner master
    URL jdbc:oracle:thin:@<HOST>:<PORT>:<Service_Name> jdbc:oracle:thin:@ssinghan-pc:1521:webtogo


    Note:

    If you do not have the database object on the Oracle Server, you can still create one using the New button on the Snapshots panel.

  2. Click OK. The Tables dialog appears and displays a list of available tables.

    Select the Packages, Trucks, and Routes tables. Click Add and click Close. The Snapshots panel displays the chosen database tables.

  3. Select the Packages table and click Edit. As Figure 22-6 displays, the Edit Snapshots panel appears.

    Figure 22-6 Edit Snapshots Panel

    The Edit Snapshots panel
    Description of the illustration ed_snap.gif


    Note:

    Ensure that the Create on Client box is selected. If the Create on Client box is not selected, the corresponding snapshot is not created on the Oracle Database Lite client.

  4. To control the order in which the snapshots are refreshed on the client, you must change the weight for the Packages table to 1. Clear the Generate SQL box, as you have already created database objects in the Oracle Database Server and hence, do not need to create SQL for creating the database.

  5. Click the WinCE tab. You must ensure that the Create on Client box is selected, and the Template field displays the following SQL statement.

    SELECT * FROM MASTER.PACKAGES


    Note:

    To update the snapshot on a client, you must ensure that the Updatable? box is checked. If the Updatable? box is not checked, the data synchronization will always be unidirectional from the Oracle Database, and all changes made from the device will be lost.

  6. Click OK.

  7. In the Snapshots panel, select the Routes table and click Edit. The Edit Snapshot dialog appears.

  8. To control the order in which snapshots are refreshed on the client, change the weight for the Routes table to 2. Clear the General SQL box, as you have already created database objects in the Oracle Database Server and do not need to create SQL for creating the database.


    Note:

    As we do not update the Routes and Trucks tables in this tutorial, users must clear the Updatable? box, but ensure that the Create on Client box is selected.

  9. Ensure that the Create on Client box is selected, and the Template field displays the following SQL statement.

    SELECT * FROM MASTER.ROUTES

  10. Repeat steps 8 through 10 for the TRUCKS table. Use 3 as the value for weight.

  11. Click Next. The DDLs panel dialog appears.

  12. Click Finish. The Application Definition Completed dialog appears.

22.3.4 Publishing the Application

Using the Application Definition Completed dialog, you can package and publish the Pocket PC Transport application.

To publish the Transport application, perform the following steps.

  1. In the Application Definition Completed dialog, select the Publish the Current Application option and click OK.

  2. The Publish the Application dialog appears. As Table 22-7 describes, enter the specified values.

    Table 22-7 Publish the Application Dialog Description

    Field Description Value
    Mobile Server URL URL or IP Address of the machine where the Mobile Server is running. <Mobile Server>/webtogo
    Mobile Server User Name User name of the Mobile Server user with administrative privileges. Administrator
    Mobile Server Password Password of the Mobile Server user with administrative privileges. admin
    Repository Directory Directory name where all files for this application will be stored inside the Mobile Server Repository. /transport
    Public Application Do not select this check box unless you want to make this application available to all users. Clear

  3. To publish the application in the Mobile Server Repository, click OK. A dialog displays the application's publishing status. You must wait until the application is published.

  4. To confirm that the application is published successfully, click OK.

  5. To exit the Packaging Wizard, click Exit.

At this stage, you have completed all the development tasks required for packaging or publishing the application.

22.4 Administering the Application

This section describes how to administer the Mobile application published by you into the Mobile Server. To administer the application, perform the following tasks.

  1. Start the Mobile Server.

  2. Launch the Mobile Manager.

  3. Create a new user.

  4. Set application properties.

  5. Grant user access to the application.

  6. Start MGP.

For more information on the Mobile Manager see the Oracle Database Lite Administration and Deployment Guide.

22.4.1 Starting the Mobile Server

To start the Mobile Server in standalone mode, enter the following command using the Command Prompt.

> runmobileserver

22.4.2 Launching the Mobile Manager

Using the login user name and password, you can log in to the Mobile Server and launch the Mobile Manager.

To start the Mobile Manager, perform the following steps.

  1. Open your Web browser and connect to the Mobile Server by entering the following URL.

    http://<mobile_server>/webtogo


    Note:

    You must replace the <mobile_server> variable with your Mobile Server's host name.

  2. Log in as the Mobile Server administrator using administrator as the User Name and admin as the Password.

  3. To launch the Mobile Manager, click the Mobile Manager link in the workspace. The Mobile Server farms page appears. To display your Mobile Server's home page, click your Mobile Server link.

    Figure 22-7 displays the Mobile Server home page.

    Figure 22-7 Mobile Server Home Page

    The Mobile Server home page
    Description of the illustration ms_home.gif

22.4.3 Creating a New User

To create a new Mobile Server user, perform the following steps.

  1. In the Mobile Manager, click the Users tab.

  2. Click Add User.

  3. Enter data as described in Table 22-8.

  4. Click Save. The Mobile Manager displays a confirmation message.

  5. Click OK.

Table 22-8 lists the values that you must enter in the Add User page.

Table 22-8 The Add User Page Description

Field Value
Display Name bob
User Name bob
Password bobhope
Password Confirm Re-enter the password for confirmation
System Privilege Select the "User" option

22.4.4 Setting the Application Properties

To set the Pocket PC Transport Application's properties, perform the following steps.

  1. In the Mobile Manager, click the Applications tab. As Figure 22-8 displays, The Applications page appears. You can search the list of available applications by application name.

    Figure 22-8 Applications Page

    The Applications page
    Description of the illustration ce_apps.gif

  2. Click Transport. The Transport application page appears. It displays an application's properties and database connectivity details.

  3. In the Platform Name, select Oracle Lite PPC2000 ARM; US. In the Database Password field, enter "master". This is the default password for the "master" user schema of the Oracle Server Database.

  4. Click Save.

22.4.5 Granting User Access to the Application

To grant user access to the Transport application, perform the following steps.

  1. In the Transport application page, click the Access link. As Figure 22-9 displays, the Access page lists application users and application groups. To grant access to a user or a group of users to the Transport application, select the corresponding boxes.

    For example, to provide access to a user named BOB, locate the user name "BOB" in the Users list and select the corresponding box.

  2. Click Save. The user "BOB" is granted access to the Transport application.

    Figure 22-9 displays the Access page of the Transport application.

22.4.6 Starting the Message Generator and Processor (MGP)

In the Oracle Database Lite 10g Asynchronous replication model, a client does not wait for the server to prepare the payload. A payload contains data that will be synchronized. The Mobile Server prepares the payload for all Mobile clients asynchronously by running the MGP process in the background at all times. Hence, when a Mobile Client initiates the synchronization process, the Mobile Server uploads the client payload into an in-queue and picks up the payload for the client from the corresponding out-queue. The MGP processes payloads in the in-queues and out-queues and performs database operations with the Oracle Server in the background.

To start the MGP, perform the following steps.

  1. Navigate to the Mobile Manager Home page and click Jobs in the Components list. The Job Scheduler page appears.

  2. Click the Start button.

    Figure 22-10 displays the Job Scheduler page.

    Figure 22-10 Job Scheduler Page

    The Job Scheduler page
    Description of the illustration ce_jobshed.gif

22.5 Running the Application on the Pocket PC

This section describes how to run the application after creating, testing, deploying, and administering the application. To run the application, perform the following tasks.

  1. Install the Oracle Database Lite Mobile Client for Pocket PC.

  2. Install and synchronize the Transport application.

22.5.1 Installing the Oracle Database Lite Mobile Client for Pocket PC

To install the Oracle Database Lite Mobile Client for Pocket PC, perform the following actions.

  1. Open your desktop browser and enter the following URL to connect to the Mobile Server.

    http://<Mobile_Server>/webtogo/setup


    Note:

    You must replace the <Mobile_Server> variable with the host name or IP address of your Mobile Server.

    A Web page appears displaying links to various Oracle Database Lite Mobile Clients with different platforms. You can filter the selection by Language and Platform.

  2. Click the hyperlink Oracle Lite PPC2000 ARM to access the setup program for the Pocket PC device with the ARM chipset.

    Figure 22-11 displays the Mobile Client Setup page.

    Figure 22-11 Mobile Client Setup Page

    The Mobile Client setup page
    Description of the illustration ce_mcsetup.gif

  3. If you are using Netscape as your browser, choose a location on your desktop to save the setup program and click OK. Open the Windows Explorer program and locate the "setup.exe". To run the setup program, double-click "setup.exe".

    If you are using Internet Explorer, run the "setup" program from your browser window. Once started, the setup program asks you to provide the user name and password to log on to the Mobile Server. Enter BOB as the User Name and bobhope for the Password. Click OK.

  4. The setup program asks you to provide an install directory. Use the default directory C:\mobileclient\olite, and click OK. To confirm your install directory, click Yes.

  5. The setup program automatically downloads all the required components to the specified destination on your desktop computer.

  6. Assume that you have a Pocket PC device attached to your desktop computer and are connected with Microsoft's ActiveSync. The installation for your Pocket PC device starts automatically.

  7. Click Yes to confirm installing Oracle Lite PPC ARM; US to the default application directory. The application's installation starts on the device. Once completed, the Mobile Client for Pocket PC is installed on your device under the \ORACE directory.

22.5.2 Installing and Synchronizing the Transport Application and Data

To install the Transport application and data, perform the following steps.

  1. On the device, locate and tap the msync application icon in the programs group.

  2. The msync dialog appears. To download the Transport application and snapshots for user BOB, enter data as described in Table 22-9.

    Table 22-9 Values You Must Enter in the msync Dialog

    Name Value
    UserName bob
    Password bobhope (all lowercase)
    Save password box Select
    Server Machine name or IP address

    Figure 22-12 displays the msync dialog on the Pocket PC.

    Figure 22-12 Running msync on Pocket PC

    The mSync dialog
    Description of the illustration fig_a_22.gif

  3. To save these values, tap Apply.

  4. To synchronize your application and data to the device, tap the Sync button.


    Note:

    Ensure that the device is connected to the desktop or the network and that the Mobile Server is running.

  5. After the synchronization process is complete, a transport.odb file is created under the \OraCE directory and the Transport application is installed on the Pocket PC automatically.

  6. Using the Start menu on the device, locate the Transport application in the Programs menu.

  7. To run the Transport application, tap the Transport icon.