Skip Headers
Oracle® Database Lite Developer's Guide
10g (10.0.0)
Part No. B13788-01
  Go To Index
Index

Next
Next
 

Contents

Title and Copyright Information

Send Us Your Comments

Preface

Intended Audience
Documentation Accessibility
Structure

1 Overview

1.1 Introduction
1.2 Oracle Database Lite 10g Application Model and Architecture
1.2.1 Oracle Database Lite RDBMS
1.2.2 Mobile Sync
1.2.3 Mobile Server
1.2.4 Message Generator and Processor (MGP)
1.2.5 Mobile Server Repository
1.3 Mobile Development Kit (MDK)
1.3.1 Mobile SQL (MSQL)
1.3.2 Using the Packaging Wizard
1.4 Supported Platforms
1.5 Java Support
1.6 Data Source Name

2 The Oracle Database Lite RDBMS

2.1 Introduction
2.2 Development Interfaces
2.2.1 Development Interface Overview
2.2.1.1 JDBC
2.2.1.2 Starting a Multi User Oracle Database Lite Database Service
2.2.1.3 Accessing the Multi User Oracle Database Lite 10g Database Service
2.2.1.4 ODBC
2.2.1.5 SODA
2.2.2 Mobile Sync Client Module Application Programming Interfaces (APIs)
2.2.3 Oracle Database Lite Load APIs
2.2.4 Oracle Database Lite Load Utility (OLLOAD)
2.2.5 ADO.NET
2.3 Using the Starter Database
2.4 Working With Your Database
2.4.1 Creating a New Database
2.4.2 Creating a Data Source Name with ODBC Administrator
2.4.3 Creating a New Database Using the Command-Line Utility
2.4.4 Connecting to a New Database
2.5 Creating Multiple Users
2.5.1 Pre-defined Roles
2.5.2 Creating Users
2.5.3 Dropping Users
2.5.4 Changing Passwords
2.5.5 Granting Roles
2.5.6 Granting Privileges
2.5.7 Revoking Roles
2.5.8 Revoking Privileges
2.5.9 Building Demo Tables
2.5.10 Populate Your Database Using Mobile SQL
2.5.11 Backing Up a Database
2.5.12 Encrypting and Decrypting a Database
2.6 Oracle Database Lite Transaction Support
2.6.1 Atomicity
2.6.2 Consistency
2.6.3 Isolation
2.6.3.1 Durability
2.6.3.2 Locking
2.6.3.3 Changing the Default Isolation Level
2.6.3.4 Supported Combinations of Isolation Levels and Cursor Types
2.6.4 Tuning the Application
2.7 Support for Linguistic Sort
2.7.1 Creating Linguistic Sort Enabled Databases
2.7.2 How Collation Works
2.7.3 Collation Element Examples
2.7.3.1 Sorting Normal Characters
2.7.3.2 Reverse Sorting of French Accents
2.7.3.3 Sorting Contracting Characters
2.7.3.4 Sorting Expanding Characters
2.7.3.5 Sorting Numeric Characters
2.8 Creating Snapshot Definitions
2.8.1 Creating a Snapshot Definition Declaratively
2.8.2 Creating the Snapshot Definition Programmatically
2.9 Using Oracle Database Lite Samples
2.9.1 Overview
2.9.2 BLOB Manager Example Notes
2.9.3 Running the Visual Basic Sample Application
2.9.3.1 Open Visual Basic
2.9.3.2 View the Sample Application Tables and Data
2.9.3.3 Open the Sample Application
2.9.3.4 View and Manipulate the Data in the EMP Table
2.9.4 ODBC Examples
2.9.4.1 What the Examples Do
2.10 Limitations
2.11 Tracing
2.11.1 Enabling Trace Output
2.11.2 Basic Functions
2.11.3 SQL Tracing
2.11.3.1 The Tid Output
2.11.3.2 SQL Statement Output
2.11.3.3 Compilation Time Output
2.11.3.4 Bind Values Output
2.11.3.5 Explain Plan Output
2.11.3.6 Temporary Table Created Output
2.11.3.7 Table Name Output
2.11.3.8 Temporary Table Sorted Output
2.11.3.9 First Fetch Time Output

3 Synchronization

3.1 Overview
3.1.1 Synchronization Concepts
3.1.1.1 Publication Item
3.1.1.2 Publication
3.1.1.3 Application
3.1.1.4 Subscription
3.1.1.5 Data Subsetting
3.1.1.6 Shared Maps
3.1.2 Synchronization Example
3.2 Synchronization Process
3.2.1 Fast Refresh Synchronization
3.2.1.1 Client Upload and Download Operations
3.2.1.2 Mobile Server Apply Operation
3.2.1.3 Mobile Server Compose Operation
3.2.2 Complete Refresh Synchronization
3.2.3 Synchronizing an Encrypted Database
3.3 Mobile Sync Application Programming Interfaces (APIs)
3.4 The Publish and Subscribe Model and Oracle Database Lite Synchronization
3.4.1 The Publish and Subscribe Model Step by Step
3.5 Using Consolidator to Define the Sample11.java Example
3.5.1 Sample11.java
3.5.2 Create Required Tables Using Standard JDBC
3.5.3 Connecting to the Mobile Server
3.5.4 Creating Publications
3.5.4.1 CreatePublication
3.5.5 Creating Publication Items
3.5.5.1 CreatePublicationItem
3.5.5.2 Defining Publication Items for Updatable Multi-table Views
3.5.5.3 Data Subsetting
3.5.6 Sequence Support
3.5.7 Defining Client Subscription Parameters for Publications
3.5.7.1 SetSubscriptionParameter
3.5.8 Creating Publication Item Indexes
3.5.8.1 CreatePublicationItemIndex
3.5.8.2 Define Client Indexes
3.5.9 Adding Publication Items to a Publication
3.5.9.1 AddPublicationItem
3.5.9.2 Defining Conflict Rules
3.5.9.3 Using Table Weight
3.5.10 Creating Users
3.5.10.1 createUser
3.5.11 Drop User
3.5.11.1 dropUser
3.5.12 Subscribing Users to a Publication
3.5.12.1 CreateSubscription
3.5.13 Instantiating a Subscription
3.5.13.1 InstantiateSubscription
3.6 Other Standard Consolidator Functionality
3.6.1 Client Device Database DDL Operations
3.6.2 Change Password
3.6.2.1 setPassword
3.6.3 Remote Database Link Support
3.6.3.1 Publishing Synonyms for the Remote Object Using CreatePublicationItem
3.6.3.2 Creating a Dependency Hint
3.6.3.3 Remove a Dependency Hint
3.7 Advanced Features for Customizing Consolidator
3.7.1 Compose Phase Customization Using MyCompose
3.7.1.1 Extending MyCompose as a User Defined Sub-Class
3.7.1.2 Primary MyCompose Methods
3.7.1.3 Subsidiary MyCompose Methods
3.7.1.4 Consolidator API Methods for Registering MyCompose Sub-Classes
3.7.2 Sync Discovery API
3.7.2.1 getDownloadInfo Method
3.7.2.2 DownloadInfo Class Access Methods
3.7.2.3 PublicationSize Class
3.7.3 Map Table Partition APIs
3.7.3.1 Create a Map Table Partition
3.7.3.2 Add Map Table Partitions
3.7.3.3 Drop a Map Table Partition
3.7.3.4 Drop All Map Table Partitions
3.7.3.5 Merge Map Table Partitions
3.7.4 Modifying a Publication Item Using AlterPublicationItem
3.7.4.1 Alter Publication Item
3.7.5 Fast Refresh and Update Operation for Multi-Table Publications (Views)
3.7.5.1 Updatable Parent Tables
3.7.5.2 Using Parent Table Hints and INSTEAD OF Triggers
3.7.5.3 Fast Refresh for Views
3.7.5.4 Complete Refresh for Views
3.7.6 Virtual Primary Key
3.7.6.1 Create Virtual Primary Key Column
3.7.6.2 Drop Virtual Primary Key Column
3.7.7 Caching Publication Item Queries
3.7.7.1 Enabling Publication Item Query Caching
3.7.7.2 Disabling Publication Item Query Caching
3.7.8 Binding User-Defined PL/SQL Procedures
3.7.9 Queue Interface for Customizing Replication
3.7.9.1 Queue Interface Operation
3.7.9.2 Queue Creation
3.7.9.3 Queue Interface PL/SQL Procedure
3.7.9.4 CreateQueuePublicationItem API
3.7.9.5 Defining a PL/SQL Package Outside the Repository
3.7.10 Null Sync Callout
3.7.11 Foreign Key Constraints in Updatable Publication Items
3.7.11.1 Foreign Key Constraint Violation Example
3.7.11.2 Avoiding Constraint Violations with BeforeApply and After Apply
3.7.11.3 Avoiding Constraint Violations with Table Weights
3.7.12 Callback Customization for Before and After Compose/Apply
3.7.13 Callback Customization for DML Operations
3.7.13.1 DML Procedure Example
3.7.14 Restricting Predicate
3.7.15 Priority-Based Replication
3.7.16 Shared Maps
3.7.16.1 Concepts
3.7.16.2 Performance Attributes
3.7.16.3 Usage
3.7.16.4 Compatibility and Migration
3.8 Synchronization Errors and Conflicts
3.8.1 Versioning
3.8.2 Winning Rules
3.8.3 Resolving Conflicts Using the Error Queue
3.8.3.1 Execute Transaction
3.8.3.2 Purge Transaction
3.8.4 Space Constraints
3.9 Mapping Datatypes Between the Oracle Server and Clients
3.9.1 Oracle Database Lite Datatypes

4 Developing Mobile Web Applications

4.1 Setting up the Mobile Client
4.2 Developing and Testing the Application
4.2.1 Building Web-to-Go Applications
4.2.1.1 Static Components
4.2.1.2 Dynamic Components
4.2.1.3 Database Components
4.2.1.4 Database Connections
4.2.2 Application Roles
4.2.3 Developing JavaServer Pages
4.2.3.1 Mobile Server or Mobile Development Kit Web Server
4.2.3.2 Mobile Client for Web-to-Go
4.2.4 Developing Java Servlets for Web-to-Go
4.2.4.1 Limitations
4.2.4.2 Accessing Applications on the Mobile Development Kit for Web-to-Go
4.2.4.3 Creating a Servlet
4.2.4.4 Running a Servlet
4.2.4.5 Debugging a Servlet
4.2.4.6 Accessing the Schema Directly in Oracle Database Lite
4.2.5 Using Web-to-Go Applets
4.2.5.1 Creating the Web-to-Go Applet
4.2.5.2 Creating the HTML Page for the Applet
4.2.6 Developing Applet JDBC Communication
4.2.6.1 getConnection()
4.2.6.2 Design Issue
4.2.7 Developing Applet Servlet Communication
4.2.7.1 Creating the Web-to-Go Servlet
4.2.8 Debugging Web-to-Go Applications
4.2.8.1 Running Sample 1 Using Oracle9i JDeveloper
4.2.9 Customizing the Workspace Application
4.2.9.1 Web-to-Go Parameters
4.2.9.2 Sample Workspace
4.2.10 Using the Mobile Server Admin API

5 Native Application Development

5.1 Supported Platforms
5.2 Java Support
5.3 Data Source Name
5.4 Mobile Sync Application Programming Interfaces (APIs)
5.4.1 COM Interface
5.4.1.1 Features and Components
5.4.1.2 ISync Interface
5.4.1.3 ISyncOption Interface
5.4.1.4 Selective Synchronization
5.4.1.5 COM Interface SyncParam Settings
5.4.1.6 COM Interface TransportParam Parameters
5.4.1.7 ISyncProgressListener Interface
5.4.2 C/C++ Interface
5.4.2.1 ocSessionInit
5.4.2.2 ocSessionTerm
5.4.2.3 ocSaveUserInfo
5.4.2.4 ocDoSynchronize
5.4.2.5 ocSetTableSyncFlag
5.4.2.6 ocGetPublication
5.4.2.7 C/C++ Data Structures
5.5 Using the Packaging Wizard

6 Oracle Database Lite 10g ADO.NET Provider

6.1 Classes
6.1.1 OracleConnection
6.1.2 Transaction Management
6.1.3 OracleCommand
6.1.4 OracleParameter and Prepared Statements
6.1.4.1 Parameters
6.1.5 OracleBlob and Large Object Support
6.1.6 OracleSync and Data Synchronization
6.2 Running the Demo
6.3 Limitations
6.3.1 Thread Safety

7 Developing Mobile Applications for Palm OS Devices

7.1 Installing Oracle Database Lite Runtime on the Device
7.2 Uninstalling or Replacing Oracle Database Lite Runtime
7.3 Running Oracle Database Lite on Palm OS Emulator
7.4 Running Oracle Database Lite on Palm OS Simulator
7.5 Using Oracle Database Lite Base Libraries
7.6 Building a SODA Application
7.7 Building a SODA Forms Application
7.8 Building an ODBC Application
7.9 Packaging your Application with Oracle Database Lite Runtime
7.10 Customizing Oracle Database Lite Runtime
7.11 Palm Shared Library Manager (PSLM)

8 Palm Shared Library Manager (PSLM)

8.1 Overview
8.2 Trying out PSLM
8.3 Writing a PSLM Library
8.4 Building a Shared Library Project
8.5 Calling a PSLM Library from Your Application
8.6 Building an Application Using PSLM
8.7 Exceptions Across Modules
8.8 Cloaked Shared Libraries
8.9 Patching the CodeWarrior Runtime

9 Using Mobile Sync for Palm

9.1 Configuring mSync
9.2 Using HotSync to Synchronize Data with the Mobile Server
9.2.1 Configuring HotSync for a PalmOS Device
9.2.2 HotSync Timeout Errors
9.2.3 Configuring PalmOS Emulator for HotSync
9.3 Using Network Sync
9.3.1 Synchronizing Using a Cradle and Windows Desktop
9.3.2 Network Sync With PalmOS Emulator

10 Building Offline Mobile Applications for Win32: A Tutorial

10.1 Overview
10.2 Developing Offline Mobile Applications for Win32
10.2.1 Command Sequence
10.2.1.1 Step 1. Create TASK Table on the Server Database
10.2.1.2 Step 2. Define a Publication Item and Publish the Application
10.2.1.3 Step 3. Create Users and Subscriptions
10.2.1.4 Step 4. Install the Oracle Database Lite 10g Client and the Mobile Field Service Application and Data
10.2.1.5 Step 5. Browse the TASK Snapshot and Update a Row
10.2.1.6 Step 6. Synchronize the Change with the Server
10.2.1.7 Step 7. Check your changes on the server and modify a server record
10.2.1.8 Step 8. Synchronize again to get the server changes
10.2.1.9 Step 9. Develop your Mobile Field Service Application Using Oracle Database Lite
10.2.1.10 Step 10. Republish the Application with the Application Program

11 Building Offline Mobile Applications for Windows CE: A Tutorial

11.1 Overview
11.1.1 Before You Start
11.1.1.1 Application Development Computer Requirements
11.1.1.2 Client Device Requirements
11.2 Developing the Application
11.2.1 Creating Database Objects in the Oracle Server
11.2.1.1 The Pocket PC Transport Application Database Objects
11.2.2 Writing the Application Code
11.2.2.1 Transport Module (Transport.vb)
11.2.2.2 Main Form (frmMain.vb)
11.2.2.3 View Packages (frmView.vb)
11.2.2.4 Create Package (frmNew.vb)
11.2.3 Compiling the Application
11.2.3.1 Creating CAB Files
11.2.3.2 Installing the Application from the CAB File
11.3 Packaging and Publishing the Application
11.3.1 Defining the Application Using the Packaging Wizard
11.3.1.1 Creating a New Application
11.3.2 Defining the Application Connection to the Oracle Database Server
11.3.3 Defining Snapshots
11.3.4 Publishing the Application
11.4 Administering the Application
11.4.1 Starting the Mobile Server
11.4.2 Launching the Mobile Manager
11.4.3 Creating a New User
11.4.4 Setting the Application Properties
11.4.5 Granting User Access to the Application
11.4.6 Starting the Message Generator and Processor (MGP)
11.5 Running the Application on the Pocket PC
11.5.1 Installing the Oracle Database Lite Mobile Client for Pocket PC
11.5.2 Installing and Synchronizing the Transport Application and Data

12 Building Mobile Web Applications: A Tutorial

12.1 Overview
12.1.1 Before You Start
12.1.1.1 Development Computer Requirements
12.1.1.2 Client Computer Requirements
12.2 Developing the Application
12.2.1 Step 1: Creating Database Objects in Oracle Database Lite
12.2.1.1 The To Do List Application Database Objects
12.2.1.2 Required Action
12.2.2 Step 2: Compiling the Application
12.2.2.1 Required Action
12.2.3 Step 3: Defining the Application and Registering the Servlet
12.2.3.1 The Packaging Wizard
12.2.3.2 Required Action
12.2.4 Step 4: Conducting a Trial Run
12.2.4.1 The Mobile Development Kit for Web-to-Go Web Server
12.2.4.2 Required Action
12.3 Packaging the Application
12.3.1 Step 1: Defining the Application
12.3.1.1 The Packaging Wizard
12.3.1.2 Required Action
12.3.2 Step 2: Specifying Database Details
12.3.2.1 Required Action
12.3.3 Step 3: Defining the Snapshot
12.3.3.1 The Snapshots Tab
12.3.3.2 Required Action
12.3.4 Step 4: Defining Sequences
12.3.5 Step 5: Creating SQL Files for the Application
12.3.5.1 Required Action
12.3.6 Step 6: Package the Application
12.3.6.1 Required Action
12.4 Publishing the Application
12.4.1 Step1: Create the Table Owner Account
12.4.2 Step 2: Create the Database Objects in the Oracle Database
12.4.2.1 Required Action
12.4.3 Step 3: Start the Mobile Server
12.4.3.1 Required Action
12.4.4 Step 4: Log on to the Mobile Server and Start the Mobile Manager
12.4.4.1 Required Action
12.4.5 Step 5: Upload the Application
12.4.5.1 Required Action
12.5 Administering the Application
12.5.1 Step 1: Starting the Mobile Manager
12.5.1.1 Required Action
12.5.2 Step 2: Using the Mobile Manager to Create a New User
12.5.2.1 Required Action
12.5.3 Step 3: Setting Application Properties
12.5.3.1 Required Action
12.5.4 Step 4: Granting User Access to the Application
12.5.4.1 Required Action
12.5.5 Step 5: Defining Snapshot Template Values for the User
12.5.5.1 Required Action
12.6 Running the Application on the Mobile Client for Web-to-Go
12.6.1 Step 1: Installing the Mobile Client for Web-to-Go
12.6.1.1 Required Action
12.6.2 Step 2: Logging into the Mobile Client for Web-to-Go
12.6.2.1 Required Action
12.6.3 Step 3: Synchronizing the Mobile Client for Web-to-Go
12.6.3.1 Required Action

13 Building Offline Mobile Web Applications Using BC4J: A Tutorial

13.1 Overview
13.1.1 Before You Start
13.1.1.1 Development Computer Requirements
13.2 Developing the Application
13.2.1 Creating the Database Connection
13.2.2 Creating the BC4J Component
13.2.3 Configuring the BC4J Component to Use the WTGJdbc Connection
13.2.4 Building and Deploying the BC4J Component as a Simple Archive
13.2.5 Writing the JSP Application to Access the BC4J Component
13.2.6 Deploying the JSP Application as a Simple Archive
13.3 Packaging the JSP Application
13.4 Publishing and Configuring the JSP Application from the Mobile Manager
13.5 Testing the BC4J Application
13.6 Running the BC4J Application on the Mobile Client for Web-to-Go
13.7 Deploying the Sample Application

A Optimizing SQL Queries

A.1 Optimizing Single-Table Queries
A.2 Optimizing Join Queries
A.2.1 Create an Index on the Join Column(s) of the Inner Table
A.2.2 Bypassing the Query Optimizer
A.3 Optimizing with Order By and Group By Clauses
A.3.1 IN Subquery Conversion
A.3.2 ORDER BY Optimization with No GROUP BY
A.3.3 GROUP BY Optimization with No ORDER BY
A.3.4 ORDER BY Optimization with GROUP BY
A.3.5 Cache Subquery Results

B Oracle Database Lite Load Application Programming Interfaces (APIs)

B.1 Overview
B.2 Oracle Database Lite Load APIs
B.2.1 Connecting to the Database: olConnect
B.2.2 Disconnecting from the Database: olDisconnect
B.2.3 Deleting All Rows from a Table: olTruncate
B.2.4 Setting Parameters for Load and Dump Operations: olSet
B.2.5 Loading Data: olLoad
B.2.6 Dumping Data: olDump
B.2.7 Compiling
B.2.8 Linking
B.3 File Format
B.3.1 Header Format
B.3.2 Parameters
B.3.3 Data Format
B.3.3.1 CSV Format
B.3.3.2 FixedAscii Format
B.4 Limitations

C Web-to-Go Sample Applications

C.1 Introduction
C.1.1 The Mobile Server
C.1.2 The Mobile Development Kit for Web-to-Go
C.1.3 Accessing Sample Programs from the Mobile Development Kit for Web-to-Go
C.1.4 Accessing Sample Programs from the Mobile Server
C.2 Sample 1 - Hello World
C.2.1 Source Code Location
C.2.2 Application Files
C.3 Sample 3 - Recording Tracker
C.3.1 Using Sample 3
C.3.2 Sample 3 Database Tables
C.3.3 Sample 3 Servlets
C.3.4 Sample 3 Resource Bundle
C.3.5 Source Code Location
C.3.6 Application Files
C.4 Sample 4 - Hello Applet
C.4.1 Sample 4 Servlets
C.4.2 Source Code Location
C.4.3 Application Files
C.5 Sample 6 - Image Gallery
C.5.1 Source Code Location
C.5.2 Application Files
C.6 Sample 7 - Employee Data Applet
C.6.1 Source Code Location
C.6.2 Application Files

D ODBC Support on Palm

D.1 ODBC Support
D.1.1 SQLAllocConnect
D.1.2 SQLAllocEnv
D.1.3 SQLAllocHandle
D.1.4 SQLAllocStmt
D.1.5 SQLFreeConnect
D.1.6 SQLFreeEnv
D.1.7 SQLFreeHandle
D.1.8 SQLFreeStmt
D.1.9 SQLConnect
D.1.10 SQLDisconnect
D.1.11 SQLBindParameter
D.1.12 SQLPrepare
D.1.13 SQLExecDirect
D.1.14 SQLExecute
D.1.15 SQLFetch
D.1.16 SQLBindCol
D.1.17 SQLDescribeCol
D.1.18 SQLError
D.1.19 SQLGetData
D.1.20 SQLNumResultCols
D.1.21 SQLRowCount
D.1.22 SQLTransact

Glossary

Index