Oracle® Database Lite Developer's Guide Release 10.3 Part Number E12090-02 |
|
|
View PDF |
You can implement Mobile applications with Oracle Database Lite for WinCE. Oracle Database Lite supports various application models for the Windows Mobile/Pocket PC device, such as ODBC, JDBC, and ADO.NET. When developing your own WinCE application, you can use Visual Studio.Net 2003 or 2005.
This chapter uses a tutorial to demonstrate how to create, deploy, administer, and use a Windows CE application. The tutorial shows a Visual Basic.NET (Visual Studio.NET) application that uses the Oracle Database Lite ADO.NET interface for Windows Mobile.
Note:
If you use Visual Studio.Net 2005 and/or ADO.Net, you must install the ODBC 3.5 driver. See Section 5.1.3, "ODBC" for details.The following sections detail the development process:
The sample WinCE application details typical activities of delivery personnel in the Transportation and Logistics industry, which includes package pick-up and delivery.
Before he leaves the dispatch center, the delivery person collects the complete delivery package list and the package delivery destination information for the day on his device.
As he delivers and picks-up packages, the delivery person updates the package pick-up and delivery status on his client device.
When he returns to the dispatch center, he synchronizes his updated information with the central server running in the dispatch center over any wireless network.
Before starting the Mobile application development process, you must ensure that the development computer and the client device meet the requirements specified below.
Table 14-1 lists the configuration and installation requirements for the Mobile application development computer.
Table 14-1 Application Development Computer Requirements
Requirement | Description |
---|---|
Windows User Login |
The login user on the Windows 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 9.2 or higher. The Mobile Server (Oracle Database Lite CD-ROM). The Mobile Development Kit (Oracle Database Lite CD-ROM). |
Installed Windows Mobile/Pocket PC Components |
Microsoft Active Sync 3.8 or higher. |
You must connect the client device to the desktop and install the Oracle Database Lite client for Pocket PC on the device. For more information on how to install the Mobile Client on the device, see Section 14.6.1, "Install the Oracle Database Lite Mobile client for Pocket PC".
This section explains how to develop and test the WinCE Transport application using the Mobile Development Kit. The WinCE Transport application is written in Visual Basic.NET (Visual Studio.NET).
To develop and test the WinCE Transport application, perform the following tasks.
During deployment, the Mobile Server automatically creates the Oracle Database Lite database in the client device along with the requisite tables and data. To publish the application, users must create the database objects used by the application in the back-end Oracle database.
The WinCE Transport application uses the following database objects:
Packages Table
Routes Table
Trucks Table
Table 14-2 lists the columns for the Packages table for storing information about the package.
Table 14-2 Packages Table
Column | Description |
---|---|
|
Package ID |
|
Package Description |
|
Package Weight |
|
Destination Street |
|
Destination City |
|
Destination State |
|
Route Number |
|
Route Name |
|
Signature |
|
Package Status |
|
Truck Number |
|
Priority |
|
Point Number |
|
Delivery 'D', or Pick-up 'P' |
Table 14-3 lists the columns for the Routes table for storing information about a route.
Table 14-3 Routes Table
Column | Description |
---|---|
|
Route Number (Primary Key) |
|
Route Name |
|
Estimated Time |
Table 14-4 lists columns for the Trucks table for storing information about the availability status and destination information for a truck.
Table 14-4 Trucks Table
Column | Description |
---|---|
|
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
In order to execute the Transport demo, you must set up the schema and the database objects. We have provided a SQL script that will create the master
schema and the database objects in the back-end. However, if the master
schema is already created, then remove the statements that create this schema from the create.sql
script.
Note:
Ensure that theCLASSPATH
includes ojdbc14.jar
.Execute the create.sql
script, as follows:
> cd ORACLE_HOME\Mobile\Sdk\samples\ado.net\wince\Transport\sql
> msql system/<sys_pwd>@jdbc:oracle:thin:@<host>:<port>:<oracle_sid> @create.sql
Note:
While entering the above command to create database objects, you must include a mandatory space between "<oracle_sid>"
and "@create.sql".
Where:
<sys_pwd>
is the system password. This is required if you are creating the master
schema. However, if you have eliminated the statements that create the schema, you can use master/master
for username/password.
<host>:<port>
refers to the name and listening port of the machine where the back-end Oracle database is installed.
The WinCE Transport application, located in cd ORACLE_HOME
\Mobile\Sdk\samples\ado.net\wince\Transport
, uses Visual Basic.NET (Visual Studio.NET), which is available with the sample application. The following sections describe the Transport application code:
To open a database connection, you must declare a connection object,. which—in this tutorial—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 the local Oracle Lite database with the DSN transport
; the Disconnect
sub-routine releases the connection.
Within the Connect
sub-routine, the DSN is initialized as follows:
Dim dsn As String = "dsn=transport;uid=system;pwd=" & pwd conn = New Oracle.DataAccess.Lite.OracleConnection(dsn) conn.Open()
The DSN username and password are system
and the user password; thus, only the user can connect since the user password is used.
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.
If the synchronization button is pushed, notice that the following is executed:
Disconnect() OracleEngine.Synchronize(True) Connect(UserName, Password)
In order to retrieve information from the database, the connection was established at the start of the application. Since you can only have a single connection to the back-end database—and the OracleEngine.Synchronization
method creates a connection to the database as part of its functionality—the original connection is disconnected before the synchronization is invoked. Once synchronization is complete, the original connection is re-established. See Section 4.4.2, "Using the OracleEngine to Synchronize" for more information on this class.
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.
Note:
TheOracleDataAdapter
is the same as the Microsoft ADO.Net DataAdapter
class. For more information on DataAdapter
and DataSet
classes, see the Microsoft ADO.Net documentation.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.
Note:
For more information on theOracleCommand
class, see Section 8.1.3, "Create Commands With the OracleCommand
Class" in the Oracle Database Lite Client Guide.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 the OracleAdapter
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 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.
Note:
For more information on theOracleCommand
class, see Section 8.1.3, "Create Commands With the OracleCommand
Class" in the Oracle Database Lite Client Guide.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).
To create the CAB file for this demo, perform the following:
Start the Visual Studio.NET and click on File->Select Open
Browse for the Transport.sln
file, which is located in the ORACLE_HOME
\Mobile\SDK\samples\ado.net\wince\Transport
directory. Ignore the warning message, "The .NET assembly 'Oracle.DataAccess.Lite.dll' could not be found.
"
Right click on References.
Select Add Reference.
Click Browse and choose Oracle.DataAccess.Lite.dll
from the ORACLE_HOME
\Mobile\SDK\ado.net\wince\v1.x
or v2.x
directory.
In the 'Solution Configuration' list box, select Release instead of Debug.
Click Build->Build CAB File, which will build the CAB file for you.
You can download and install the application on the device after packaging and publishing the application. See Section 14.4, "Package and Publish the Application" for directions on how to package and publish the application.
As described fully in Chapter 6, "Using Mobile Database Workbench to Create Publications", you can use MDW to create your publication. Launch MDW by executing oramdw
from $ORACLE_HOME
/Mobile/Sdk/bin
. The following sections detail how to use MDW to create a publication for the application in this tutorial.
Note:
While creating this publication, use Chapter 6, "Using Mobile Database Workbench to Create Publications" heavily for a deeper understanding of how to use MDW and the type of information that you must enter.Create a new project for this application by selecting File->New->Project. This brings up a wizard where you enter the following information:
Note:
For more information, see Section 6.2, "Create a Project".Define a name and location for the project.
Enter the username, password, JDBC driver type, database host, database port and database SID for the Mobile repository.
Provide the Mobile Repository access information. Because you are interacting with the repository to create and manipulate synchronization objects, including the SQL scripts for the publication items, you need access to the Mobile Repository.
Specify schema username and password. Enter the user and password of the schema owner for the schema that you are using for the Mobile application. The Mobile application schema contains all database tables, views, synonyms used to build the snapshots for the application.
Verify the information that you entered and click Finish.
For this project, you need to create three publication items for packages, routes, and trucks. Start the new publication item wizard by selecting File->New->Publication Item.
Note:
For more information, see Section 6.4, "Create a Publication Item".Enter the name as packages
and the type as Fast
.
Select the schema name as MASTER
, the object type as Table
, and leave the object filter blank. Click Search. When the search ends, select Packages
from the object list.
Click '>>
' to select all of the columns in the Packages
table.
In the Query tab, select Edit if you want to edit the query.
Click Run to test.
Verify and click Finish.
Enter the name as routes
and the type as Fast
.
Select the schema name as MASTER
, the object type as Table
, and leave the object filter blank. Click Search. When the search ends, select Routes
from the object list.
Click '>>
' to select all of the columns in the Routes
table.
In the Query tab, select Edit if you want to edit the query.
Click Run to test.
Verify and click Finish.
Enter the name as trucks
and the type as Fast
.
Select the schema name as MASTER
, the object type as Table
, and leave the object filter blank. Click Search. When the search ends, select Trucks
from the object list.
Click '>>
' to select all of the columns in the Trucks
table.
In the Query tab, select Edit if you want to edit the query.
Click Run to test.
Verify and click Finish.
When you have completed the creation of the publication items, create the publication within the project by selecting File->New->Publication.
In the General tab, enter the name as transport
, which is the DSN for the client-side database.
In the Publication Item tab, add the three publication items that you just created with the following configuration:
Name: PACKAGES Updatability: Updatable Conflict Resolution: Server Wins DML Callback: BLANK Grouping Function: BLANK Priority Condition: BLANK My Compose: BLANK Weight: 1 Description: Blank Name: ROUTES Updatability: Read Only Conflict Resolution: Custom DML Callback: BLANK Grouping Function: BLANK Priority Condition: BLANK My Compose: BLANK Weight: 2 Description: Blank Name: TRUCKS Updatability: Read Only Conflict Resolution: Custom DML Callback: BLANK Grouping Function: BLANK Priority Condition: BLANK My Compose: BLANK Weight: 3 Description: Blank
Save the publication by selecting File->Save.
The following sections describe how to package the application and prepare it for publishing into the Mobile Server:
Using the Packaging Wizard, you can select and describe the Transport application.
Using the Mobile Server Packaging Wizard, you can publish the WinCE application into the Mobile Server. For more information on how to use the Packaging Wizard, see Chapter 7, "Using the Packaging Wizard".
You can select and describe the WinCE 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.
cd ORACLE_HOME
\Mobile\SDK\bin
wtgpack
As Figure 14-1 displays, the Packaging Wizard displays the Welcome panel. Select the Create a new application option and click OK.
The Select Platforms panel appears. Choose 'Oracle Lite PPC50 ARMV4I;US' from the list displayed and click Next.
The Application panel appears. As Table 14-5 describes, enter the WinCE Transport application settings. Figure 14-2 displays the Applications panel.
Table 14-5 The WinCE Transport Application Settings
Field | Value |
---|---|
Application Name |
Transport |
Virtual Path |
|
Description |
Transport and Logistics Management |
Local Application Directory |
< |
Publication Name |
Select Browse to locate the publication that was created by MDW, named |
Click Next. As Figure 14-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.
For example, in this tutorial, you must select the Transport_PPC.ARMV4.CAB
and Transport_PPC.ARMV4.DAT
, because your target device is Pocket PC with the ARM chipset. If other .CAB
and .DAT
files are in this listing, then use the Delete button in the Files panel to delete these files from the list.
After selecting the appropriate CAB file, you must define the application connection details to the Oracle Lite database.
On the Files panel, click Next.
Using the Application Definition Completed dialog, you can package and publish the WinCE 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 14-6 describes, enter the specified values.
Table 14-6 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 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.
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.
> runmobileserver
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.
http://<mobile_server>/webtogo
Note:
You must replace the<mobile_server>
variable with your Mobile Server's host name.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 14-4 displays the 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 14-7.
Click Save. The Mobile Manager displays a confirmation message.
Click OK.
Table 14-7 lists the values that you must enter in the Add User page.
To set the WinCE Transport Application properties, perform the following steps.
In the Mobile Manager, click the Applications tab. As Figure 14-5 displays, The Applications page appears. You can search the list of available applications by application name.
Click Transport. The Transport application page appears. It displays an application's properties and database connectivity details.
In the Platform Name, select Oracle Lite PPC50 ARMV4I; US. In the Database User field, enter master
for the master
schema. In the Database Password field, enter master
. This is the default password for the master
user schema of the Oracle Server Database.
Click Apply.
To grant user access to the Transport application, perform the following steps.
In the Transport application page, click the Access link. As Figure 14-6 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 14-6 displays the Access page of the Transport application.
The following sections describe how to run the application after creating, testing, deploying, and administering the application:
Section 14.6.1, "Install the Oracle Database Lite Mobile client for Pocket PC"
Section 14.6.2, "Install and Synchronize the Transport Application and Data"
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.
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.
Click the hyperlink Oracle Lite PPC50 ARMV4I;US to access the setup program for the Pocket PC device with the ARM chipset.
Figure 14-7 displays the 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 "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.
The setup program asks you to provide an install directory. Enter the directory where you want to install the client, such as C:\mobileclient\
. 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 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 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 14-8.
Table 14-8 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 14-8 displays the msync dialog on the Pocket PC.
To save these values, click Apply.
To synchronize your application and data to the device, click Sync. If you receive an error message for invalid username/password, re-enter the clear text password in the login window.
Note:
Ensure that the device is connected to the desktop or the network and that the Mobile Server is running.Once the synchronization is complete, click Exit. The Update window appears.
Note:
After the synchronization process is complete, atransport.odb
file is created under the \OraCE
directory.Click Install to install the application. Click Exit.
Using the Start menu on the device, locate the Transport application in the Programs menu.
To run the Transport application, click the Transport icon.