Oracle® Database Lite Developer's Guide
Part No. B15920-01
This chapter provides an introduction to Oracle Database Lite and presents an overview of the application development process, using the Mobile Development Kit and its components. This chapter discusses the following topics.
Oracle Database Lite facilitates the development, deployment, and management of offline Mobile database applications for a large number of Mobile users. An offline Mobile application is an application that can run on Mobile devices without requiring constant connectivity to the server. An offline database application requires a local database on the Mobile device, whose content is a subset of data that is stored in the enterprise data server. Modifications made to the local database by the application are occasionally reconciled with the server data. The technology used for reconciling changes between the Mobile database and the enterprise database is known as data synchronization.
Offline Mobile database applications can be developed in many ways. The most common way is to develop native C or C++ applications for specific Mobile platforms. C++ applications can access the Oracle Database Lite database using the Simple Object Data Access API (SODA), an easy-to-use C++ interface that is optimized for the object-oriented and SQL functionality of Oracle Database Lite. For more information about SODA, refer the SODA API documentation, which is installed as part of the Mobile Development Kit.
Applications that need a standard interface and work with multiple database engines can use either the Open Database Connectivity (ODBC) interface, Active Data Object (ADO) interface, or some other interface built on top of ODBC. ADO.NET can be used on Win32 and Windows CE. Another way to develop an offline Mobile database application is to use Java and the Java Database Connectivity (JDBC) interface. Oracle Database Lite also offers a third way to develop offline Mobile database applications using the servlet based Web model called Web-to-Go.
Web-to-Go applications can be built using Web technologies, such as servlet, Java Sever Pages (JSP), applet, HTML, and JDBC.
Once the application has been developed, it has to be deployed. Deployment of applications is concerned with setting up the server system so that end users can easily install and use the applications. The nerve center of the server system for Oracle Database Lite applications is the Mobile Server which is where the Mobile applications are deployed. Deployment consists of five major steps:
Designing the server system to achieve the required level of performance, scalability, security, availability, and connectivity. Oracle Database Lite provides tools such as the "Consperf" utility to tune the performance of data synchronization. It also provides benchmark data that can be used for capacity planning for scalability. Security measures such as authentication, authorization, and encryption are supported using the appropriate standards. Availability and scalability are also supported by means of load balancing, caching, and the transparent switch-over technologies of the Oracle Application Server (Oracle AS) and the Oracle database server.
Publishing the application to the server. This refers to installing all the components for an application on the Mobile Server. Oracle Database Lite provides a tool called the Packaging Wizard that can be used to publish applications to the Mobile Server.
Provisioning the applications to the Mobile users. This phase includes determining user accesses to applications with a specified subset of data. Oracle Database Lite provides a tool called the Mobile Manager to create users, grant privileges to execute applications, and define the data subsets for them, among others. You can also use the Java API to provision applications.
Testing for functionality and performance in a real deployment environment. A Mobile application system is a complex system involving many Mobile device client technologies (such as, operating systems, form factors, and so on), many connectivity options (such as, LAN, Wireless LAN, cellular, wireless data, and other technologies), and many server configuration options. Nothing can substitute for the real life testing and performance tuning of the system before it is rolled out. Particular attention should be paid to tuning the performance of the data subsetting queries, as it is the most frequent cause of performance problems.
Determining the method of initial installation of applications on Mobile devices (application delivery). Initial installation involves installing the Oracle Database Lite client, the application code, and the initial database. The volume of data required to install applications on a Mobile device for the first time could be quite high, necessitating the use of either a high-speed reliable connection between the Mobile device and the server, or using a technique known as offline instantiation. In offline instantiation, everything needed to install an application on a Mobile device is put on a CD or a floppy disk and physically mailed to the user. The user then uses this media to install the application on the device by means of a desktop machine. Oracle Database Lite provides a tool for offline instantiation.
After deployment, both the application and the data schema may change because of enhancements or defect resolution. The Oracle Database Lite Mobile Server takes care of managing application updates and data schema evolution. The only requirement is that the administrator must republish the application and the schema. The Mobile Server automatically updates the Mobile clients that have older version of the application or the data.
Oracle Database Lite installation provides you with an option to install the Mobile Server or the Mobile Development Kit. For application development, you will need to install the Mobile Development Kit on your development machine. However, as discussed later in this document, the development examples require the Mobile Server to be running. Hence, if you intend to recreate the sample applications on your system, you must install the Mobile Server, preferably on a different machine. The installation of the Mobile Server requires an Oracle database instance to be running. You can use an existing test database as well. The Mobile Server stores its meta data in this database.
Oracle Database Lite is an integrated framework that simplifies the development, management, and deployment of offline mobile applications on the following mobile platforms, operating systems, and hardware:
|Platform||Programming Languages||Operating System||Hardware|
|Win32 on a laptop or notebook||When you develop on a laptop, you are using one of the Windows operating systems. You can use any of the languages mentioned in this book. However, C, C++ are better for creating applications with a good user interface.
The languages available are C, C++, C#, Java, Visual Basic, JSPs and Servlets.
|Windows 95/98/NT/2000||Pentium processor|
|PocketPC||C, C++, Visual Basic, Java applications (no Servlet or JSP support), SODA, ADO.NET||Windows CE (WinCE)||ARM, xScale|
|Linux||Java, C, C++||Linux Redhat 3.0||X86|
|Palm Pilot||C, C++, use MetroWerks CodeWarrior tool, SODA||PalmOS||ARM, DragonBall|
Oracle Database Lite provides the Mobile Development Kit, which includes facilities, tools, APIs, and sample code for you to develop your applications. There are three application models:
Native applications are built using C, C++, Visual C++, Visual Basic, Embedded Visual tools, ActiveX Data Objects (ADO), and MetroWerks CodeWarrior. The application must be compiled against the mobile device operating system, such as Windows CE or Palm Computing Platform.
Use ODBC to access the Oracle Lite database on the client. Alternatively, you could use JDBC to access the local client database. See Section 2.4.1, "JDBC" and Section 2.4.2, "ODBC" for more information on accessing the database with either of these interfaces.
See Section 8, "Native Application Development" for more information on C and C++.
Standalone Java applications do not include Web and J2EE technology—such as Servlets and JSPs. Instead, Java applications revolve around using JDBC driver to access the Oracle Lite database on the client platform, and use AWT and SWING classes to build the application UI. In addition, the database supports Java stored procedures and triggers.
Your Java/JDBC application must be compiled for the particular mobile device JVM environment, which can be different across various client devices. Thus, when you are developing your Java application, do the following:
Check the environment: Verify that the
olite40.jar, which is located in
OLITE_HOME/bin, is in your
CLASSPATH, which should have been modified during installation.
Load the JDBC driver in to your applications. The following is an example:
Connect to the Oracle Lite database installed on the client. If your database is on the same machine as the JDBC application, connect using the native driver connection URL syntax, as follows:
Or if not local, connect as follows:
You can execute existing Web applications using the J2EE Java technologies, such as servlets and JSPs, in a disconnected mode without modifying the code base. Web-to-Go is a development option for Web applications, and can be executed on laptops using Windows 95/98/NT/2000. Web-to-Go applications use Java servlets and JSPs that may invoke JDBC to access the database, as opposed to using application APIs, such as C or C++.
For more information, see Chapter 7, "Developing Mobile Web Applications".
In the Oracle Database Lite application model, each application defines its data requirements using a publication. A publication is akin to a database schema and it contains one or more publication items. A publication item is like a parameterized view definition and defines a subset of data, using a SQL query with bind variables in it. These bind variables are called subscription parameters or template variables.
A subscription defines the relationship between a user and a publication. This is analogous to a newspaper or magazine subscription. Accordingly, once you subscribe to a particular publication, you begin to receive information associated with that publication. With a newspaper you receive the daily paper or the Sunday paper, or both. With Oracle Lite you receive snapshots, and, depending on your subscription parameter values, those snapshots are partitioned with data tailored for you.
When a user synchronizes the Mobile client for the first time, the Mobile client creates an Oracle Database Lite database on the client machine for each subscription that is provisioned to the user. The Mobile client then creates a snapshot in this database for each publication item contained in the subscription, and populates it with data retrieved from the server database by running the SQL query (with all the variables bound) associated with the publication item. Once installed, Oracle Database Lite is transparent to the end user; it requires minimal tuning or administration.
As the user accesses and uses the application, changes made to Oracle Database Lite are captured by the snapshots. At a certain time when the connection to the Mobile Server is available, the user may synchronize the changes with the Mobile Server. Synchronization may be initiated by the user using the Oracle Database Lite Mobile Synchronization application (msync) directly, by programmatically calling the Mobile Synchronization API from the application, or in the case of Web applications, the synchronization option can be used from the Web-to-Go workspace to synchronize the data. The Mobile Synchronization application communicates with the Mobile Server and uploads the changes made in the client machine. It then downloads the changes for the client that are already prepared by the Mobile Server.
A background process called the Message Generator and Processor (MGP), which runs in the same tier as the Mobile Server, periodically collects all the uploaded changes from many Mobile users and then applies them to the server database. Next, MGP prepares changes that need to be sent to each Mobile user. This step is essential because the next time the Mobile user synchronizes with the Mobile Server, these changes can be downloaded to the client and applied to the client database.
Figure 1-1 illustrates the architecture of Oracle Database Lite applications.
Figure 1-1 Oracle Database Lite Architecture
Note:Web-to-Go clients have one additional component, a light weight HTTP listener that is not shown in the diagram.
The Oracle Database Lite RDBMS is a small footprint, Java-enabled, secure, relational database management system created specifically for laptop computers, handheld computers, PDAs, and information appliances. The Oracle Database Lite RDBMS runs on Windows 98/NT/2000/XP, Windows CE/Pocket PC, Linux, and Palm. Oracle Database Lite RDBMS provides JDBC, ODBC, and SODA interfaces to build database applications from a variety of programming languages such as Java, C/C++, and Visual Basic. These database applications can be used while the user is disconnected from the Oracle database server.
When you install the Mobile Development Kit, the Oracle Database Lite RDBMS and all the utilities listed in Appendix C are installed on your development machine. In a production system, when the Mobile Server installs Oracle Database Lite applications, only the RDBMS, the Mobile Sync, and Mobile SQL applications are installed on the client machine.
Mobile Sync (msync) is a small footprint application that resides on the Mobile device. Mobile Sync enables you to synchronize data between handheld devices, desktop and laptop computers and Oracle databases. Mobile Sync runs on Windows 98/NT/2000/XP, Windows CE/Pocket PC, Linux, and Palm.
Mobile Sync synchronizes the snapshots in Oracle Database Lite with the data in corresponding Oracle data server. These snapshots are created by the Mobile Server for each user from the publication items associated with a Mobile application. The Mobile Server also coordinates the synchronization process.
The Mobile Sync application communicates with the Mobile Server using any of the supported protocols (e.g., HTTP or HTTPS). When called by the Mobile user, the Mobile Sync application first collects the user information and authenticates the users with the Mobile Server. It then collects the changes made to Oracle Database Lite (from the snapshot change logs) and uploads them to the Mobile Server. It then downloads the changes for the user from the Mobile Server and applies them to the Oracle Database Lite.
In addition to this basic function, the Mobile Sync application can also encrypt, decrypt, and compress transmitted data.
When you install the Mobile Development Kit, the Mobile Sync application is also installed on your development machine. The Mobile Server also installs the Mobile Sync on the client machine as part of application installation.
Unlike base tables and views, snapshots cannot be created in Oracle Database Lite by using SQL statements. They can only be created by the Mobile Server based on subscriptions which are derived from publication items associated with an application. This point is discussed further later in this chapter and in Chapter 4.
Application Installation and Update
The Mobile Server has two major modules called the Resource Manager and the Consolidator Manager. The Resource Manager is responsible for application publishing, application provisioning, and application installation. The Consolidator Manager is responsible for data and application synchronization.
Application publishing refers to uploading your application to the Mobile Server so that it can be provisioned to the Mobile users. Once you have finished developing your application, you can publish it to the Mobile Server by using the development tool called the Packaging Wizard.
Application provisioning is concerned with creating subscriptions for users and assigning application execution privilege to them. Application provisioning can also be done in one of two ways.
Using the administration tool called the Mobile Manager, you can create users and groups, create subscriptions for users by assigning values to subscription parameters, and give users or groups privileges to use the application.
Using the Resource Manager API, you can programmatically perform the above tasks.
End users install Mobile applications in two steps. First, as the Mobile user, you browse the setup page on the Mobile Server and choose the setup program for the platform you want to use. The setup program only runs on Windows 32 platforms. For Windows 32 based client systems, you can download the setup program directly to the Windows 32 system and execute it to set up the Oracle Database Lite client. For Windows CE and Palm devices, you must download the setup program to your Windows 32 desktop first and execute it there. Then you must use ActiveSync for Windows CE or Hot Sync for Palm to install the Oracle Database Lite client on the device.
Second, you run the Mobile Sync (msync) command on your Mobile device, which prompts for the user name and password. The Mobile Sync application communicates with the Consolidator Manager module of the Mobile Server and downloads the applications and the data provisioning for the user.
After the installation of the applications and data, you can start using the application. Periodically, use Mobile Sync or a custom command to synchronize your local database with the server database. This synchronization updates all applications that have changed.
The Consolidator Manager module of the Mobile Server uploads the changes from the client database to the server, and it downloads the relevant server changes to the client. But it does not reconcile the changes. The reconciliation of changes and the resolution of any conflicts arising from the changes are handled by MGP. MGP runs as a background process which can be controlled to start its cycle at certain intervals.
Each cycle of MGP consists of two phases: Apply and Compose.
In the apply phase, MGP collects the changes that were uploaded by the users since the last apply phase and applies them to the server database. For each user that has uploaded his changes, the MGP applies the changes for each subscription in a single transaction. If the transaction fails, MGP will log the reason in the log file and stores the changes in the error file.
When the apply phase is finished, MGP goes into the compose phase, where it starts preparing the changes that need to be downloaded for each client.
Because of the asynchronous nature of data synchronization, the Mobile user may sometimes get an unexpected result. A typical case is when the user updates a record that is also updated by someone else on the server. After a round of synchronization, the user may not get the server changes.
This happens because the user's changes have not been reconciled with the server database changes yet. In the next cycle of MGP, the changes will be reconciled with the server database, and any conflicts arising from the reconciliation will be resolved. Then a new record will be prepared for downloading the changes to the client. When the user synchronizes again (the second time), the user will get the record that reflects the server changes. If there is a conflict between the server changes and the client changes, the user will get the record that reflects either the server changes or the client changes, depending on how the conflict resolution policy is defined.
The Mobile Server repository contains all the information needed to run the Mobile Server. The information is usually stored in the same database where the application data reside. The only exception to this is in cases where the application data resides in a remote instance and there is a synonym defined in the Mobile Server to this remote instance.
Changes to the repository should only be made using the Mobile Server Mobile Manager or the Resource Manager API.
How your application integrates with the Oracle Lite database depends on how you design the execution model, as described in the following sections:
Do you want to use the Oracle Lite database to store data solely for a single application? Yes; use the embedded application option. See Section 1.4.1, "Embedded Application in Single Process" for more information.
Do you want to have multiple applications access the same database? See Section 1.4.2, "Multiple Processes Accessing the Same Database" for more information.
Do you want your application to access the database remotely? See Section 1.4.3, "Multiple Embedded Application Clients Accessing Remote Database" for more information.
Do you want to use the Oracle Lite database to store changes that will be synchronized with a back-end Mobile Server repository? Yes; use the Mobile client option. See Section 1.4.4, "Mobile Client in Single Process" for more details.
Do you want your application to access the database remotely? See Section 1.4.5, "Multiple Clients Accessing Remote Database" for more information.
As demonstrated in Figure 1-2, if you chose to build a standalone application with the Oracle Lite database embedded in the application, then when the application is launched, the Oracle Lite database libraries are loaded into the same process as the application.
Figure 1-2 Embedded Application With ODB Libraries in Single Process
See Chapter 2, "Creating and Managing the Database in an Embedded Application" for more information on how to embed an Oracle Lite database into a standalone application.
You can configure your application processes to share the same database. Thus, when each application is launched, each application exists in its own process and can access the same database independently. In this scenario, Oracle Database Lite libraries use shared memory to coordinate locking between both processes.
As shown in Figure 1-3, this type of scenario is automatically created when you include the same publication within two different applications during the packaging and deploying process.
Figure 1-3 Applications in Multiple Processes Accessing Single Database
If you are embedding a database into your application software, but you want the applications to exist remotely from the data, then use the client/server embedded approach with the multi-user service, as described in Section 2.5, "Oracle Database Lite Multi-User Service".
If you chose to install the Mobile client and synchronized your user on a single device, then when you launch your application, the Oracle Lite database libraries are loaded into the same process as your application. This is the default scenario and is demonstrated in Figure 1-4.
Figure 1-4 Diagram of Mobile Client and ODB Libraries in SIngle Process
If you have several remote clients accessing the same data, you can use Branch Office to facilitate the remote applications. Figure 1-5 demonstrates how multiple remote Branch Office clients access the data from a single machine. This machine contains the Branch Office executables and the local Oracle Lite database, which all clients access for their information. When a synchronization is requested, information is communicated between the Branch Office and the back-end database through the Mobile Server.
Figure 1-5 Using Branch Office for Managing Multiple Clients that Access a Remote Database
See the Branch Office chapter in the Oracle Database Lite Administration and Deployment Guide for more information.
Before you develop an offline application using Oracle Database Lite, you should install the Oracle Database Lite Mobile Development Kit (MDK) on the machine on which you intend to develop your application. for instructions on how to install the Mobile Development Kit, see the Oracle Database Lite Getting Started Guide.
The Oracle Database Lite Mobile Development Kit includes the following components.
Oracle Database Lite RDBMS—A lightweight, object-relational database management system
Mobile Development Workbench (MDW)—
Packaging Wizard—A tool to publish applications to the Mobile Server
Mobile Sync—A transactional synchronization engine
mSQL—An interactive tool to create, access, and manipulate Oracle Database Lite on laptops and handheld devices
Using any C, C++, or Java development tool in conjunction with the Mobile Development Kit for Windows, you can develop your Mobile applications for Windows against Oracle Database Lite, and then publish the applications to the Mobile Server by using the Packaging Wizard. See Oracle Database Lite Getting Started Guide for instructions on how to install the Mobile Server.
Once you have published the applications to the Mobile Server, you can use the Mobile Manager to provision the applications to the Mobile users. Provisioning involves specifying the values of the subscription parameters used for subsetting the data needed by the application for a particular user. A user to whom an application has been provisioned can then log in to the Mobile Server and request it to set up everything the user needs to run the applications on the user's device.
The Mobile Development Kit is installed in <
\Mobile\Sdk. The bin directory contains, among other things:
The Oracle Database Lite RDBMS and its components, including Mobile SQL (
msql.exe), are described in the Oracle Database Lite Developer's Guide. Mobile SQL is written in Java. It requires the Java runtime environment JRE 1.4.2 or higher to be installed on your system before you can use it. If you have installed JDK 1.4.2 or higher, the JRE is already installed in your machine.
Mobile Sync (msync), the executable (
msync.exe), the COM interface, and the Java wrapper for it.
Packaging Wizard (
Mobile Database Workbench (MDW)
The Samples directory <
\Mobile\Sdk\Samples directory contains some sample applications. Chapter 2, "The Oracle Database Lite RDBMS", Chapter 2, "Using Oracle Database Lite Samples" describes the sample programs and explains how to run them. You should familiarize yourself with the various Oracle Database Lite features by perusing the source code and running the samples.
\Mobile\Sdk\OLDB40 directory contains a starter database file named
When you install the Mobile Development Kit, the installer sets the
PATH environment variable to include the bin directory of the Mobile Development Kit. You can use the Command Prompt on your Windows 32 machine to do the following quick test.
At the Command Prompt, enter the following.
msql system/manager@jdbc:polite:polite ... SQL>create table test (c1 int, c2 int); Table created SQL>insert into test values(1,2) 1 row(s) created SQL>select * from test; C1 | C2 ----+---- 1 | 2 SQL>rollback; Rollback completed SQL>exit
Mobile SQL is an interactive tool that allows you to create, access, and manipulate Oracle Database Lite on laptops and handheld devices. The mSQL installations on laptops cannot be used to create a database, but can create a database on hand-held devices. Using mSQL, you can perform the following actions.
Create database objects such as tables and views
Execute SQL statements
The mSQL tool is installed with the Mobile Development Kit installation. It is also installed by the Mobile Server as part of application installation. The mSQL tool for the Windows 32 platform is a command line tool that is similar to the Oracle SQL*Plus tool, but does not provide compatibility with SQL*Plus. The mSQL tool for Windows CE and Palm supports a graphical user interface.
The Mobile Database Workbench (MDW) is a new tool that enables you to iteratively create and test publications—testing each object as you add it to a publication. Publications are stored within a project, which can be saved and restored from your file system, so that you can continue to add and modify any of the contained objects within it.
All work is created within a project, which can be saved to the file system and retrieved for further modifications later. Once you create the project, start creating the publication items, sequences, scripts and resources that are to be associated with the publication. You can create the publication and associated objects in any order, but you always associate an existing object with the publication. Thus, it saves time to start with creating the objects first and associating it with the publication afterwards.
For detailed information on how to use MDW, see Chapter 5, "Using Mobile Database Workbench to Create Publications".
The Packaging Wizard is a graphical tool that enables you to perform the following tasks.
Create a new Mobile application.
Edit an existing Mobile application.
Publish an application to the Mobile Server.
When you create a new Mobile application, you must define its components and files. In some cases, you may want to edit the definition of an existing Mobile application's components. For example, if you develop a new version of your application, you can use the Packaging Wizard to update your application definition. The Packaging Wizard also enables you to package application components in a .jar file which can be published using the Mobile Manager. The Packaging Wizard also enables you to create SQL scripts which can be used to execute any SQL statements in the Oracle database.
For detailed information on how to use the Packaging Wizard, see Chapter 6, "Using the Packaging Wizard".
Your development environment must include Oracle Database Lite 10g as the encompassing platform. For developing native applications on the Oracle Database Lite 10g platform, the following operating system platforms are supported:
Microsoft Windows NT/2000/XP
Windows Mobile 2003 Second Edition software for Pocket PCs (Windows CE 4.2)
The following Windows CE chipsets are supported:
Pocket PC 2003 (ARM, xScale, Emulator)
Pocket PC (ARM)
Palm OS 3.5 through 5.2