Oracle9i Lite Developers Guide for Windows 32
Part No. A95912-01
This document provides an overview of the Mobile Development Kit and its components. Topics include:
The Oracle9i Lite Mobile Development Kit includes:
Oracle Lite database A lightweight, embedded database.
Mobile Sync A transactional replication engine.
Using the Mobile Development Kit for Windows, you can deploy your enterprise data and applications to distributed, mobile platforms. The data on the devices is stored in an Oracle Lite database object-relational database specifically designed for each platform. Once deployed, the Oracle Lite database is transparent to the end user, and requires minimal tuning or administration.
Note:This document discusses developing applications and synchronizing with databases on a Windows system only. For Windows CE, see the Developer's Guide for Windows CE. For EPOC, see the Developer's Guide for EPOC.
Oracle Lite database is a small footprint, Java-enabled, relational database created specifically for laptop computers, handheld computers, PDAs, and information appliances. The Oracle Lite database runs on Windows 95/98/NT/2000, Windows CE/Pocket PC, Palm Computing, and EPOC. Oracle Lite database provides ODBC and OKAPI programming interfaces to build database applications from a variety of programming languages such as C/C++, Visual Basic and Satellite Forms. These database applications are capable of use while disconnected from the database server.
Mobile Sync 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 95/98/NT/2000, Windows CE/Pocket PC, Palm Computing, and EPOC.
There are several components to which the developer should have access:
Oracle8i database server.
Mobile Server which is the gateway between the mobile device and the Oracle8i database server.
Message Generator and Processor (MGP), a background process that manages the transaction and data exchange between the Mobile Server and the Oracle8i database server
Mobile Sync resides on the mobile devices and talks to the Mobile Server over any given communication mechanism.
A communication mechanism between the mobile device and Mobile Server such as HTTP.
Typically, the handheld device needs to carry only a small subset of the data contained in a central database. For example, the sales force in a particular region only needs access to the contact information for customers in that region. Mobile Server, uses a publish/subscribe model for managing data and applications. In this model the publication, which you create using the Mobile Server Admin API, defines the subset of data that you make available to particular subscribers. A subscription associates a user with a publication. As the client and server exchange data, Mobile Server detects and resolves data conflicts using rules that you define.
Using a publish/subscribe model for data distribution, the Mobile Server enables individual client devices to subscribe to different subsets of data based on parameters such as user ID or location. Mobile Server supports the HTTP transport protocol between devices and servers.
Mobile SQL allows you to create, access, and manipulate Oracle Lite database on laptops and handheld devices. Using Mobile SQL you can:
Execute SQL statements
This section provides an overview of the development interface. Topics include:
For relational database development:
For object database development:
Object Kernel API (OKAPI)
The models can be used either independently or in combination. For example, you can have OKAPI and JDBC calls in the same program. The following diagram illustrates the Oracle Lite application interfaces. The shaded interfaces support the relational model.
The Java Database Connectivity (JDBC) interface specifies a set of Java classes that provide an ODBC-like interface to SQL databases for Java applications. JDBC, part of the JDK (Java Developer's Kit) core, provides an object interface to relational databases. Oracle Lite database supports JDBC through an Oracle Lite, Type-2 JDBC driver that interprets the JDBC calls and passes them to an Oracle Lite database. Oracle Lite database uses a Type-4 JDBC driver when connecting with an Oracle Lite Multi User Service running under Branch Office. Type-2 drivers can be used in both remote and native databases.
For more information on JDBC and Oracle Lite, see the Oracle Lite Java Developer's Guide.
Microsoft's Open Database Connectivity (ODBC) interface is a procedural, call-level interface for accessing SQL databases, and is supported by most database vendors. It specifies a set of functions that allow applications to connect to databases, prepare and execute SQL statements at runtime, and retrieve query results. Oracle Lite supports Level 3 compliant ODBC 2.0 and the ODBC 3.5 drivers through Oracle Lite ODBC drivers that interprets the ODBC calls and passes them to Oracle Lite.
For more information on ODBC:
See Microsoft's ODBC documentation.
See the Oracle Lite ODBC sample application. For its location, see Chapter 2, " Using the Oracle Lite Samples".
See Section C.7, "ODBC Administrator and the Oracle Lite ODBC Driver" for more information.
Note:ODBC 3.5 drivers do not support Java Stored Procedures.
run-time class creation and access to class information
direct object access based on object identity and navigation
object clustering and grouping
queries on classes and their subclasses
object naming and inter-object relationships
binary large object (BLOB) data
transaction and crash recovery
For more information, see the C and C++ Object Kernel API Reference.
This API allows the application to programmatically control the replication process. The application invokes Mobile Sync API functions to initiate the replication process and captures error messages generated by the Mobile Sync API. For more information on the Mobile Sync API please see Section 3.6, "Programming with the OCAPI.DLL".
The Oracle Lite Load Utility allows you to load data from an external file into a table in an Oracle Lite database, or to unload (dump) data from a table in an Oracle Lite database to an external file. For more information on OLLOAD see Appendix E, " Oracle Lite Loading Utility API".
When you install Oracle Lite database, an ODBC data source name (DSN) POLITE is created, and a starter database POLITE.ODB is dedicated. The location of new databases for the DSN POLITE is set to Oracle_Home\Mobile\SDK\oldb40.
A default user named SYSTEM is set up for you during installation of the samples. SYSTEM contains all database privileges and has a no password. You can create a password for SYSTEM by using the ALTER USER command. (The following section shows sample syntax.) You can either use the default user name or establish user names of your own.
Note:Review the Oracle Lite SQL Reference before using the starter database. This reference describes the Structured Query Language (SQL) used to manage information in an Oracle Lite database.
You can connect to the Oracle Lite starter database using an application such as Mobile SQL. Mobile SQL is a command line interface. To connect to the POLITE database, type "Connect" followed by the "username/password@dsnname". For example:
You can assign SYSTEM a password by typing the command:
ALTER USER SYSTEM IDENTIFIED BY <password>
Note:For more information, see Section 1.5.4, "Changing Passwords".
When connecting to the starter database from an ODBC application, use the default ODBC DSN, POLITE.
This section provides an overview of working with your Oracle Lite database, including creating a database, connecting to a database, creating users, and administering the database.
When you create a new database using the POLITE data source name, the new database file is located in the Oracle_Home\Mobile\SDK\oldb40 directory. For ease of maintenance, it is recommended that you use one database directory for all of your databases.
Note:All newly created databases contain the user SYSTEM, which has a NULL password.
You can create a new data source name using the ODBC Administrator. See Section 1.4.2, "Creating a Data Source Name with ODBC Administrator" for more information.
The ODBC Administrator is a tool provided by Microsoft to manage the ODBC.INI file and associated registry entries in Windows 95/98/NT and Windows 2000. It allows you to add a data source name and specify the database file you want to dedicate as the default for the data source name. See Section C.7, "ODBC Administrator and the Oracle Lite ODBC Driver" for more information on the ODBC Administrator, and for instructions on creating a data source name using the tool.
To create a new database from the command line, use the CREATEDB utility. The syntax is:
CREATEDB mydatabase mydbname
CREATEDB polite newdb
mydatabase is the DSN name and mydbname is the new database name.
See Section C.2, "CREATEDB", for more information.
To connect to a new database using Mobile SQL, connect as the user SYSTEM, with the password MANAGER and the data source name. For example:
You can replace MYDATABASE with a previously defined ODBC data source name.
If there is more than one database associated with a data source name, use the format:
dsn is the data source name and dbname is the name of the database. For example:
You can create multiple users in Oracle Lite by using the CREATE USER command. A user is not a schema. When you create a user, Oracle Lite creates a schema with the same name and automatically assigns it to that user as the default schema. You can access database objects in the default schema without prefixing them with the schema name.
Users with the appropriate privileges can create additional schemas by using the CREATE SCHEMA command, but only the user can connect to the database. You cannot connect to the database using the schema name, even though it may be the same as the user name, they are different objects.
These schemas are owned by the user who created them and require the schema name prefix in order to access their objects.
When you create a database using the CREATEDB utility or the CREATE DATABASE command, Oracle Lite creates a special user called SYSTEM, which has all database privileges and is not assigned a password. A password may be assigned to SYSTEM if required. You can use SYSTEM as the default user name until you establish user names of your own.
Oracle Lite does not permit a user other than SYSTEM to access data or perform operations in a schema that is not the user's own. To access data and perform operations in another user's schema, a user must be granted specific privileges.
Oracle Lite combines some privileges into pre-defined roles for convenience. In many cases it is easier to grant a user a pre-defined role than to grant specific privileges in another schema. Oracle Lite does not support creating or dropping roles. Following is a list of Oracle Lite pre-defined roles:
Table 1-1 Pre-Defined Roles
|Role Name||Privileges Granted To Role|
|ADMIN||Enables the user to create other users and grant privileges other than DBA and ADMIN on any object in the schema:
CREATE SCHEMA, CREATE USER, ALTER USER, DROP USER, DROP SCHEMA, GRANT, REVOKE
|DBA||Enables the user to issue the following DDL statements which otherwise can only be issued by SYSTEM:
All ADMIN privileges, CREATE TABLE, CREATE ANY TABLE, CREATE VIEW, CREATE ANY VIEW, CREATE INDEX, CREATE ANY INDEX, ALTER TABLE, ALTER VIEW, DROP TABLE, DROP VIEW, and DROP INDEX.
|RESOURCE||The RESOURCE role grants the same level of control as the DBA role, but only over the user's own domain. The user can execute any of the following commands in a SQL statement:
CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE CONSTRAINT, ALTER TABLE, ALTER VIEW, ALTER INDEX, ALTER CONSTRAINT, DROP TABLE, DROP VIEW, DROP INDEX, DROP CONSTRAINT, and GRANT or REVOKE privileges on any object under a user's own schema.
General Note:Unlike the Oracle server, Oracle Lite does not commit data definition language (DDL) commands until you explicitly issue the COMMIT command.
You can create users if you are connected to the database as "system", or if you are granted the ADMIN or DBA role. To create a user, issue the following statement:
CREATE USER user IDENTIFIED BY password
Here, user is a unique user name with up to 128 characters, beginning with a letter, and password is a string of up to 128 characters. This statement creates a schema with the user name and assigns the schema as the default schema for the user.
For encrypted databases, all user names and passwords are written to a file named dsn.opw. Each user can then use the password as a "key" to unlock the .opw file before the .odb file is accessed. When you copy or back up the database, you should include the .opw file.
You can drop users if you are connected to the database as "system", or if you are granted the ADMIN or DBA role.
To drop a user and all schemas owned by the user, use the syntax:
DROP USER user
To drop all objects in the user's schema before dropping the user, use the syntax:
DROP USER user CASCADE
For more information on the DROP USER command, see the Oracle Lite SQL Reference.
You can change a user's password if you meet one of the following conditions:
You are connected to the database as that user
You are connected to the database as SYSTEM
You are granted the ADMIN, DBA, or RESOURCE role
To change a user's password, issue the following statement:
ALTER USER user IDENTIFIED BY password
You can grant the ADMIN or DDL roles to users by issuing the following statement:
GRANT role TO user_list
Here, user_list is either one user or a comma-separated list of multiple users.
You can grant privileges on a database object to users by issuing the following statement:
GRANT privilege_list ON object_name TO user_list
Here, privilege_list is either a comma-separated list of the following privileges or a combination called ALL:
Object_name is a table name prefixed with a schema name.
If privilege_list is ALL, then the user can INSERT, DELETE, UPDATE or SELECT from the table or view. If privilege_list is either INSERT, DELETE, UPDATE, or SELECT, then the user has that privilege on a table.
You can revoke user roles by issuing the following statement:
REVOKE role FROM user_list
You can revoke privileges on database objects from users by issuing the following statement:
REVOKE privilege_list ON table_name FROM user_list
Note:Any application can use the ODBC interface to issue CREATE USER, DROP USER, and ALTER USER statements.
Oracle Lite comes with a script called POLDEMO.SQL, which enables you to build the same tables that are in your Oracle Lite default starter database (POLITE.ODB).
You can use SQL scripts to create tables and schema, and to insert data into tables. A SQL script is a text file, generally with a .SQL extension, that contains SQL commands. You can run a SQL script from the Mobile SQL prompt by typing:
You can also type:
SQL> START filename
Note:You do not need to include the .SQL file extension when running the script.
The Oracle Lite database occupies one file, and has dependent log files which can be backed up by copying to another location. Before any files can be copied, however, your database administrator must shut down the database which ensures that log file changes are applied to the database. Once that has been accomplished, you can copy the *.odb, *.opw, and *.plg files to another directory to make a backup of the database.
Two tools, ENCRYPDB and DECRYPDB, allow you to encrypt and decrypt Oracle Lite databases. These tools allow you to encrypt an Oracle Lite database with a password. The password can be used to prevent unauthorized access to the database and also to encrypt the database so that the data stored in the database files cannot be interpreted by examining the files. The password is used to derive a 40-bit encryption key. Oracle Lite uses a version of the Data Encryption Standard (DES) algorithm known as CAST5.
When an application connects to an Oracle Lite database, it begins a transaction with the database. There can be a maximum of 32 connections to an Oracle Lite database. Each connection to an Oracle Lite database maintains a separate transaction.
A transaction is a sequence of database operations, such as SELECT, UPDATE, DELETE, and INSERT. All operations either succeed and are committed or are rolled back. This is called the atomicity property of a transaction.
Oracle Lite implements atomicity by not updating the actual database file until a database commit. During commit, a temporary undo log is created and then the database file is updated. If an event, such as a power outage, interrupts commit, the database is restored from the log during the next connection.
Transactions preserve database consistency. A transaction transforms a consistent state of the database into another consistent state, without necessarily preserving consistency at all intermediate points. Oracle Lite does not permit a transaction to commit if it violates a constraint and would therefore violate consistency.
Transactions are isolated from one another. Even though many transactions run concurrently, a given transaction's updates are concealed from other transactions until the transaction commits. Oracle Lite supports the following isolation levels for transactions:
Table 1-2 Isolation Levels
|Read Committed||This isolation level does not require locks for a SELECT statement by creating a temporary snapshot of the most recent committed version of the data. A READ COMMITTED transaction is not blocked by other transactions that update or insert new data into the tables requested by the SELECT statement. As a result, the same Select statement can be used to return a different set of data.
A SELECT statement containing a FOR UPDATE clause is executed as if it is running in a REPEATABLE READ isolation level.
In Oracle Lite, a SELECT statement can execute Java stored procedures. If the transaction executing the Java stored procedure is in the READ COMMITTED isolation level and the Java stored procedure updates the database, then the SELECT statement to execute the Java stored procedure must have a FOR UPDATE clause. Otherwise, Oracle Lite issues an error.
|Repeatable Read||In this isolation level, a query acquires read locks on all its returned rows. More rows may be read locked because of the complexity of the query itself, the indexes defined on its tables, or because of the execution plan chosen by the query optimizer. The REPEATABLE READ isolation level provides less currency than a READ COMMITTED isolation level transaction because the locks are held until the end of the transaction.
When a transaction inserts additional rows meeting the search criteria given in a query, subsequent executions of that same query can return extra rows that meet its search criteria.
If a FOR UPDATE clause is used in a query, a short-term update lock is acquired on the current row(s) being selected. If a row is updated, the lock is converted into an exclusive lock. An exclusive lock prevents any other transaction running in an isolation level other than READ COMMITTED to access this row. If the row is not updated but the next row is fetched, the update lock is downgraded to a read lock, permitting other transactions to read the row.
|Serializable||This isolation level acquires shared locks on all tables participating in the query. The same set of rows is returned for the repeated execution of the query in the same transaction. Any other transaction attempting to update any rows in the tables in the query is blocked.|
|Single User||In this isolation level only one connection is permitted to the database. The transaction has no locks and consumes less memory.|
See the ODBC documentation for more information on isolation levels, specifically, for the terms "Dirty Read", "Nonrepeatable Read", and "Phantom", which define transaction isolation levels.
Transactions are guaranteed to be durable. That is, once a transaction commits, all its changes are persistent in the database file even if the system subsequently fails at any point. If a transaction fails during a commit or rollback due to some system failure, the undo log file is required to restore the database to a consistent state.
Oracle Lite supports row level locking. Whenever a row is read, it is read locked. Whenever a row is modified, it is write locked. Different transactions can read the same row, which is read locked. However, a write locked row cannot be accessed by another transaction.
In Oracle Lite the READ COMMITTED isolation level is the default.
You can change the default isolation level for a data source name (DSN) by using the ODBC Administrator, or by manually editing the ODBC.INI file to include:
IsolationLevel = XX
XX is RC, RR, SR, or SU.
Also, you can establish the isolation level of a transaction by using the SQL statement:
SET TRANSACTION ISOLATION LEVEL <ISOLATION_LEVEL>;
ISOLATION_LEVEL is READ COMMITTED, REPEATABLE READ, SERIALIZABLE, or SINGLE USER.
See Section 22.214.171.124, "Supported Combinations of Isolation Levels and Cursor Types", for more information.
The following table shows the supported combinations of isolation levels and cursor types. Isolation levels appear in the left column and cursor types appear in the top row. "S" indicates supported, "U" indicates unsupported.
Table 1-3 Supported Combinations
|Isolation Level||Forward Only||Static||Keyset Driven||Dynamic|
Unsupported combinations generate error messages.
Tuning your application design ideally occurs before you begin to implement your application. Before beginning your design, you should carefully read about each of the Oracle Lite features available and consider which features best suit your requirements. Also, you should work with your Oracle database administrator to determine how the Oracle master site can be tuned to accommodate your application. Some specific design tips to consider are outlined in Appendix D, " Optimizing SQL Queries ".
Applications require data to function, which they access through the Mobile Server. An application must be directed to the proper tables on the database which is done through the use of the snapshot on the client and the publication item on the Mobile Server. The snapshot definition is the process Mobile Server uses to direct the application which tables to use.
In most situations, the tables already exist that you will create snapshots of for your application to use. The following techniques can be used to create publication items on the Mobile Server, which then automatically create snapshots on the client when you synchronize with the database. The options for creating snapshot definitions are:
Creating a Snapshot Definition Declaratively - Create publication items using the Packaging Wizard. This is the recommended method.
Creating the Snapshot Definition Programmatically - Create a publication item programmatically using the Consolidator Admin API.
This method takes advantage of the Mobile Server Packaging Wizard. This GUI tool can be used to package applications into a .jar file. This file is the deployment descriptor for the application which contains the information necessary for Mobile Server to manage the application. This .jar file is published to the Mobile Server repository.
A secondary feature is the ability of the Packaging Wizard to generate a SQL script which can be executed to create database tables which will be used by your application. These tables are the Oracle 8i database base tables that the Mobile Server synchronizes with.
The convenience of a graphical tool is a safer and less error prone technique for developers to create a mobile application. Before actual application programming begins, the following steps must be executed:
Verify that the base tables exist on the database, if not:
Use the Packaging Wizard to collect the snapshot definitions.
Use the Packaging Wizard to generate a .jar file and a SQL script.
Execute the SQL script on the database to create the base tables, if they do not exist there yet.
Publish the .jar file to the Mobile Server repository.
Setup the Mobile Client.
Synchronize the Mobile Client with the Mobile Server to create the client-side snapshots.
Creating the snapshot definitions using the Packaging Wizard\Mobile Server architecture allows a centralized server to be used to manage and deploy mobile applications and the snapshot definitions they require. This documentation provides a step-by-step approach in Chapter 4, " Using the Packaging Wizard".
[[[Note to reviewers: Use of "publication" and "publication item" in the following section is likely to be revised to snapshot definition and snapshot.]]]
The second way to create a database and populate data is to create a snapshot definition programmatically using the Consolidator Admin API, which is described in Chapter 3, " Synchronization". The terminology is slightly different because Mobile Server interacts with both the client system and the Oracle8i database. Mobile Server deals with everything in terms of publications.
A publication includes data replication objects such as publication items which are equivalent to snapshot definitions. The database base tables must exist before the Consolidator Admin API can be invoked. The following steps are required to create a distributed database schema:
Creating a Publication
Creating a Publication Item
Create User ID
Creating a Subscription
Publications are template groups containing metadata such as table subsetting definitions and indexes. You can create publications using the Consolidator Admin API. This API contains Java functions that implement the publish/subscribe model. You can call the functions in these APIs from within Java programs as standard function calls.
A publication item is a SQL select statement that specifies which data subset of the parent database and is replicated on the client at when synchronization occurs. A publication item usually corresponds to a snapshot on the client device. You can create publication items using the Consolidator Admin API. This API contains Java functions that implement the publish/subscribe model. You can call the functions in this API from within Java programs as standard function calls.
Each client is identified by a user ID. For development purposes, a user ID must be created using the Consolidator Admin API in order to assign data subscriptions to a particular user.
Subscription links a user to a publication. You can create subscriptions using the Consolidator Admin API. This API contains Java functions that implement the publish/subscribe model. You can call the functions in this API from within Java programs as standard function calls. To create publications and subscriptions using Java, see Chapter 3, "The Publish and Subscribe Model".
Creating publication items programmatically requires enhanced skills in Java and the Consolidator Admin API and is therefore more labor intensive. Oracle always recommends using the Packaging Wizard described in Chapter 4, " Using the Packaging Wizard", which requires more steps, but is more user friendly.