Skip Headers

Oracle® Database Lite Developer's Guide Release 10.3 Part Number E12090-02 |
Home | Book List | Contents | Index | Contact Us

Previous | Next
| View PDF

# 1 Overview for Designing Mobile Applications

The following sections provide an introduction to Oracle Database Lite 10g and an overview of the application development process:

- Section 1.1, "Introduction"

- Section 1.2, "Oracle Database Lite 10g Application Model and Architecture"

- Section 1.3, "Creating the Publish-Subscribe Model for Mobile Users"

- Section 1.4, "Mobile Development Kit (MDK)"

- Section 1.5, "Mobile Application Design"

- Section 1.6, "Supported Languages for Application Development"

## 1.1 Introduction

Oracle Database Lite 10g facilitates the development, deployment, and management of Mobile database applications for a large number of mobile users. A Mobile application is an application that can run on mobile devices without requiring constant connectivity to the server. The application requires a small, local database on the mobile device, whose content is a subset of data that is stored in the enterprise data server. This database can either be a SQLite database or the Oracle Lite database. Each database type stores data in files with an ODB extension. Modifications made to the local database by the application are reconciled with the back-end server data through data synchronization.

The Mobile client component in Oracle Database Lite is a preconfigured component to facilitate running a Mobile application. It contains synchronization and software components to manage the device.

Mobile database applications can be developed in many ways, as follows:

- Web-to-Go applications can be built using Web technologies, such as servlet, Java Sever Pages (JSP), applet, HTML, and JDBC.

- 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 a Mobile database application is to use Java and the Java Database Connectivity (JDBC) interface. Oracle Database Lite 10g also offers a third way to develop Mobile database applications using the servlet based Web model called Web-to-Go.

- 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.

- Symbian applications that need a standard interface and work with multiple database engines can use either the Open Database Connectivity (ODBC) interface or some other interface built on top of ODBC.

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 10g applications is the Mobile Server which is where the Mobile applications are deployed. Deployment consists of five major steps:

1. Designing the server system to achieve the required level of performance, scalability, security, availability, and connectivity. Oracle Database Lite 10g 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.

2. Publishing the application to the server. This refers to installing all the components for an application on the Mobile Server. Oracle Database Lite 10g provides a tool called the Packaging Wizard that can be used to publish applications to the Mobile Server.

3. Provisioning the applications to the Mobile users. This phase includes determining user accesses to applications with a specified subset of data. Oracle Database Lite 10g 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.

4. 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, and form factors), many connectivity options (such as, LAN, Wireless LAN, cellular, and wireless data), 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.

5. Determining the method of initial installation of applications on Mobile devices (application delivery). Initial installation involves installing the Oracle Database Lite 10g 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 10g 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 10g 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 to be running. You can use an existing test database as well. The Mobile Server stores its metadata in this database.

## 1.2 Oracle Database Lite 10g Application Model and Architecture

In the Oracle Database Lite 10g 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 Database 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 the Mobile client database on the client machine for each subscription that is provisioned to the user. The Mobile client database could be a SQLite database or an Oracle Lite database, as set in the publication. 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 the Mobile client database 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 10g 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 10g applications.

Figure 1-1 Oracle Database Lite 10g Architecture

Description of "Figure 1-1 Oracle Database Lite 10g Architecture"

Note:

Web-to-Go clients have one additional component, a light weight HTTP listener that is not shown in the diagram.

The following sections describe the separate components of Oracle Database Lite:

- Section 1.2.1, "Mobile Client Database"

- Section 1.2.2, "Mobile Sync"

- Section 1.2.3, "Mobile Server"

- Section 1.2.4, "Message Generator and Processor (MGP)"

- Section 1.2.5, "Mobile Server Repository"

- Section 1.2.6, "Device Manager"

### 1.2.1 Mobile Client Database

The Mobile client uses a client database, which can be either a SQLite database or Oracle Lite database, to store the relational data in one or more data files on the file system on the client. While the SQLite database is already installed on many client devices, you can install the Oracle Lite database on most any device from the Mobile Manager.

Both Mobile client databases are described in the following sections:

- Section 1.2.1.1, "SQLite Database"

- Section 1.2.1.2, "Oracle Lite Database"

#### 1.2.1.1 SQLite Database

If you are using the SQLite database as the Mobile client database, if may already be installed on your device or you must install this independently. All details about the SQLite database are documented on the SQLite database Web site at `http://www.sqlite.org/`. After installing the SQLite database, install the SQLite Mobile client, which includes the Sync Engine for managing synchronization between the SQLite database and the back-end Oracle database.

#### 1.2.1.2 Oracle Lite Database

The Oracle Database Lite database 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 2003/XP, Windows CE/Windows Mobile, Linux, and Symbian. 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 Lite RDBMS and the utilities are installed on your development machine. In a production system, when the Mobile Server installs Oracle Database Lite 10g applications, only the RDBMS, the Mobile Sync, and Mobile SQL applications are installed on the client machine.

Oracle Database Lite has its own JDBC driver on the client. With this driver, you can connect to the client Oracle Lite database.

### 1.2.2 Mobile Sync

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 authenticates locally, collects changes from the Oracle Lite database and sends them to the server, where the user is authenticated before the changes are uploaded. Mobile Sync runs on Windows 2003/XP, Windows CE/Windows Mobile, and Linux.

Use the `msync` executable for Mobile Sync.

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 (that is, 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.

### 1.2.3 Mobile Server

The installation of the Mobile Server requires an Oracle database to be running. You can use an existing test database as well. The Mobile Server stores its metadata in this database.

The Mobile Server is a Web middle-tier server that can exist on Windows, Solaris, HP-UX, AIX, and Linux. The Mobile Server uses the Oracle Containers for Java (OC4J) Web engine and provides the interface between the mobile infrastructure and the enterprise database. Most administration tasks are accomplished through the Mobile Server Web application—the Mobile Manager.

The Mobile Server provides the following features.

- application publishing

- application provisioning

- application installation and update

- data synchronization

The Mobile Manager application provides the capability to manage users, devices, publications and applications. This utility can provides the following:

- Monitors and manages synchronization between the client data store and the enterprise data store.

- Sends administrative commands to the Mobile clients. These commands capture data and logs from the client or instructs the client to carry out necessary tasks. For example, the Mobile Manager could send a command to a client to perform synchronization or to remove the entire client data store, if a device may have been compromised.

Note:

If you wish, you can also accomplish the same tasks as the Mobile Manager with the Application Programming Interfaces (APIs).

As with any Web server tier, the Mobile Server may be configured within a Web farm for improved performance within the mobile infrastructure. This enables the use of a load balancer, such as the balancer included with Oracle Internet Application Server (OracleAS), or with one provided by a 3rd party vendor. The Mobile Server is designed to be fully integrated with OracleAS to take advantage of the features within OracleAS, such as the Oracle Internet Directory (OID) capabilities.

Note:

As the Mobile Server is a Web-based environment, it is important to design for a proper security environment as would be done with any Web server.

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.

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.

1. As the Mobile user, browse the setup page on the Mobile Server and choose the setup program for the platform you want to use.

2. Run the Mobile Sync (mSync) command on your Mobile device, which prompts for the Mobile username 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 msync or a custom command to synchronize your local database with the server database. This synchronization updates all applications that have changed.

### 1.2.4 Message Generator and Processor (MGP)

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.

Note:

The mobile infrastructure may allow for multiple Mobile Servers to be configured within a Web farm. However, there may only be one MGP application utilized for the entire Web farm.

Each cycle of MGP consists of two phases: Apply and Compose.

The Apply Phase

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.

The Compose Phase

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.

Applying Changes to the Server Database

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.

### 1.2.5 Mobile Server Repository

The Mobile Server Repository contains all the application data as well as all information needed to run the Mobile Server. The Mobile Repository contains the repository schema under which all the data mapping and internal tables utilized to maintain data synchronization exist. This schema also stores the application, application tables and its data published for use with a Mobile client.

The information is normally stored in the same database where the application data resides. 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.

The Mobile Repository contains some internal tables that the Mobile Server uses to perform its functions. You may query these tables to gain more details about the current state of the environment; however, most of the information needed from these tables is already accessible from the Mobile Manager. You should never alter any of the internal tables and their contents unless explicitly directed to by Oracle Support Services or Oracle Development.

Administration, backup, and recovery of the repository are no different then for any other Oracle database requiring standard Database Administrator (DBA) skills

Changes to the repository should only be made using the Mobile Server Mobile Manager or the Resource Manager API.

### 1.2.6 Device Manager

The Device Manager manages client devices. On install of the Mobile client, the Device Manager registers a device with the Mobile Server. The Device Manager invokes the update executable after synchronization completes to determine if any mobile application updates are available, then downloads and installs these application updates to a Mobile client. You can request—through the Mobile Manager—that certain commands are invoked on the client. The Device Manager executes these commands. The Device Manager is responsible for most administrative actions between the Mobile Server and the Mobile client.

## 1.3 Creating the Publish-Subscribe Model for Mobile Users

ITo enable users to access their data, you need to first define the data in the snapshot. Then, subscribe the appropriate users to access only their data. On the client device, data is stored in a special type of relational table, called a snapshot table. A snapshot table behaves identical to a regular relational table, but has functionality that enables tracking changes made to the table.

A SQL query, called the publication item, can determine the record set that is downloaded to the snapshot table. The publication item is executed against the server database. The result set of the query defines the structure (columns) of the snapshot table on the client device as well as its contents.

A collection of publication items is called a publication, which corresponds to a single Oracle Lite database on a client device. All snapshot tables based that are based on publication items part of a single publication are stored in the same Oracle Lite database.

Oracle Database Lite operates within a publish-subscribe model. We use the example of the magazine as an effective way to explain the publish-subscribe model. A magazine is created with specific data that would be of interest to readers, such as sports, hunting, automobiles, and so on. Readers request a subscription for the specific magazine they feel would be in their interest to read. Once this subscription is created only the magazines to which the reader has been subscribed are sent to the reader.

For Oracle Database Lite, the publication is the magazine, the publication items are the specific articles of data and the subscription is the granting of access to the publication for specific users. In the Oracle Database Lite 10g application model, each application defines its data requirements using a publication. Data subsets, known as publications items, are created and added to a publication. Application files are also uploaded to the same publication. Once these publications are deployed to the Mobile Server, any user may be granted a subscription to the publication.

Technically, a publication is like 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.

As shown in Figure 1-2, a subscription defines the relationship between a user and a publication. 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 Database Lite you receive snapshots, and, depending on your subscription parameter values, those snapshots are partitioned with data tailored for you.

Subscription parameter values can be set by the administrator in order to tailor the snapshot data for each user.

Figure 1-2 Subscription Defines Relationship Between User and Publication

Description of "Figure 1-2 Subscription Defines Relationship Between User and Publication"

The subscription is the definition of how to retrieve data from the back-end database; the snapshot is the actual data that conforms to the definition within the subscription and which belongs to the user.

This process really forms a simple development cycle for mobile applications, as follows:

1. Create the publication and its publication items that contains the data subset for a particular application.

2. Grant users a subscription to a publication. This forms the specific dataset that is used on a Mobile client.

3. Develop and test the Mobile application to work with the specific data set.

4. Deploy the application to the Mobile Server and install it on the client.

Two of the more common questions and sources of confusion that comes up are what has to be done first:

1. Do you create the publication first or the publication items?

   It does not matter. You can create either the publication or the publication item first. Consider an article for a magazine. That article may have been written by a freelance author. The article exists before it belongs to any publication. The author submits this to two or three magazine publishers since it is relevant to the content they advertise. Two decide it is appropriate for the publication they are distributing currently while one does not include it since the content is not quite what their readers want.

2. Do you have to create a separate publication item for each publication?

   No, you can have one or more publication items in a publication.

Note:

You can create publications with the Mobile Database Workbench or the Java APIs.

The following sections describes other pertinent information for publication items:

- Section 1.3.1, "Defining the Weight and Conflict Resolution for Publication Items"

- Section 1.3.2, "Behavior and Requirements for Primary Keys, Foreign Keys and Not Null Fields in Publication Items"

### 1.3.1 Defining the Weight and Conflict Resolution for Publication Items

The following important aspects of the publication item should be taken into account when you are designing your application:

- Weight—The publication item weight is used to control the order in processing publication items, which avoids conflicts. Changes made on the client are processed according to weight in order to prevent conflicts, such as foreign key violations. The weight determines what tables are applied to the enterprise database first. For example, the `scott.emp` table has a foreign key constraint to the `scott.dept` table. If a new department number is added to the `dept` table and a new record utilizing the new department number were added to the `emp` table, then the transaction would be placed in the error queue if the new record utilizing the new department in the `emp` table was applied to the repository before the new department in the `dept` table was applied. To prevent the violation of the foreign key constraint on the enterprise server, you set the `dept` snapshot to a weight of 1 and the `emp` snapshot to a weight of 2, which applies all updates to the `dept` table prior to any updates to the `emp` table as the lower weight is always processed first.

- Conflict Resolution—In the same scenario, what if someone already updated the enterprise server with the new department number? This causes a conflict when the client attempts to synchronize with the new department that utilizes the same number. To handle this, conflict resolution may be set to either "client wins" or "server wins". If set to "server wins", then the setting on the server takes precedence to the setting on the client. The client transaction is sent to the error queue. However, if "client wins" is set, then the new department number from the client overrides the setting on the server.

### 1.3.2 Behavior and Requirements for Primary Keys, Foreign Keys and Not Null Fields in Publication Items

Only primary keys and not null fields are replicated down to the client. Publication items require a primary key field or, as in the case of a view, primary key hints.

If a foreign key needs to be applied to the client, then the script for the foreign key needs to be added to the publication, so that it will be executed when the client synchronizes the first time. You can set the script for the foreign key within either the MDW scripts section or the API.

Constraints are not the only type of script that may be executed on the client. The script could execute any valid SQL DDL statement on the client.

## 1.4 Mobile Development Kit (MDK)

Before you develop an application using Oracle Database Lite 10g, you should install the Oracle Database Lite 10g 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 Section 4.3, "Installing Oracle Databse Lite" in the Oracle Database Lite Getting Started Guide.

The Oracle Database Lite 10g Mobile Development Kit includes the following components.

- Oracle Database Lite RDBMS—A lightweight, object-relational database management system, including Mobile SQL (`msql.exe`). Mobile SQL is written in Java. It requires the Java runtime environment JRE to be installed on your system before you can use it.

- Mobile Database Workbench (MDW)—A development tool for creating a publication.

- Packaging Wizard—A tool to publish applications to the Mobile Server.

- Mobile Sync—A transactional synchronization engine that includes the executable (`msync.exe`) and the Java wrapper for it.

- 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 Section 4.3, "Installing Oracle Database Lite" in the 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.

When you install the MDK, it performs the following:

- Installs a starter database file in the <`ORACLE_HOME`>`\Mobile\Sdk\OLDB40` directory named `polite.odb`.

  Note:

  The `polite.odb` starter database is not the name of the Mobile client database. For information on what Oracle Lite database (ODB) files are installed on the client, see "Section 6.3, "Synchronize or Execute Applications on the Mobile Client" in the Oracle Database Lite Client Guide.

- 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

### 1.4.1 Mobile SQL (mSQL)

Mobile SQL is an interactive tool that allows you to create, access, and manipulate the Oracle Lite database on laptops and handheld devices. The mSQL installations on laptops cannot be used to create an Oracle Lite database, but can create an Oracle Lite database on hand-held devices. Using mSQL, you can perform the following actions.

- Create Oracle Lite database objects such as tables and views

- View tables

- 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. If the Oracle Lite database was created in the Mobile Server environment, then the default username is `SYSTEM` and the password is the same as the Mobile user password.

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 supports a graphical user interface. See Appendix C.1, "The mSQL Tool in the Oracle Database Lite Client Guide for details.

Note:

UTF8 SQL Scripts are not supported in mSQL.

### 1.4.2 Using the Mobile Database Workbench

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 6, "Using Mobile Database Workbench to Create Publications".

### 1.4.3 Using the Packaging Wizard

The Packaging Wizard is a graphical tool that enables you to perform the following tasks.

1. Create a new Mobile application.

2. Edit an existing Mobile application.

3. 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 7, "Using the Packaging Wizard".

## 1.5 Mobile Application Design

Before you start to design your Mobile application, it is important to read the following sections to understand the differences between an enterprise application and the mobile application as well as the choices you have in designing your application:

- Section 1.5.1, "Steps for Designing Your Mobile Application"

- Section 1.5.2, "Application Programming Interfaces"

- Section 1.5.3, "Application Deployment into the Mobile Environment"

### 1.5.1 Steps for Designing Your Mobile Application

With a proper design, you can avoid the most common causes for mobile project failure, not meeting the needs of the business, poor performance, or issues occurring within a production environment. Proper design of the Mobile System includes the infrastructure and the mobile application. Without proper design, a mobile architecture could end up costing more then it saves.

The following assumption is one of the most common misconceptions for taking an enterprise application and incorporating it into a mobile component:

The mobile application is a scaled down version of the enterprise application.

By taking an existing enterprise application, you may intend to provide the same functionality in remote or disconnected locations. Since the enterprise application has already undergone thorough requirements gathering, design, development, testing, and successful implementation, you may assume that it will automatically work seamlessly as a mobile application and so do not test it in this environment. This assumption may lead to project failure.

For example, take an enterprise form-based client/server application. You have a client connecting through a middle-tier connecting to a database on the back-end. Taking this to a mobile infrastructure, such as with Oracle Database Lite, adds a completely new tier that did not exist within the original infrastructure. The Mobile Server tier introduces new concerns, such as the following:

- Security—There is now a system in the infrastructure that potentially gives any outsider access to the entire organization if proper security configuration and implementation is not performed.

- Bandwidth—The Mobile Server may become a bottleneck for all remote locations without the implementation of a Web farm.

- Scalability—The applications are performing synchronization of hundreds to millions of records, which is not the same as providing static Web pages to a large number of users. A system that is fine for serving static Web pages may not be capable of servicing hundreds of users performing synchronization.

A complete redesign of the system specifications may be in order.

You may also need to re-evaluate the original design of the enterprise application. The following lists a few design considerations for the mobile application:

- Memory—An application designed, tested, and implemented on a multiprocessor system with several gigabytes of memory will not perform the same on a standard PC with only a single processor and maybe 512 megabytes of memory.

- Resource Limitations—Several years ago, limitations of available resources made the usage of data types an extreme concern. The storage space saved by using a small integer over an integer was crucial due to limited memory available. With advances in memory and system resources, this has not been a concern to most modern developers. Now, the mobile infrastructure brings resource limitations back to the list of chief concerns for the design and development of mobile applications. One of the most significant of these limitations is the bandwidth available for the mobile client. If a Mobile client is only able to synchronize over a cell phone network, you may not wish to bring a million records down to a client that only needs a few thousand records. This decision impacts the synchronization performance, as well as the costs associated with the synchronization. If the mobile client was only utilized to collect data, then you can create a data collection queue for synchronization and avoid the whole download phase of synchronization.

- Use of Indexes—You use indexes for avoiding full-table scans. So, if you use the same data subset originally designed for a Windows machine down on a client device and do not use an index, then the performance may be adversely effected. Oracle Database Lite uses two types of scans for queries: full table scans and index based scans.

Thus, we recommend the following steps:

- Section 1.5.1.1, "Read the Documentation Before Design"

- Section 1.5.1.2, "Gather Mobile Requirements"

- Section 1.5.1.3, "Proof of Concept"

- Section 1.5.1.4, "Prototype"

- Section 1.5.1.5, "Design for Data Subsets"

- Section 1.5.1.6, "Design for Indexing"

- Section 1.5.1.7, "Design for Sequences"

- Section 1.5.1.8, "Design for Synchronization"

- Section 1.5.1.9, "Design for Administration"

- Section 1.5.1.10, "Design for the Language Utilized for Handheld Devices"

#### 1.5.1.1 Read the Documentation Before Design

To ensure that you develop your applications correctly, we recommend that you start with a light scan of all of the documentation followed by detailed reading of pertinent areas related to the specific project that are to be undertaken.

#### 1.5.1.2 Gather Mobile Requirements

Gathering requirements is the key to successful project development; a mobile project is no exception.

Mobile users have a specific task they perform. Gather the specific requirements of each mobile user type that will use the mobile application. For example, an insurance application may have two types of mobile users:

- The insurance investigator may only drive to locations to photograph an incident for an insurance claim before any required evidence is altered or vanishes. This user needs a mobile application that captures and uploads the images of the incident.

- A claims agent may later be sent to the site to process a claim for the client. This user needs more details for the client, such as their account information and what they are covered for in order to properly process a claim.

Each user requires a different subset of the enterprise data and functionality within the mobile application.

#### 1.5.1.3 Proof of Concept

Proof of concept testing determines if the Oracle Database Lite mobile architecture meets the needs of the business before architectural design starts.

When an aspect of the mobile solution is determined to meet a requirement, a quick proof of concept test ensures that the requirement may be satisfied by the potential solution. There is a difference in what can be done in a mobile environment versus what can be accomplished in the enterprise environment.

#### 1.5.1.4 Prototype

Prototyping may be seen as too costly to utilize for the proper design of a mobile architecture and application. However, consider that a prototype may be anything from sketching out a design on paper to writing a full application prototype.

You may use one of the sample applications included with the Oracle Database Lite product, such as the transport demo, to gain a full understanding of the infrastructure and the design considerations it involves. Take the application through the setup of the environment, the creation of users, the deployment of the mobile application to multiple clients, the synchronization of multiple clients, and the testing of updates made to the application. This provides a better understanding of the infrastructure as actual hands on experience.

#### 1.5.1.5 Design for Data Subsets

Data subsetting reduces data to be downloaded, which has a direct impact on performance.

What is Data Subsetting? Data subsets are a crucial part of the design for a mobile application. Data sub-setting is accomplished through variables that limit the data to a specific requirement, such as by region or department. An easy way to conceptualize data subsets is to consider a subset that is limited through a `WHERE` clause that uses bind variables—also known as snapshot template variables—to limit the snapshot data.

When you set up your publication item, you may have set up an input parameter that defines what snapshot of data is to be retrieved for this user. For example, if you have an application that retrieves the customer base for each sales manager, the application needs to know the sales manager's identification number to retrieve the data specific to each manager. Thus, if you set up each sales manager as a unique user and set their identification number in the data subsetting screen, then the application is provided that unique information for retrieving data.

#### 1.5.1.6 Design for Indexing

Oracle Database Lite maintains two basic methods for accessing data within a table: full table scan and index-based access. The performance of an application can be affected if the design does not include the appropriate indexing. Use indexes to avoid full table scans.

You can execute the Explain Plan and SQL Trace capabilities within the product to determine the access method used by a statement and to determine if indexes are being utilized. Analyze your statements throughout the design and development cycles to ensure that they are being designed and developed for maximum efficiency.

#### 1.5.1.7 Design for Sequences

Sequences guarantee uniqueness of a value, such as a primary key. Design how the sequences are generated within the mobile infrastructure. For example, if the enterprise database generates a sequence number and the Mobile client generates the same sequence number a conflict with the data occurs and causes an error.

Native sequences may be formed specifically for the Mobile clients. These sequences would never populate on the enterprise database itself, so there is no risk of a conflict occurring. This works well when data updates only occur from the Mobile clients and input to the database does not come from any other source. However, it is often necessary to have the sequences generated by both the database and the clients. To accomplish this, sequences must be designed so the database uses a range separate from the range used by the clients. For example, you could define the sequences where the database uses all odd numbers and the clients uses all even numbers.

You must also design sequences for the Mobile clients, so that each client uses a unique range of values without any two clients using the same range. For this you specify the sequence range for each client, such as sequences 1 through 1000 for client A and sequences 1001 through 2000 for client B. Using these ranges for the sequence numbers prevents each client from using the same sequence number as used by another client.

For full details on sequences, see Section 6.6, "Create a Sequence".

#### 1.5.1.8 Design for Synchronization

If you are using the Mobile option, synchronization holds the mobile infrastructure together.

Analyze all of the data needed by the mobile user, as follows:

- Most snapshots are created where the data can be modified on either the client or the server, where the modifications are propagated to the other side through synchronization.

- If any snapshots require only the ability to read the data—that is, all modifications to the data are made on the server-level and not by the user—then create read-only snapshots.

- If all or a majority of the users use the same read-only snapshots, then create a cached user that shares the read-only data across multiple clients.

Analyze the type of synchronization that is appropriate for the user's needs, as described below:

- For optimal performance, use fast refresh for all publications, if appropriate.

- Only design publication items for a complete refresh if the following is true:

  - If it is absolutely critical for all changes to be processed and applied to the data store immediately.

  - If it is critical that any enterprise updates are immediately brought down to the client.

  Note:

  The complete refresh is the most resource intensive method and should only be utilized after full consideration of the performance hit is analyzed. Only time critical publication items should be specified for a complete refresh synchronization type.

- If a mobile user is only performing data collection and it is not necessary for server updates to be brought down to the user, then implement a push-only synchronization model for those publication items. For more information, see Section 2.13.2, "Creating Data Collection Queues for Uploading Client Collected Data".

- When a mobile user only requires specific table to be updated or synchronized, perform a selective synchronization methodology limiting the synchronization process to specific tables or specific publications.

#### 1.5.1.9 Design for Administration

When designing for administration, you may create standardized groups and assign users to these groups. It is much easier to administer groups of users then it is to administer several thousand users each as individuals. The situations where groups simplify your administration is when your organizational architecture has data and the environment is structured into a hierarchy, such as by region or department. In this situation, all properties, access settings, and template variables may be set for groups of users and not the users individually.

#### 1.5.1.10 Design for the Language Utilized for Handheld Devices

Any language selected for handheld development has limitations not existing in their fuller counterparts. The mobile application must be designed to meet these limitations. For example, when developing with WinCE utilizing the Microsoft Compact Framework with C# or VB.Net, forms are not maintained the same way as they are in the full framework when running on a normal PC. Without designing for this limitation, it would be easy to accidentally leave forms sitting in the background that should have been closed when the application exits. This prevents crucial memory from being freed and also prevents any application updates from downloading and installing correctly.

When you properly research and design for the device platform, everything done on the device may be performed on the larger non-mobile applications. Thus, you can reuse and update code simply as it uses the same coding for both environments. Designing in the reverse may result in functionality being utilized in the non-mobile applications that does not have a matching functionality in the mobile version.

### 1.5.2 Application Programming Interfaces

When you are developing your application, you may decide that you want to control more aspects of Oracle Database Lite within your application—rather than relying on user interaction. In this case, you can use the Oracle Database Lite Application Programming Interfaces (APIs). Almost any task performed by the tools and utilities included with Oracle Database Lite may also be accomplished with the APIs. Some of the more advanced functionality within the product is only available through the use of the APIs. Except for the synchronization APIs which are provided for most languages utilized for application development, most of the APIs are Java interfaces that must be developed with the Java programming language.

The most common APIs utilized and their uses are as follows:

- Synchronization APIs: These APIs provide all of the basic synchronization functionality that is found within the mSync utility. The advantages of using these APIs are that the synchronization process can be fully integrated within the actual Mobile application. The APIs also provide the ability for a push-only synchronization, which allows Mobile clients to only upload data skipping the downloading of new data or applications. The push-only model is useful when bandwidth is limited and when the client just collects data—that is, it is not necessary for a remote client to have updated data from the enterprise.

- Consolidator APIs: The Consolidator APIs provide administrative functionality for creating users, setting the user properties, working with applications, and so on. You can automate common administration tasks and speed up some of the administration tasks required, such as the creation of a large amount of users. The only limitation is that application and user settings are not displayed in the Mobile Manager Web administration tool as these APIs directly access the Mobile Repository.

- Mobile Resource Manager APIs: The Mobile Resource Manager APIs also provides administration functionality for users and applications; however, this API actually updates the Mobile Manager administration tool as well as the repository. This utility may be used to create users, set user access, set the user template variables, and many other tasks.

- Device Manager APIs: The Device Manager APIs provide the ability customize the management of devices. These APIs may be used to gather information on devices, send commands to devices, register devices, and so on.

### 1.5.3 Application Deployment into the Mobile Environment

Deployment of applications includes setting up the server system so that end users can easily install and use the applications. Mobile applications are deployed to the Mobile Server.

Deployment consists of the following steps:

1. Create the publication with the Mobile Database Workbench (MDW). See Section 1.4.2, "Using the Mobile Database Workbench" for more information.

2. Publishing the application to the server includes installing all the components for an application on the Mobile Server with the Packaging Wizard tool. See Section 1.4.3, "Using the Packaging Wizard" for details.

3. Provisioning the applications to the Mobile users through the Mobile Manager, which is a GUI interface for the Mobile Server. This phase includes determining user accesses to applications with a specified subset of data. The Mobile Manager can 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.

4. Testing for functionality and performance in a real deployment environment. A Mobile application system is a complex system involving the following:

   - Multiple Mobile device client technologies—such as, operating systems, form factors, and so on.

   - Multiple connectivity options—such as, LAN, Wireless LAN, cellular, wireless data, and other technologies.

   - Multiple server configuration options.

   When testing, pay particular attention to tuning the performance of the data subsetting queries, as it is the most frequent cause of performance problems.

5. Determining the method of initial installation of applications on Mobile devices (application delivery). Initial installation involves installing the Oracle Database Lite 10g client, the application code, and the initial Oracle Lite 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 given 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 10g provides a tool for offline instantiation.

After deployment, both the application and the data schema may change because of enhancements or defect resolution. The Mobile Server manages 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.

## 1.6 Supported Languages for Application Development

Oracle Database Lite is an integrated framework that simplifies the development, management, and deployment of mobile applications. For a list of the supported on mobile platforms, operating systems, and hardware, see Chapter 3, "Requirements before Installation or Development" in the Oracle Database Lite Getting Started Guide.

Platform | Programming Languages
--- | ---
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. You can use Visual Studio.Net 2003 or 2005 for development. If you use Visual Studio.Net 2005, you must install the ODBC 3.5 driver. See Section 5.1.3, "ODBC" for details.
PocketPC | C, C++, Visual Basic, Java applications (no Servlet or JSP support), SODA, ADO.NET. You can use Visual Studio.Net 2005 for development. If you use Visual Studio.Net 2005, you must install the ODBC 3.5 driver. See Section 5.1.3, "ODBC" for details.
Linux | Java, C, C++
Symbian OS on Nokia and Motorola | C, C++

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:

- Section 1.6.1, "Native Applications"

- Section 1.6.2, "Standalone Java Applications"

- Section 1.6.3, "Web Applications"

### 1.6.1 Native Applications

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 the Windows CE 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 5.1.2, "JDBC" and Section 5.1.3, "ODBC" for more information on accessing the database with either of these interfaces.

See Section 5.1, "Data Access APIs" for more information on C and C++.

### 1.6.2 Standalone Java Applications

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:

1. 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.

2. Load the JDBC driver in to your applications. The following is an example:

       Class.forName("oracle.lite.poljdbc.POLJDBCDriver");

3. 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:

       jdbc:polite:<dsn>

   Or if not local, connect as follows:

       jdbc:polite@[hostname]:[port]:dsn

See Chapter 5, "Application Development" and Chapter 7, "JDBC Programming" in the Oracle Database Lite Client Guide for more information.

### 1.6.3 Web Applications

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 2003 or XP. 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 5, "Developing Mobile Web-to-Go Applications".