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

Previous
Previous
Next
Next
 

2 The Oracle Database Lite RDBMS

This chapter presents the Oracle Database Lite Relational Database Management System (RDBMS). It discusses the following topics:

2.1 Introduction

The Oracle Database Lite RDBMS is a small footprint, administration free, object-relational database management system that supports ODBC, JDBC, and SODA interfaces. SODA provides access to SQL as well as object-oriented functionality. The Oracle Database Lite RDBMS supports SQL92 language with some extensions. It is designed to be used as a local RDBMS for mobile clients. Data stored in the Oracle Database Lite database can be synchronized with the data stored in Oracle server databases, such as Oracle9i.

2.2 Development Interfaces

This section provides an overview of the development interface. Topics include:

2.2.1 Development Interface Overview

Oracle Database Lite provides the following interfaces for developing database applications:

  • For relational database development:

    • JDBC

    • ODBC

  • For object and relational database development:

    • Simple Object Data Access (SODA)

Any interface that supports ODBC or JDBC data sources, such as ADO can also be used to access Oracle Database Lite. The interfaces can be used either independently or in combination.

2.2.1.1 JDBC

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 Database Lite supports JDBC through an Oracle Database Lite Type 2 and Type 4 (for multi user version only) JDBC drivers that interpret the JDBC calls and pass them to Oracle Database Lite.

The following section describes how to start a multi user Oracle Database Lite service.

2.2.1.2 Starting a Multi User Oracle Database Lite Database Service

Oracle Database Lite 10g provides a means to install, start, and stop an Oracle Database Lite multi user database service. Once started, you can manipulate the local databases from any machine on the local network. The Branch Office infrastructure demonstrates the use of a multi user database service. For more information on the client/server computing architecture, refer to Section15.3 "Architecture" in the Oracle Database Lite Administration and Deployment Guide.

The following section describes how to install, configure, start, debug, create DSNs, access the database, and verify the database connection using msql for a multi user database service.

2.2.1.3 Accessing the Multi User Oracle Database Lite 10g Database Service

This section describes how to install and configure the multi user Oracle Database Lite database service. Topics include:

Installation and Configuration

To install and configure the multiuser Oracle Database Lite 10g database service, perform the following steps.

  1. Ensure that you install the olsv2040.exe in the following directory.

    &fmv112;\Mobile\Sdk\bin

    If not already available, please re-install the MDK to retrieve the component. A sample &fmv113; location is C:\Olite.

  2. To install the service, start the Command Prompt and enter the following command.

    Olsv2040.exe/install

  3. If you have JDK installed on your PC, ensure that the system PATH variable includes the following:

    • <Jdk_home>\bin

    • <Jdk_home>\jre\bin

    • <Jdk_home>\jre\bin\hotspot

    • &fmv114;\Mobile\Sdk\bin

      For example, the <Jdk_home> directory could be C:\jdk1.3.1_05. Ensure that you use JDK 1.3.1 variants only.

  4. If you have JRE installed on your PC, ensure that the system PATH variable includes the following:

    • <jre_home>\bin

    • <jre_home>\bin\hotspot

    • &fmv115;\Mobile\Sdk\bin

      For example, the <Jre_home> directory could be C:\Program Files\JavaSoft\JRE\1.3.1_05. Please use JDK 1.3 variants only.


      Note:

      JRE does not include the Java compiler. Therefore, other attempts to load a Java source into the database such as the CREATE JAVA SOURCE command and the loadjava utility will fail.

  5. Ensure that your system CLASSPATH variable includes the following:

    &fmv116;\Mobile\Classes\Olite40.jar and '.'

  6. You may change the startup type from the Windows NT service console. Highlight the Oracle Database Lite 10g Multi User Service and select 'Properties'. When required, change the startup type to manual. The property also contains startup parameters but has not been tested.

  7. Ensure that the SuggestedSharedAddress and SharedAddress parameters are not present in the polite.ini file.

  8. After changing the above mentioned variables, reboot your PC.

Starting the Service

The Multi User Oracle Database Lite Database Service can be started in many ways. If the service property "Startup Type" is automatic in the polite.ini file, the multiuser service is started every time you reboot the machine.

Using the Command Prompt, you can start the multiuser service by entering any one of the following startup commands.

  • net start "Oracle Lite Multiuser Service"

  • net start "Oracle Lite Multiuser Service"/wdir=<a_working_directory> /port=nnn

If you use '.' in SQL scripts that load Java classes, you must specify a working directory. The port parameter defaults to 100.

To stop the service, use the following command.

net stop "Oracle Lite Multiuser Service"

Debugging the Service

If the service the does not start, you can debug the service using the following method.

  1. Edit the polite.ini file which is available under %WINDIR%\polite.ini to add the entry SvTrace=on under the [ALL_DATABASES] section. The information in this file is not case-sensitive.

  2. Start the Command Prompt and enter the following.

    olsv2040/debug/port/=nnn

    The port parameter in the above command is optional.

  3. Should the service fail, the multiuser service generates a log file named olsv.log in the current working directory. Ensure that the PATH and CLASSPATH variables are accurate.

  4. Correct the cause and retry.

Creating DSNs

To access the database using an ODBC or VB application, you must create the DSN differently enabled from the embedded connection. When you 'Add' a DSN using the ODBC administration tool, choose the Oracle Lite 40 ODBC Driver(Client). In this way, you will create a client DSN. If you are running the service on the same PC where the client application is running, you can leave the Database Host Name, Database Port Number, and Database Host DSN value empty. The remaining values must be included in the same manner as the 'Oracle Lite ODBC Driver' DSN. If you start the service on a port other than 100, you must specify the Database Port Number.

Accessing the Database

To access the database, you need not make any changes to the ODBC or VB application. The DSN automatically routes the request to the client via the ODBC driver olcl2040.dll. For a JDBC application, you must change the URL for the connect string. The URL syntax is documented in the Oracle Database Lite Developer’s Guide for Java. It is similar to the one used while connecting to the database using msql. For more information, refer to Section 3.3, "Connect to Oracle Database Lite," in the Oracle Database Lite Developer’s Guide for Java.

Verifying the Connection Using msql

Using the Command Prompt, you can verify the connection to the multiuser service in the following ways.

msql system/passwd@jdbc:polite@::a-dsn

The above command connects to a-dsn on the local host or port 100.

msql system/passwd@jdbc:polite@:1000:a-dsn

The above command connects to a-dsn on a local host on port 1000.

msql system/passwd@jdbc:polite4@::a-dsn

The above command connects to a-dsn on a local host on port 100 using the Type4 JDBC driver.


Note:

Oracle Database Lite supports Type2 and Type4 JDBC drivers. Type4 is a pure Java JDBC driver that communicates with the service in the Oracle Database Lite network protocol. The Type2 JDBC driver talks to the remote ODBC driver (olcl2040.dll) using a native oljdbc40.dll (JNI Implementation).

For more information on JDBC and Oracle Database Lite, see the Oracle Database Lite Developer’s Guide for Java.

2.2.1.4 ODBC

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 Database Lite supports Level 3 compliant ODBC 2.0 and the ODBC 3.5 drivers through Oracle Database Lite ODBC drivers that interpret the ODBC calls and pass them to Oracle Database Lite.

For more information on ODBC, see the following:

  • Microsoft's ODBC documentation.

  • The Oracle Database Lite ODBC sample application. For its location in this document, see Section 2.9, "Using Oracle Database Lite Samples".

  • The Oracle Database Lite Tools and Utilities Guide.

  • Section 4.4.2.1, "Returning Multiple Rows in ODBC", in the Oracle Database Lite Developer’s Guide for Java.

2.2.1.5 SODA

SODA is a comprehensive and easy interface for Oracle Database Lite development using C++. It provides object-oriented data access using method calls, relational access using SQL and object-relational mapping to bridge the gap between the two.

Object functionality is roughly 3 times faster than ODBC for simple operations. It allows rich datatypes such as arrays and object pointers in addition to standard SQL columns. A programmer now has an option to just store any data structure in the database and not worry about relational design or doing joins.

On the other hand, a C++ developer can also use an interface that is similar to JDBC for executing SQL statements when necessary. The resulting code is much shorter and clearer than its ODBC equivalent. SQL queries can optionally return objects that can be examined and modified directly through the object-oriented layer, without calling any additional SQL statements.

Finally, object-relational mapping allows the application to access relational data as if it was an object hierarchy. This is essential for replicating rich data types or object pointers to the database server.

2.2.2 Mobile Sync Client Module Application Programming Interfaces (APIs)

These APIs allow the application to programmatically control the data synchronization process. The application invokes the functions in the Mobile Sync APIs to initiate the data synchronization process and capture error messages generated by the Mobile Sync APIs. For more information on the Mobile Sync APIs please see Chapter 5, "Native Application Development", Section 5.4, "Mobile Sync Application Programming Interfaces (APIs)".

2.2.3 Oracle Database Lite Load APIs

Using the Oracle Database Lite Load APIs, you can develop applications to load data from an external file into a table in Oracle Database Lite, or to unload (dump) data from a table in Oracle Database Lite to an external file. The details of the APIs and file formats are provided in Appendix B, "Oracle Database Lite Load Application Programming Interfaces (APIs)".

2.2.4 Oracle Database Lite Load Utility (OLLOAD)

The Oracle Database Lite Load Utility enables you to load data from an external file into a table in Oracle Database Lite, or to unload (dump) data from a table in Oracle Database Lite to an external file. For more information on OLLOAD see the Oracle Database Lite Tools and Utilities Guide.

2.2.5 ADO.NET

The Oracle Database Lite ADO.NET Provider implements Microsoft's ADO.NET specification. Developers can use this programming interface to access Oracle Database Lite and trigger Data Synchronization in their .NET based applications. The Oracle Database Lite ADO.NET data provider supports both .NET and Compact .NET frameworks.

2.3 Using the Starter Database

When you install the Mobile Development Kit, an ODBC data source name (DSN) POLITE, and a starter database called POLITE.ODB are created. The location of new database for the DSN POLITE is set to &fmv144;\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 describes sample syntax.) You can either use the default user name or establish user names of your own.


Note:

Review the Oracle Database Lite SQL Reference before using the starter database. This reference describes the Structured Query Language (SQL) used to manage information in Oracle Database Lite.

You can connect to the Oracle Database Lite starter database using an application such as Mobile SQL. Mobile SQL is a command line interface. To connect to the POLITE database, use the following command from the Command Prompt.

C:>msql system/any@jdbc:polite:polite

You can assign SYSTEM a password by entering the following command.

SQL> ALTER USER SYSTEM IDENTIFIED BY <password>

Note:

For more information, see Section 2.5.4, "Changing Passwords".

When connecting to the starter database from an ODBC application, use the default ODBC DSN POLITE.

2.4 Working With Your Database

This section provides an overview of working with your Oracle Database Lite, including creating a database, connecting to a database, creating users, and administering the database.

2.4.1 Creating a New Database

When you create a new database using the POLITE data source name, the new database file is located in the &fmv149;\Monile\Sdk\oldb40 directory. For ease of maintenance, it is recommended that you use one database directory for all 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. For more information, refer the following section.

2.4.2 Creating a Data Source Name with ODBC Administrator

The ODBC Administrator is a tool provided by Microsoft to manage the ODBC.INI file and associated registry entries in Windows 98/NT/2000/XP. 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. For more information on the ODBC Administrator, and for instructions on creating a data source name using the tool, refer to Section 3.7, "ODBC Administrator and the Oracle Database Lite ODBC Driver," in the Oracle Database Lite Tools and Utilities Guide.

2.4.3 Creating a New Database Using the Command-Line Utility

To create a new database from the command line, use the CREATEDB utility. The syntax is:

CREATEDB mydsn mydbname

For example:

CREATEDB polite newdb

where mydsn is the DSN name and mydbname is the new database name.

See the Oracle Database Lite Tools and Utilities Guide for more information on CREATEDB.

2.4.4 Connecting to a New Database

To connect to a new database using Mobile SQL (MSQL), connect as the user named SYSTEM, with the password MANAGER and the data source name. For example:

C:> msql system/manager@jdbc:polite:mydsn

You can replace mydsn with a previously defined ODBC data source name.

2.5 Creating Multiple Users

You can create multiple users in Oracle Database Lite by using the CREATE USER command. A user is not a schema. When you create a user, Oracle Database 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.

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 Database Lite creates a special user called SYSTEM, which has all database privileges and is not assigned a password.

To access data and perform operations in another user's schema, a user must be granted DBA or ADMIN privileges. Alternatively, the user can access data with the user name SYSTEM, as this user name automatically holds DBA and ADMIN privileges.

2.5.1 Pre-defined Roles

Oracle Database 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 Database Lite does not support creating or dropping roles. Following is a list of Oracle Database Lite pre-defined roles:

Table 2-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 schema. 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 database server, Oracle Database Lite does not commit data definition language (DDL) commands until you explicitly issue the COMMIT command.

2.5.2 Creating Users

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 mydbname.opw. Each user can then use their own 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 and the .plg file.

2.5.3 Dropping Users

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 when the user's schema does not contain any objects, 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 Database Lite SQL Reference.

2.5.4 Changing Passwords

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 or DBA role

To change a user's password, issue the following statement:

ALTER USER <user> IDENTIFIED BY <password>

2.5.5 Granting Roles

You can grant the ADMIN or DBA 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.

2.5.6 Granting Privileges

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:

  • ALL

  • INSERT

  • DELETE

  • UPDATE (column_list)

  • SELECT

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.

2.5.7 Revoking Roles

You can revoke user roles by issuing the following statement:

REVOKE <role> FROM <user_list>

2.5.8 Revoking Privileges

You can revoke privileges on database objects from users by issuing the following statement:

REVOKE <privilege_list> ON <table_name> FROM <user_list>

2.5.9 Building Demo Tables

Oracle Database Lite comes with a script called POLDEMO.SQL, which enables you to build the same tables that are in your Oracle Database Lite default starter database (POLITE.ODB).

2.5.10 Populate Your Database Using Mobile SQL

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 the following SQL script from the Mobile SQL prompt.

SQL> @<ORACLE_HOME>\DBS\Poldemo.sql

You can also enter:

SQL> START <filename>

Note:

You do not need to include the .SQL file extension when running the script.

2.5.11 Backing Up a Database

The Oracle Database Lite 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.

2.5.12 Encrypting and Decrypting a Database

Two utilities, ENCRYPDB and DECRYPDB, enable you to encrypt and decrypt Oracle Database Lite databases. These utilities enable you to encrypt an Oracle Database 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 Database Lite uses a version of the Data Encryption Standard (DES) algorithm known as CAST5. A new database created in 10g uses the Advanced Encryption Standard (AES) encryption. Oracle Database Lite will continue to support CAST5 for previous databases.

See the Oracle Database Lite Tools and Utilities Guide for more information about these utilities.

2.6 Oracle Database Lite Transaction Support

When an application connects to Oracle Database Lite, it begins a transaction with the database. There can be a maximum of 64 connections to Oracle Database Lite. Each connection to Oracle Database Lite maintains a separate transaction.

2.6.1 Atomicity

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

2.6.2 Consistency

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 Database Lite does not permit a transaction to commit if it violates a constraint and would therefore violate consistency.

2.6.3 Isolation

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 Database Lite supports the isolation levels for transactions listed in Table 2-2:

Table 2-2 Isolation Levels

Isolation Level Description
Read Committed In Oracle Database Lite, a READ COMMITTED transaction first acquires a temporary database level read lock, materializes the result of the query into a temporary table, and then releases the database lock. During this time, no other transaction can perform a commit operation. No data objects are locked. All other transactions are free to perform any DML operation (except commit), during this time. Since a commit operation locks the database in "intent" exclusive mode, a read committed transaction, while materializing the query result, will block another transaction that is trying to commit or vice versa. A READ COMMITTED transaction gives the highest level of concurrency as it does not acquire any data locks and does not block any other transaction from performing any DML operations. In addition, the re-execution of the same query (SELECT statement) may return more or less number of rows based on other transactions made to the data in the result set of the query.

Note: A SELECT statement containing the FOR UPDATE clause is always executed as if it is running in a REPEATABLE READ isolation level.

In Oracle Database 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 Database 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 concurrency than a READ COMITTED isolation level transaction because the locks are held until the end of the transaction.

A "Phantom" read is possible in this isolation level. this happens when another transaction inserts rows that meet the search criteria of the current query and the transaction re-executes the query.

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.
SingleUser In this isolation level only one connection is permitted to the database. The transaction has no locks and consumes less memory.

Refer to the documentation for ODBC for more information on isolation levels, specifically, for the terms "Dirty Read", "Nonrepeatable Read", and "Phantom", which define transaction isolation levels.

2.6.3.1 Durability

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.

2.6.3.2 Locking

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

2.6.3.3 Changing the Default Isolation Level

In Oracle Database 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

where the value for XX is RC for Read Committed, RR for Repeatable Read, SR for Serializable, or SU for Single User.

Also, you can establish the isolation level of a transaction by using the SQL statement:

SET TRANSACTION ISOLATION LEVEL <ISOLATION_LEVEL>;

where ISOLATION_LEVEL is READ COMMITTED, REPEATABLE READ, SERIALIZABLE, or SINGLE USER.

See Section 2.6.3.4, "Supported Combinations of Isolation Levels and Cursor Types", for more information.

2.6.3.4 Supported Combinations of Isolation Levels and Cursor Types

Table 2-3 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 2-3 Supported Combinations

Isolation Level Forward Only Static Keyset Driven Dynamic
Read Committed S S U U
Repeatable Read S U S S
Serializable S U S S
Single User S S S S

Unsupported combinations generate error messages.

2.6.4 Tuning the Application

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 Database 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 A, "Optimizing SQL Queries ".

2.7 Support for Linguistic Sort

Linguistic sort is a new feature for the "ASCII" version of Oracle Database Lite. It produces culturally acceptable order of strings for a specified language or collation sequence. The "ASCII" version supports several code pages defined by single-byte 8-bit encoding schemes. Each of these code pages is a super set of 7-bit ASCII, and the additional accented characters necessary to support a group of European languages are included in the upper 128 bytes. A new string comparison mechanism is provided that produces strings in a linguistically correct order by mapping each collation element of a string to the corresponding 8-bit value of the supported code page.

2.7.1 Creating Linguistic Sort Enabled Databases

The linguistic sort capability must be enabled when the database is created using the CREATEDB command line utility with the <collation_sequence> enabled.

The behavior of the ORDER_BY clause and the WHERE condition are determined by how the NLS_SORT parameter is implemented. Binary sorting is the default setting, and is used unless the <collation_sequence> parameter is set to use the linguistic sort ordering rules.

Unicode and NLSRT are not supported in the current version of Oracle Database Lite. Therefore, NCHAR data type and customization of collation sequence are not yet available. For more information on how collation sequences are enabled using the file polite.ini, refer the Oracle Database Lite Developers Guide.

2.7.2 How Collation Works

Collation refers to ordering of strings into a culturally acceptable sequence. A collation sequence is a sequence of all collation elements from an alphabet from smallest collation order to the largest. Once a collation sequence is given, orders of all strings from the same alphabet are fixed. As such, the collation sequence encodes the linguistic requirements on collation. A collation element is the smallest sub-string that can be used by the comparison function to determine the order of two strings.

2.7.3 Collation Element Examples

Normally, a collation element is just one character. In binary sorting, only one property, the code value that represents a character, is used. But in linguistic sorting, usually three properties. The primary level of difference is the base character. The secondary level of difference is for diacritical marks on a given base character. The tertiary level of difference is for the case of a given character. Punctuation can function as a fourth level of difference, but comparisons for punctuation occur last and are made at the binary rather than the linguistic level. These are used for each collation element. The following sections contain examples that demonstrate sorting priorities.

2.7.3.1 Sorting Normal Characters

This section lists a set of examples that describe how to sort normal characters.

Example 1

'a' < 'b'. There is a primary difference between them on the character level.

Example 2

'À' > 'a'. This difference occurs on the secondary level. Note that 'À'and 'a' are considered "equal" on the primary level.

Example 3

'À' < 'à' in FRENCH but 'À' > 'à' in GERMAN. This difference on the tertiary level. Note that 'À' and 'à' are considered being "equal" on the primary and secondary level. Also note that the case convention may be different for different language.

Example 4

'às' < 'at'. This is a difference on the primary level. This example shows the role of difference levels: the lower level differences are ignored if there is a primary level difference anywhere in the strings.

Example 5

'+data' < '-data' <'data' <'data-'. If strings are compared and present no difference on the primary, secondary, or tertiary levels, they are compared for punctuation.

2.7.3.2 Reverse Sorting of French Accents

Some languages, particularly French, require words to be ordered on the secondary level according to the last accent difference. This behavior is known as French secondary sorting or French accent ordering.

Example

'côte' < 'coté' in FRENCH but 'coté' < 'côte' in GERMAN. Note that the secondary difference of 'e' and 'é' occurred later than those of 'ô' and 'o'.

2.7.3.3 Sorting Contracting Characters

There are some special cases where two or more characters in a group can function as a single collation element. These types of collation elements are called 'contracting characters' or 'group characters'. In these cases each of these characters properties are assigned appropriate values.

Example

'h' < 'ch' < 'i' in XCZECH. Here 'ch' is assigned a primary property value which differentiates it from 'h' and 'i', such that 'h' < 'ch' < 'i'. Note that 'ch' is treated as a single character.

2.7.3.4 Sorting Expanding Characters

If a letter sorts as if it were a sequence of more than one letter, it is called an 'expanding character'. For example, in German the sharp s (ß) is treated as if it were a string of two characters 'ss' when comparing with other letters.

2.7.3.5 Sorting Numeric Characters

Only sorting of single digit characters from '0' to '9' is currently supported. For the supported European languages a digit character is always sorted as greater than any alphabetic character. For other languages this may be not the same. Other numeric characters such as Roman numeric characters and counting sequences, such as "one", "two", "three", are not supported at this time.

Example

'1' > 'z' in any European language, '1' < 'a' in LATVIAN. Note that this difference occurs on the primary level.

2.8 Creating Snapshot Definitions

The data that your offline applications operate on is stored in Oracle Database Lite as either base tables or snapshots. Base tables can be created using the CREATE TABLE SQL statement. Base table store data that is independent of the server data; changes made to them are never synchronized with the server database.

Snapshots store a subset of server data. Changes made to a snapshot can be synchronized with the server data. However, snapshots cannot be created in Oracle Database Lite by using SQL statements. Snapshots are created by the Mobile Server as part of the application installation. They are created based on the publication items defined on the Mobile server. A publication items contains a parameterized SQL query that defines the subset of server data that needs to be stored in the snapshot.

In most situations, a table or view already exist on the server from which you will create snapshots for your application to use. The following techniques can be used to create publication items on the Mobile Server, which then automatically creates snapshots on the client when you synchronize with the database. The options for creating publication items/snapshot definitions are:

  1. Creating a Snapshot Definition Declaratively - Create publication items using the Packaging Wizard. This is the recommended method.

  2. Creating the Snapshot Definition Programmatically - Create a publication item programmatically using the Consolidator API.

2.8.1 Creating a Snapshot Definition Declaratively

This method uses the Packaging Wizard, a GUI based tool of Oracle Database Lite. 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 server database, if not, create one.

  • Use the Packaging Wizard to define an application and the publication items (snapshot definitions) for it.

  • Use the Packaging Wizard to publish the application to the Mobile Server. This will create the publication items associated with the application.

  • Use the Mobile Manager to create a subscription for a given user.

    Install the application on the development machine.

  • Synchronize the Mobile Client with the Mobile Server to create the client-side snapshots.

Using the Packaging Wizard, as described in the Oracle Database Lite Tools and Utilities Guide provides additional details for this approach.

2.8.2 Creating the Snapshot Definition Programmatically

The second way to create a snapshot definition is to use the Consolidator API to programmatically create the publication items on the Mobile Server. While this method is more involved, requiring the knowledge of the Oracle Database Lite 10g application model, it does provide all the features of the product, including creation of publication items from views, customize code to construct snapshots, which is described in Chapter 3, "Synchronization". The database base tables must exist before the Consolidator API can be invoked. The following steps are required to create a a subscription:

  • Create a publication

  • Create a publication item and add it to the publication

  • Create a user

  • Creating a subscription for the user based on the publication

Creating Publications

Publications are Mobile Server objects that are used to organize other objects such as publication items, indexes on them, platform specific information, etc., required by an application. You can create publications using the Consolidator API. You can call the functions in these APIs from within Java programs as standard function calls.

Creating Publication Items

A publication item is a Mobile Server object that contains the SQL select statement that specifies which data subset of the parent table or view or synonym is replicated on the client. A publication item usually corresponds to a snapshot on the client device. You can create publication items using the Consolidator API. You can call the functions in this API from within Java programs as standard function calls.

Creating Users

Each client is identified by a user ID. For development purposes, a user must be created using the Consolidator API in order to assign data subscriptions to a particular user.

Creating Subscriptions

A subscription is a Mobile Server object that relates a user to a publication. You can create subscriptions using the Consolidator API. Before a subscription can be used to create a client database, every parameter of the publication must be given a value. You can assign a value to each parameter using the SetSubscriptionParameter method of the Consolidator API. You can call the functions in this API from within Java programs as standard function calls. To create publications and subscriptions using Java, see Section 3.4, "The Publish and Subscribe Model and Oracle Database Lite Synchronization", in Chapter 3, "Synchronization".

2.9 Using Oracle Database Lite Samples

The following sections provide instructions on how to use Oracle Database Lite samples.

2.9.1 Overview

After you perform a complete installation of Oracle Database Lite, the samples are available in your &fmv194;\Mobile\Sdk directory. The tools, locations for samples, and descriptions are listed in Table 2-4.

Table 2-4 Sample File Directory

Tool Location of Sample Applications Description
Blob Manager &fmv195;\Mobile\Sdk Demonstrates the use of the Oracle BLOB datatype and Visual Basic's ODBC programming methods and object manipulation. See Section 2.9.2, "BLOB Manager Example Notes" for more information.
Java &fmv196;\Mobile\Sdk Demonstrates programming with JDBC. See the Oracle Database Lite Developer’s Guide for Java for more information.
ODBC &fmv198;\Mobile\Sdk Provides ODBC programs written in C.
Visual Basic &fmv199;\Mobile\Sdk Demonstrates the ease of querying tables in Oracle Database Lite with Visual Basic tools. See Section 2.9.3, "Running the Visual Basic Sample Application" for more information.


Note:

Most examples use the data source name (DSN) POLITE. If you need to drop and recreate, use the REMOVEDB and CREATEDB utilities.

2.9.2 BLOB Manager Example Notes

To install the BLOB Manager example, open the \SETUP folder in &fmv201;\Lite\Sdk and run setup.exe. After you complete the installation, click the 'Start' button and select 'BLOB Manager' from the 'Programs' menu.

To uninstall the example, click the 'Start' button, select 'Settings', and then 'Control Panel'. Select 'Add/Remove'. Select 'BLOB Manager' and click the 'Add/Remove' button.

You need at least Version 3.51.2723.0 of MSJET35.dll to run the example.

Run the 'setup.exe' and 'BLOB Manager' from the 'Programs' menu as stated above before you open the Visual Basic project file and run it with Visual Basic. Running the program from the Programs menu will prepare the table in the database for you automatically.


Note:

BLOB Manager is for demonstration purposes. It assumes that you have installed the default database with the default POLITE ODBC DSN. If this is not the case, you can create the POLITE DSN using the ODBC Administrator. Also, you must verify that SYSTEM is a valid user for the database.

2.9.3 Running the Visual Basic Sample Application

This example (which uses Visual Basic 5.0 or higher) demonstrates how to develop a Visual Basic application with Oracle Database Lite. It uses the ODBC DSN, POLITE. To use the AddNew, Update, and Delete macros you need a unique EMPNO column of the EMP table. This is the default condition when you connect to the default database.

These instructions for installing and running the Visual Basic sample application assume that you have already installed Oracle Database Lite and Visual Basic (version 5.0 or higher).


Note:

If you have not installed Visual Basic and the ODBC drivers, you need to install them before you begin.

2.9.3.1 Open Visual Basic

Double-click the Visual Basic icon in your Visual Basic program group to open Visual Basic.

2.9.3.2 View the Sample Application Tables and Data

This step uses the Visual Data Manager, which is available only with Visual Basic 5.0. If you are using an earlier version of Visual Basic, skip to Step 3.

  1. From the Add-Ins menu, select Visual Data Manager. In the VisData window, select Open Database from the File menu and select ODBC.

  2. In the ODBC Logon dialog, enter values as described in Table 2-5.

    Table 2-5 ODBC Logon Dialog Description

    Field Name Value
    DSN POLITE
    UID SYSTEM
    PW Enter at least one character
    Database POLITE

  3. Click OK. The Oracle Database Lite tables are displayed in the Database window. You can highlight a table and right click to open the table and display the records.

2.9.3.3 Open the Sample Application

  1. To open the sample application, select Open Project from the File menu. In the dialog box, navigate to your &fmv205;\Mobile\Sdk\Examples\VB directory. Select update.mak, and click Open.


    Note:

    If you do not see the file update.mak listed, select Files of type *.* to show all file types. You should now see the file in the list.

  2. From the Run menu, select Start to open the sample application and display the EMP table.

2.9.3.4 View and Manipulate the Data in the EMP Table

  1. To view data in the EMP table:

    • Click Show to show the EMP table data.

    • Click Next to show the next record.

    • Click Previous to show the previous record.

  2. To manipulate data in the EMP table, use the Add, Update, and Delete features.

2.9.4 ODBC Examples

These examples are located in &fmv206;\Mobile\Sdk.

These examples must be compiled using a C++ complier. To build them, open a console, switch to the &fmv207;\Mobile\Sdk directory and type "nmake".

There are 5 odbc examples namely, odbctbl, odbcview, odbcfunc, odbctype, and long. You only need the POLITE data source name (DSN) to run these examples. The POLITE DSN is automatically created during the Mobile Development Kit installation.

To run the examples, execute run.bat in the &fmv208;\Mobile\Sdk directory. The first four examples have their own output windows showing the log of what is done. Closing the current example window causes the next example to be run. The output displayed in the example windows is also printed in the log files, odbctbl.log, odbcview.log, odbcfunc.log, odbctype.log. The long example output is collected in the output file long.out.

2.9.4.1 What the Examples Do

The following sections describe the functionality of the samples found in &fmv209;\Mobile\Sdk.

2.9.4.1.1 odbctbl

This is an ODBC SQL Table example. It shows you how to manipulate tables using ODBC API. It creates table EMP with columns ID, NAME, START_DATE, SALARY, populates this table with the data, does an update on the salary column, selectively deletes some rows, then selects from the resulting table and shows the results of the fetch operation. At the end, the EMP table is dropped.

2.9.4.1.2 odbcview

This is an ODBC SQL View example. It shows you how to manipulate views using the ODBC API. It creates table EMP (as above) and view HIGH_PAID_EMP selecting the full name (using the CONCAT scalar function), HIRE_DATE and SALARY from the EMP table. Then EMP is populated. After that a select is performed from the HIGH_PAID_EMP view is issued to see the populated data. Then the salary column of EMP is updated, some rows are deleted from EMP, and again the select from HIGH_PAID_EMP is issued to see how those changes are reflected in the view. Finally, the view and the table are dropped.

2.9.4.1.3 odbcfunc

This is an ODBC SQL Scalar Functions example. It shows you how to use scalar functions in the ODBC API. It creates table EMP, populates it with the data, then does select ID, FULL_NAME from EMP, where to calculate full name it uses odbc scalar function CONCAT with last and first names as arguments. Then it updates the table converting last name to uppercase and first name to lowercase for IDs < 3 using odbc scalar functions UCASE and LCASE. The new data is selected and displayed again. At the end the table EMP is dropped.

2.9.4.1.4 odbctype

This is ODBC SQL Types Example. It shows you how to manipulate different data types using ODBC API. This test just creates table EMP, populates it with data, selects all the rows and displays the result, but the columns are bound differently from the previous tests. First, it calls SQLNumResultCols to find the number of result columns. Then, for each result column, it calls SQLDescribeCol to get all the information about that column, such as column name, column name length, column type, column length, column scale, etc. This information is then used to bind the column. This shows how you can get the type information from the database using the ODBC API.

2.9.4.1.5 long

This example exercises the basic read/write functions of SQL LONG VARCHAR. It first drops, then creates the table LONG_DATA with one LONG VARCHAR column and inserts the data into the table. For each row the data is put in frames, where each frame represents a buffer of long varchar data (of length 4096). The example uses SQLParamData and SQLPutData to send each frame to populate the row. Then the select from the table is issued to fetch the rows and read long varchar data from the table. For each row, the data is also read in frames, using SQLGetData until SQL_NO_DATA_FOUND is returned. These actions are logged into the file "long.out".

2.10 Limitations

Currently, the Oracle Database Lite engine has a limitation of not being able to sort any row that exceeds 4040 bytes in length. The selected columns exceed 4040 bytes and the database engine issues this error. Therefore, queries that use the UNION operation that are implemented by sorting the intermediate results from the two select clauses in the query cannot be fixed.

2.11 Tracing

The Oracle Database Lite 10g database is used in conjunction with other products such as Oracle forms, SQLJ, Web Servers, and OC4J. When an unexpected error is reported by the software system, users need to identify the location and cause of the error. Errors can be caused due to problems in code written by users, other Oracle tools such as forms, SQLJ, OC4J or in the Oracle Database Lite 10g database component. Errors also occur in simple environments where a user application talks directly to the Oracle Database Lite 10g database through JDBC or ODBC drivers. At first glance, it may not be obvious which component is at fault, whether it is the user application, JDBC or ODBC drivers, or the core database runtime system.

If the optimizer spends too much time evaluating alternative plans or collecting index statistics, a query may take a long time for compilation. If the execution plan selected by the optimizer is not optimal, the query may also take a long time during execution. Based on these criteria, the tracing facility provides the compilation time and the execution plan.

This section describes how to set the Tracing feature. Topics include:

2.11.1 Enabling Trace Output

To enable Trace output, perform the following.

Include the following line in the polite.ini configuration file.

OLITE_SQL_TRACE= yes

The parameter name and the value string "yes" are not case sensitive. For example, the following line also enables trace output.

OLITE_SQL_trace= YeS


Note:

Any value other than "yes" disables the tracing feature. The parameter value is checked once during database startup. Hence, users must set this value before connecting to the database.

2.11.2 Basic Functions

The Tracing facility can be enabled through a configuration parameter in the polite.ini file. On enabling the trace feature, the information generated is dumped to a trace file named oldb_trc.txt in the current working directory of the database process. If the file exists, the trace output is added to it. If it does not exist, a new file is created automatically. If the database fails to create or update the file, the tracing feature does not take place. The following information is dumped in a trace file.

  1. Each time a SQL statement is prepared, its text is dumped into the trace file. The text begins with a header titled Statement Text.

  2. After the SQL statement is compiled, the compilation time is printed in one line titled Compilation Time.

  3. If there are no errors, the execution plan is printed when available. Only statements that contain a WHERE clause generate an execution plan. The printed plan contains the execution order of tables for each sub-select.

  4. If a SQL statement contains markers, then the bind value is printed for every line.

  5. Each time a temporary table is created, its name is dumped into the trace file. The text begins with a header titled Temporary Table Created.

  6. Each time a table is accessed, the following information is dumped into the trace file:

    1. Table Name: The name of the table been accessed is dumped into the trace file. The text begins with a header titled Table Name.

    2. Access Method: The access method used by the database is dumped into the trace file. The text begins with a header titled Access Method.

  7. Each time a temporary table is sorted, its name and sorting time are dumped into the trace file. The text begins with a header titled Temporary Table Sorted followed by the sorted temporary table name and the time it takes (in milliseconds) to sort the table.

  8. If the SQL statement is a SELECT statement, the time spent on fetching the first row is dumped into the trace file. The text begins with a header titled First Fetch Time.

  9. The thread ID is dumped into the trace file in front of some of the dumped information. The Tid is the title used to represent the Thread ID.

2.11.3 SQL Tracing

SQL trace output is dumped to a trace file named oldb_trc.txt in the current working directory of the database process. For a database service on Windows, Windows NT or the Oracle Database Lite daemon for a Linux platform, the current working directory is specified by the wdir parameter during startup of the database service or daemon. To implement the Tracing feature, the database process must contain permissions to create the trace file in the current working directory. The Trace output is always included in the trace file. If the trace file does not exist, it is created automatically.

The SQL trace facility dumps the following information to the trace file.

  1. The thread ID.

  2. SQL statements after compilation.

  3. Compilation time including optimization.

  4. Value of marker as it exists just before execution of the SQL statement.

  5. Execution plan as described in the EXPLAIN PLAN statement in the Oracle Database Lite SQL Reference.

  6. The name of the temporary table created.

  7. The name of the table being accessed and the access method used.

  8. The name of the temporary table been sorted and the sorting time.

  9. The time spent on fetching the first row if the SQL statement is a SELECT statement.

2.11.3.1 The Tid Output

The thread ID of the running operation is printed in front of some of the dumped information. The thread is displayed in the following format:

Tid: <thread id>

2.11.3.2 SQL Statement Output

Each SQL statement is preceded by the prefix Statement Text. The SQL statement itself is output without any formatting. If a SQL statement contains a new line character, it is also included in the SQL statement output.

2.11.3.3 Compilation Time Output

After the SQL statement is compiled, the compilation time is printed in one line. This line begins with the title Compilation Time.

2.11.3.4 Bind Values Output

The value of markers or bind variables is one per line. This line is displayed in the following format.

Marker [<number>]: <Value>

Where, <number> is the number of the marker and <value> denotes the value of the marker just before execution.

2.11.3.5 Explain Plan Output

This output is printed in the same format as printed by the EXPLAIN PLAN SQL statement.

2.11.3.6 Temporary Table Created Output

The name of the temporary table created is printed if a temporary table is created by the database system.

2.11.3.7 Table Name Output

The name of the table that is currently being accessed and the method used to access the table are printed in the following formats.

  • If the table is accessed sequentially, the format is:

    Table Name: <table name>

    Access Method: Sequential

    Where <table name> is the name of the table being accessed.

  • If indices are used, the format is:

    Table Name: <table name>

    Access Method: Term[<number>], Index No: <index number>, IndexName: <index name>

    <table name> is the name of the table being accessed.

    Term[<number>] is the internal representation of the conjunct search conditions in the WHERE clause.

    <index number> is the index number. Each index has an unique number in the database.

    <index name> is the name of the index if any.

2.11.3.8 Temporary Table Sorted Output

The name of the temporary table sorted and the time it takes to sort the table.

2.11.3.9 First Fetch Time Output

The time the database takes to retrieve the first row when performing a SELECT operation. The "First Fetch Time" is the time to retrieve the first row in the result set.