Oracle® Database Lite Developer's Guide
10g (10.0.0) Part No. B13788-01 |
|
![]() Previous |
![]() Next |
This chapter presents the Oracle Database Lite Relational Database Management System (RDBMS). It discusses the following topics:
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.
This section provides an overview of the development interface. Topics include:
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.
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.
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.
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.
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
.
To install the service, start the Command Prompt and enter the following command.
Olsv2040.exe/install
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.
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 theCREATE JAVA SOURCE command and the loadjava utility will fail.
|
Ensure that your system CLASSPATH
variable includes the following:
&fmv116;\Mobile\Classes\Olite40.jar and '.'
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.
Ensure that the SuggestedSharedAddress
and SharedAddress
parameters are not present in the polite.ini
file.
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.
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.
Start the Command Prompt and enter the following.
olsv2040/debug/port/=nnn
The port parameter in the above command is optional.
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.
Correct the cause and retry.
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.
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.
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.
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.
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)".
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)".
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.
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.
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>
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 Database Lite, 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 &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 userSYSTEM , which has a NULL password.
|
You can create a new data source name using the ODBC Administrator. For more information, refer the following section.
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.
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
.
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.
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.
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:
|
General Note: Unlike the Oracle database server, Oracle Database Lite does not commit data definition language (DDL) commands until you explicitly issue theCOMMIT 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 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.
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.
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>
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.
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.
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>
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
).
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. |
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.
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.
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.
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.
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.
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 In Oracle Database Lite, a |
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 |
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.
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 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.
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.
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.
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 ".
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.
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.
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.
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.
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.
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'.
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.
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.
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.
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:
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 API.
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.
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
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".
The following sections provide instructions on how to use Oracle Database Lite samples.
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 theREMOVEDB and CREATEDB utilities.
|
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 defaultPOLITE 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.
|
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. |
Double-click the Visual Basic icon in your Visual Basic program group to open Visual Basic.
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.
From the Add-Ins menu, select Visual Data Manager. In the VisData window, select Open Database from the File menu and select ODBC.
In the ODBC Logon dialog, enter values as described in Table 2-5.
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.
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. |
From the Run menu, select Start to open the sample application and display the EMP table.
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.
The following sections describe the functionality of the samples found in &fmv209;\Mobile\Sdk
.
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.
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.
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.
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.
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".
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.
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:
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. |
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.
Each time a SQL statement is prepared, its text is dumped into the trace file. The text begins with a header titled Statement Text.
After the SQL statement is compiled, the compilation time is printed in one line titled Compilation Time.
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.
If a SQL statement contains markers, then the bind value is printed for every line.
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.
Each time a table is accessed, the following information is dumped into the trace file:
Table Name: The name of the table been accessed is dumped into the trace file. The text begins with a header titled Table Name.
Access Method: The access method used by the database is dumped into the trace file. The text begins with a header titled Access Method.
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.
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.
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.
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.
The thread ID.
SQL statements after compilation.
Compilation time including optimization.
Value of marker as it exists just before execution of the SQL statement.
Execution plan as described in the EXPLAIN PLAN
statement in the Oracle Database Lite SQL Reference.
The name of the temporary table created.
The name of the table being accessed and the access method used.
The name of the temporary table been sorted and the sorting time.
The time spent on fetching the first row if the SQL statement is a SELECT statement.
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>
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.
After the SQL statement is compiled, the compilation time is printed in one line. This line begins with the title Compilation Time.
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.
This output is printed in the same format as printed by the EXPLAIN PLAN SQL
statement.
The name of the temporary table created is printed if a temporary table is created by the database system.
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.