Oracle® Database Lite Developer's Guide
Part No. B15920-01
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:
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.
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.
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
|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
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".
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.
Create database objects in the Oracle database.
Write the application code.
Compile the application.
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.
The Pocket PC Transport application uses the following database objects.
Table 22-2 lists the columns of packages that enable you to store all information about the package.
Table 22-2 Packages Table
||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
||Route Number (Primary Key)|
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
||Truck Number (Primary Key)|
||Status of the Truck|
||Mobile or Pager address to send alert to (Portal User Interface)|
||ID of the Truck Driver|
To Create Database Objects
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
> msql system/manager@jdbc:oracle:thin:@<HOST>:<PORT>:<Service_Name> SQL> create user master identified by master; SQL> grant connect,resource to master;
<HOST> refers to the machine name where the Oracle database is installed.
<PORT> refers to the Oracle database listener port.
Enter the following commands to create database objects in the Oracle Database Server.
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 "
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.
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".
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.
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
When this form is loaded, it creates an instance of the
OracleDataAdapter object and sets the appropriate OracleCommand objects namely,
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)
Dataset is filled with Oracle Database Lite data, this form populates the UI controls using data from the
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)
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
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()
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).
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.
Open the Project file "
.ebp" of the application by entering the following value.
Enter the directory name of the "
.vb" file that you created and saved in the previous section.
Enter a directory name to store the CAB files. For example: "
Select the required processor for which you want to create a CAB file. For example, ARM 1100.
The Application Install Wizard displays default Active X Controls. Accept the default controls and click Next.
The next window prompts you to include additional files such as images to the application. The current application has two image files namely,
Olite.bmp. Both files are not system files. Click Next.
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.
Click Create Install.
The Application Install Wizard creates CAB files for the selected processors and saves them under the "
To skip the steps in this section for creating a CAB file, a cab.zip file is provided in the following directory.
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.
Define the application using the Packaging Wizard.
Define the application connection to the Oracle Database Server.
Define the snapshot.
Publish the application.
Using the Packaging Wizard, you can select and describe the Transport 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.
Using the Command Prompt, enter the following.
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 Select Platforms panel appears. Choose WinCE from the list displayed and click Next.
Figure 22-2 Applications Panel
Click Next. As Figure 22-3 displays, the Files panel appears.
Figure 22-3 Files Panel
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.
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.
Figure 22-4 Database Panel
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'.
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.
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
Table 22-6 Connect to Database Dialog Description
|User Name||Schema name (database user name) which has the database object||master|
|Password||Password of the schema owner||master|
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.
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.
Select the Packages table and click Edit. As Figure 22-6 displays, the Edit Snapshots panel appears.
Figure 22-6 Edit Snapshots Panel
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.
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.
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.
In the Snapshots panel, select the Routes table and click Edit. The Edit Snapshot dialog appears.
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.
Ensure that the Create on Client box is selected, and the Template field displays the following SQL statement.
SELECT * FROM MASTER.ROUTES
Repeat steps 8 through 10 for the
TRUCKS table. Use 3 as the value for weight.
Click Next. The DDLs panel dialog appears.
Click Finish. The Application Definition Completed dialog appears.
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.
In the Application Definition Completed dialog, select the Publish the Current Application option and click OK.
The Publish the Application dialog appears. As Table 22-7 describes, enter the specified values.
Table 22-7 Publish the Application Dialog Description
|Mobile Server URL||URL or IP Address of the machine where the Mobile Server is running.||
|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.||
|Public Application||Do not select this check box unless you want to make this application available to all users.||Clear|
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.
To confirm that the application is published successfully, click OK.
To exit the Packaging Wizard, click Exit.
At this stage, you have completed all the development tasks required for packaging or publishing 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.
Start the Mobile Server.
Launch the Mobile Manager.
Create a new user.
Set application properties.
Grant user access to the application.
For more information on the Mobile Manager see the Oracle Database Lite Administration and Deployment Guide.
To start the Mobile Server in standalone mode, enter the following command using the Command Prompt.
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.
Open your Web browser and connect to the Mobile Server by entering the following URL.
Note:You must replace the
Log in as the Mobile Server administrator using
administrator as the User Name and
admin as the Password.
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
To create a new Mobile Server user, perform the following steps.
In the Mobile Manager, click the Users tab.
Click Add User.
Enter data as described in Table 22-8.
Click Save. The Mobile Manager displays a confirmation message.
Table 22-8 lists the values that you must enter in the Add User page.
To set the Pocket PC Transport Application's properties, perform the following steps.
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
Click Transport. The Transport application page appears. It displays an application's properties and database connectivity details.
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.
To grant user access to the Transport application, perform the following steps.
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.
Click Save. The user "BOB" is granted access to the Transport application.
Figure 22-9 displays the Access page of the Transport application.
Figure 22-9 Access Page
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.
This section describes how to run the application after creating, testing, deploying, and administering the application. To run the application, perform the following tasks.
Install the Oracle Database Lite Mobile Client for Pocket PC.
Install and synchronize the Transport application.
To install the Oracle Database Lite Mobile Client for Pocket PC, perform the following actions.
Open your desktop browser and enter the following URL to connect to the Mobile Server.
Note:You must replace the
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.
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
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 "
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.
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.
The setup program automatically downloads all the required components to the specified destination on your desktop computer.
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.
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.
To install the Transport application and data, perform the following steps.
On the device, locate and tap the
msync application icon in the programs group.
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
|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
To save these values, tap Apply.
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.
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.
Using the Start menu on the device, locate the Transport application in the Programs menu.
To run the Transport application, tap the Transport icon.