|
Oracle® Database Lite Developer's Guide
10g (10.2.0) Part No. B15920-01 |
|
 Previous |
 Next |
|
Oracle® Database Lite Developer's Guide
10g (10.2.0) Part No. B15920-01 |
|
|
Previous |
Next |
This chapter presents the Oracle Database Lite Relational Database Management System (RDBMS). It discusses the following topics:
Section 2.2, "Creating and Managing the Database for a Mobile Client"
Section 2.3, "Creating and Managing the Database in an Embedded Application"
Section 2.6, "Move Your Client Data Between an Oracle Lite Database and an External File"
Section 2.13, "Limitations of the Oracle Database Lite Engine"
The Oracle Lite database is compliant to the SQL92 standard and compatible to Oracle databases. In addition, it is compliant to the ACID requirements for transaction support. Because it is a small database specifically designed for a client device, it has a small footprint and easy to administer. The Oracle Lite database can be installed on the following platforms: Linux, UNIX, Windows platforms—such as Win32—WinCE, and Palm.
You can use the Oracle Lite database either with the Mobile client and use synchronization to replicate data between the client and the Oracle database or you can embed the Oracle Lite database within an independent application of your own design. Either way, you use a small database that contains the client data—known as the Oracle Lite database—that is stored in a file with an ODB extension. The Oracle Lite database exists solely to store and retrieve the user data specific to this device. It is not a replication of the entire Oracle database.
Oracle Database Lite creates all ODB files with an automatic name and assigns a data source name (DSN). The DSN is used to connect to the database using ODBC, JDBC or ADO.NET APIs. In order to make the connection, you must know the DSN name for your ODB file. When you install the Mobile Development Kit, a default database is installed with database name of polite.odb and DSN name of polite. However, when you synchronize, an Oracle Lite database is created for each publication (under a directory named after each user). The DSN for these ODB files is a combination of the user and publication names.
The Oracle Lite database is an RDBMS that supports ODBC, JDBC, ADO.NET and SODA interfaces. SODA is an Oracle Database Lite specific C++ object API created to access the Oracle Lite database. SODA provides access to SQL as well as object-oriented functionality. See Section 2.4, "Data Access APIs" for more information on each language.
When you use the Mobile client and Mobile Server to replicate data between the back-end Oracle database and your Mobile device, a small Oracle Lite database (ODB file) is created on your Mobile device to contain the data—that is stored in tables known as snapshots. The snapshot tables are used to track the modifications that the client makes on the data, which is then replicated during the synchronization process to the back-end database. All of this activity is transparent to the client. Your application queries and modifies data using SQL as if interacting with any Oracle database.
The Oracle Lite database for the Mobile client is automatically created on the first synchronization request. In addition, the data is replicated and updated with the data on the Oracle database automatically for you. See Section 3.2, "What is The Process for Setting Up a User For Synchronization?" for techniques that 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.
When you want to create your own application that does not use the formal Mobile client model and is installed on its own, with an embedded Oracle Lite database as the storage vehicle for your application, perform the following:
Section 2.3.2, "Creating the Default Starter Oracle Lite Database for an Embedded Application"
Section 2.3.3, "Creating a Unique Oracle Lite Database for an Embedded Application"
Section 2.3.4, "Creating Users for Your Embedded Oracle Lite Database"
Section 2.3.5, "Packaging Your Embedded Application With the Oracle Database Lite Runtime"
In order to create the Oracle Lite database and embed it into your application, you must include not only the Oracle Lite database, but certain DLLs in your application. In order to develop applications, you must install the Mobile Development Kit. See the Oracle Database Lite Getting Started Guide for full details.
If you want to create an application that uses a small file-based database, you can develop your application around an Oracle Lite database. When you installed the Mobile Development Kit, the following was created automatically for you:
An ODBC data source name (DSN) POLITE and a starter database called POLITE.ODB are created. The location of the new database for the DSN POLITE is <ORACLE_HOME>\Mobile\Sdk\oldb40.
A default user named SYSTEM is created when the starter database is created. This user contains all database privileges and has a password of MANAGER.
You can develop your application to store and retrieve any information in the database using any of the APIs listed in Section 2.4, "Data Access APIs".
If you do not want to use the default Oracle Lite database, described in Section 2.3.2, "Creating the Default Starter Oracle Lite Database for an Embedded Application", then you can create your own database file. First, create a data source name (DSN) for the database and then create the database itself, as described in the following sections:
Section 2.3.3.1, "Creating a Data Source Name with ODBC Administrator"
Section 2.3.3.2, "Creating a New Oracle Lite Database for the Embedded Application"
Section 2.3.3.3, "Connecting to Your New Oracle Lite Database"
The data source name (DSN) points to the physical location of the ODB file. The DSN is used when creating the Oracle Lite database (ODB) file. How you create the DSN is platform-dependent, as described in the following sections:
Create the DSN on a Windows system through the Microsoft ODBC Administrator, which is a tool that manages the ODBC.INI file and associated registry entries in Windows 98/NT/2000/XP. Within this tool, add the data source name for your ODB file and specify the database file you want to dedicate as the default for the data source name. For more information on DSN properties, see Table 2-1 and Table 2-2.
|
Note: The name of the ODB file is used in the next step: Section 2.3.3.2, "Creating a New Oracle Lite Database for the Embedded Application". For more information on the ODBC Administrator, and for instructions on creating a data source name using the tool, refer to Appendix B, "ODBC Administrator and the Oracle Database Lite ODBC Driver". |
In order to create a DSN on a LINUX platform, add the DSN in the ODBC.INI file, which is located in the ORACLE_HOME\olite\bin directory. In this file, add the DSN in its own section, where the section name is the DSN name. For example, the following ODBC.INI example contains two DSN configurations:
The Polite DSN configuration is for a single Oracle Lite database installed on the Mobile client.
The Politecl DSN configuration describes a multi-user service DSN, as shown with the ServerHostName and ServerPortNumber elements. This service is described further in Section 2.5, "Oracle Database Lite Multi-User Service".
[Polite] Description=Oracle Lite 40 Data Source DataDirectory=/home/olite Database=polite IsolationLevel=Read Committed Autocommit=Off CursorType=Forward Only [Politecl] Description=Oracle Lite 40 Data Source DataDirectory=/home/olite ServerHostName=localhost ServerPortNumber=10000 Database=polite IsolationLevel=Read Committed Autocommit=Off CursorType=Static
The parameters that you can use are listed in Table 2-1:
Table 2-1 POLITE.INI DSN Parameters
| DSN Parameter | Description |
|---|---|
Description
|
An optional description for the data source. Use only for Windows environment. |
Data Directory
|
The path to the data directory where the database resides. This is an existing path. |
Database
|
Oracle Database Lite database name to be created. Do not include the .ODB extension.
|
Default Isolation Level
|
Determines the degree to which operations in different transactions are visible to each other. For more information on the supported isolation levels, refer to Section 15.2, "What Are the Transaction Isolation Levels?" for more information. The default level is Read Committed. Other options are Repeatable Read, Single User, and Serializable.
|
Autocommit
|
Commits every database update operation in a transaction when that operation is performed. Auto-commit values are Off and On. The default value is Off.
Note: In the Microsoft ODBC SDK, the ODBC driver defaults to auto-commit mode. However, the default for Oracle Database Lite is manual-commit mode. In this environment, if you execute SQLEndTrans / SQLTransact call with SQL_COMMIT option using the ODBC driver, you receive a SQL_SUCCESS, because ODBC believes that auto-commit is on. However, no commit actually occurs, because ODBC transfers the transaction to Oracle Database Lite, whose default is manual-commit. You must configure the Microsoft ODBC Driver Manager to transfer control of the SQLEndTrans / SQLTransact API call to Oracle Database Lite by explicitly setting autocommit to OFF in ODBC. When you do this, ODBC does not try to autocommit, but gives control of the transaction to Oracle Database Lite. To set auto-commit to off, execute either the |
Cursor Type
|
See Section 15.4, "Supported Combinations of Isolation Levels and Cursor Types" for details on the restrictions when combining cursor types and isolation levels. |
If your DSN connects to a multi-user service—see Section 2.5, "Oracle Database Lite Multi-User Service"—then the DSN entries have the following additional parameters:
Table 2-2 DSN Configuration Parameters for Multi-User Service on LINUX
| Parameter | Description |
|---|---|
ServerHostName
|
Provide the server machine hostname or IP address where the database service is running. |
ServerPortNumber
|
The port number where the database service is listening for incoming requests. |
ServerDSN
|
The server-side DSN. Thus, the client DSN name on the client machine can be different from the DSN on the server mahcine. This is required only if the client and server machines are not the same and the Database Directory and Database parameters are not required.
|
To create a new Oracle Lite database, use the CREATEDB command-line utility providing the DSN name, the database name, and the system user password, as follows:
CREATEDB myDSN myDBname sysPwd
For example, if the name of the DSN is POLITE, the ODB name is myDB, and the system user password is MANAGER:
CREATEDB polite mydb manager
See Section B.2, "CREATEDB" for more information.
The new database file is located in the <ORACLE_HOME>\Mobile\Sdk\oldb40 directory. For ease of maintenance, it is recommended that you use one database directory for all of your Oracle Lite databases.
Connect to the file-based Oracle Lite starter database using your application or mSQL, which is a command line interface. See Section B.1, "The mSQL Tool" for full details.
When connecting to the starter database from an ODBC application, use the default ODBC DSN POLITE. To connect to the POLITE database using mSQL with SYSTEM user, MANAGER password, and the mydsn data source name, perform the following:
C:>msql system/manager@jdbc:polite:mydsn
|
Note: On WinCE, the mSQL utility is a GUI installed on your platform. |
You can replace mydsn with a previously defined ODBC data source name. To connect to the default DSN POLITE, the mSQL statement would be as follows:
C:>msql system/manager@jdbc:polite:polite
|
Note: Review the Oracle Database Lite SQL Reference before using the starter database to understand the SQL used to manage information in Oracle Database Lite. |
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.
To access data and perform operations in another user schema, a user must grant you DBA or ADMIN privileges. Alternatively, the user can access data with the user name SYSTEM, as this username automatically holds DBA and ADMIN privileges.
You can create multiple users in your Oracle Lite database for your embedded application with the CREATE USER command. See the Oracle Database Lite SQL Reference for information on how to manage your user through SQL commands. However, if you are using Branch Office, then create the users with the Branch Office Admin Tool, as described in "Managing Branch Office Users" section in the Oracle Database Lite Administration and Deployment Guide.
While most information you need to understand about SQL and your Oracle Lite database can be gathered from the Oracle Database manuals and the Oracle Database Lite SQL Reference, the following sections help you understand concepts related specifically to the Oracle Lite database.
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-3 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:
|
DBA
|
Enables the user to issue the following DDL statements which otherwise can only be issued by SYSTEM:
All ADMIN privileges, |
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.
|
Oracle Database Lite comes with a script called POLDEMO.SQL, which enables you to build the same tables that are in your Oracle Lite default starter database (POLITE.ODB).
You can use SQL scripts to create tables and schema, and to insert data into tables. A SQL script is a text file, generally with a .SQL extension, that contains SQL commands. You can run the following SQL script from the Mobile SQL prompt.
SQL> @<ORACLE_HOME>Mobile\DBS\Poldemo.sql
You can also enter:
SQL> START Poldemo.sql
|
Note: You do not need to include the.SQL file extension when running the script.
|
In order to use the Oracle Lite database and embed it into your application, you must include not only the Oracle Lite database, but certain DLLs in your application. Perform the following:
Copy the following files from the Mobile Development Kit library, which is located in ORACLE_HOME/Mobile/Sdk, into the directory in your PATH where your application DLLs are located.
olite40.msb: Oracle Database Lite message file
oljdbc40.dll: JDBC JNI library
olobj40.dll: Oracle Database Lite object kernel
olod2040.dll: Oracle Database Lite ODBC driver
olsql40.dll: Oracle Database Lite SQL runtime library
olstddll.dll: Oracle Lite Common library
If you are using the Multi-User Service, copy olsv2040.exe and olsvmsg.dll into your PATH where your application DLLs are located.
To use any Java program with Oracle Database Lite, make sure that the olite40.jar file, which is installed in OLITE_HOME/bin, is in the application CLASSPATH. If the Java program uses the multi-user service, also place this JAR file in the SYSTEM CLASSPATH. This JAR file contains the JDBC driver for Oracle Database Lite. Your environment must provide a Java Runtime Environment from Sun, version JDK 1.4.2 version or higher.
If you want to support the mSQL command-line tool for querying and managing the Oracle Lite database, then you must place the following files in the PATH:
msql.dll
msql.exe
msql.jar
Manage ODBC for creating the DSN and registering the ODBC driver. On Linux, modify the ODBC.INI file. On Windows, perform the following:
To use Microsoft ODBC for the ODBC environment—including DSN creation support—or to create and manage DSN names programmatically, place the olad2040.dll in the PATH. This DLL provides a plug-in to programmatically access the ODBC administration tool—odbcad32—that is used to create DSNs.
Register the ODBC driver for the product in the Windows Registry, as follows:
KEY:HKEY_LOCAL_MACHINE\Software\ODBC\ODBCINST.INI\Oracle Lite 40 ODBC Driver VALUE:32Bit = 1 VALUE:ApiLevel = 0 VALUE:ConnectFunctions = YYN VALUE:Driver = <path to olod2040.dll> VALUE:DriverODBCVer = 02.00 VALUE:SQLLevel = 0 VALUE:Setup = <path_to_olad2040.dll> KEY:HKEY_LOCAL_MACHINE\Software\ODBC\ODBCINST.INI\ODBC DRIVERS VALUE:Oracle Lite 40 ODBC Driver = Installed
Configure the POLITE.INI file and place it in the system Windows directory, such as c:\winnt, as follows:
[All Databases] NLS_LANGUAGE=ENGLISH NLS_LOCALE=ENGLISH DB_CHAR_ENCODING=Native DATA_DIRECTORY=<default_directory_to_create_database_files>
|
Note: See thePOLITE.INI Appendix in the Oracle Database Lite Administration and Deployment Guide for more information on how to configure the POLITE.INI file.
|
To access the data within the ODB file from your application through one of the following APIs:
For relational database development:
JDBC—See Section 2.4.1, "JDBC" for more information.
ODBC—See Section 2.4.2, "ODBC" for more information.
ADO.NET—See Section 2.4.3, "ADO.NET" for more information.
Any interface that supports ODBC or JDBC data sources, such as ADO.Net, can also be used to access Oracle Database Lite. The interfaces can be used either independently or in combination.
For object and relational database development:
Simple Object Data Access (SODA)—See Section 2.4.4, "SODA" for more information.
The following sections describe the different development interfaces that you can use to store and retrieve data from the file-based Oracle Lite database:
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 core, provides an object interface to relational databases. Oracle Database Lite conforms to the JDBC 1.2 API specification standard.
Oracle Database Lite supports JDBC through an Oracle Database Lite Type 2 and Type 4 JDBC drivers that interpret the JDBC calls and pass them to Oracle Database Lite. The Type 4 JDBC driver can only be used for the multi-user service only, as described in Section 2.5, "Oracle Database Lite Multi-User Service".
For Mobile clients, all JDBC drivers are provided for you to use within the Oracle Database Lite binaries. However, for embedded applications, you must include the correct binaries when you package the application, as described in Section 2.3.5, "Packaging Your Embedded Application With the Oracle Database Lite Runtime".
See Chapter 10, "JDBC Programming" for more information on using JDBC.
The Microsoft Open Database Connectivity (ODBC) interface is a procedural, call-level interface for accessing any SQL database, and is supported by most database vendors. It specifies a set of functions that allow applications to connect to the database, 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.
For more information on ODBC, see the following:
Microsoft ODBC documentation.
The Oracle Database Lite ODBC sample application, as described in Section 2.12, "Using Oracle Database Lite Samples".
The Oracle Database Lite ADO.NET Provider implements the Microsoft ADO.NET specification. Use this programming interface to access Oracle Database Lite and trigger data synchronization in .NET applications. The Oracle Database Lite ADO.NET data provider supports both .NET and Compact .NET frameworks.
See Chapter 14, "Oracle Database Lite ADO.NET Provider" for a full description.
SODA is an 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 about three times faster than ODBC for simple operations. It allows rich datatypes—such as arrays, object pointers, and standard SQL columns. A programmer can store any data structure in the database and not worry about relational design or performing joins.
A C++ developer can use the interface for executing SQL statements. The resulting code is shorter and clearer than ODBC code. SQL queries can return objects, which can be examined and modified directly through the object-oriented layer without calling any additional SQL statements.
Finally, object-relational mapping enables 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 Oracle database server.
For more information, see Chapter 12, "Using Simple Object Data Access (SODA) for PalmOS and PocketPC Platforms".
If you want to have multiple users accessing a single entry point for the Oracle Lite database, then use one of the following multi-user services:
For multiple clients accessing a single Mobile client that synchronizes with a Mobile Server, use the Branch Office service. See Chapter 10, "Manage Your Branch Office" for full details.
For multiple clients executing an application that accesses the same database, set up a listener to receive requests from each of these clients. See Section 2.5.1, "Accessing the Multi-User Oracle Database Lite Database Service" for full details.
When you are using the embedded application approach, there may be a time when you want to protect all of your data on a centralized machine and only allowing the clients to access the information remotely. Figure 2-1 demonstrates centralizing your Oracle Lite databases (ODB files) by installing them on a Windows or Linux host machine. The Oracle Database Lite Multi-User Service facilitates the communication between the remote application clients by starting the multi-user service, which opens the designated ports, and then translates the DSNs to the appropriate database.
The Multi-User Service enables you to use up to sixty-four concurrent client connections, each of which can connect to up to sixty-four ODB database files on the remote machine. All clients and server must install the same binary with the same NLS. The server machine with the Multi-User Service and each of the clients can be installed on either Windows or Linux platforms.
When you implement the remote data access for your embedded application, the flow of events is as follows:
Remote client sends the connect request to the multi-user service.
The client connection can use the ODBC 2.0 client driver, the JDBC Type 2 MU driver, or the JDBC Type 4 driver. In addition, the client provides the following in the connection string: remote host and port where the multi-user service is listening for incoming calls and the DSN of the Oracle Lite database (ODB file) where the data is stored.
Multi-user service receives incoming call with the DSN from the remote client.
The multi-user service parses the DSN name and completes the request by connecting to the Oracle Lite database that maps to the DSN name.
The following sections describe how to install and configure the multi-user Oracle Database Lite database service:
To install and configure the Oracle Database Lite multi-user service, perform the following steps:
Ensure that you install the olsv2040.exe in the following directory.
<OLITE_HOME>\Mobile\Sdk\bin
|
Note: This directory must also be included in your systemPATH.
|
If not already available, re-install the MDK to retrieve the component. A sample <OLITE_HOME> location is C:\Olite.
To install the service, start the Command Prompt and enter the following command.
olsv2040.exe /install [/account=AccountName] [/wdir=WorkingDirectory] [/port=ServerPort]
where the optional parameters can be as follows:
AccountName: Provide either the DomainName\UserName or .\UserName.
WorkingDirectory: If you use '.' in SQL scripts that load Java classes, you must specify a working directory.
ServerPort: Defaults to 1531.
If you are using Java Stored Procedures, then perform the following to set up the environment for Java Stored Procedures:
If you have JDK, which should be a minimum of version 1.4.2, 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
For example, the <JDK_HOME> directory could be C:\jdk1.4.2.
If you have JRE, which should be a minimum of version 1.4.2, installed on your PC, ensure that the system PATH variable includes the following:
<JRE_HOME>\bin <JRE_HOME>\bin\hotspot
For example, the <JRE_HOME> directory could be C:\Program Files\JavaSoft\JRE\1.4.2
|
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:
<OLITE_HOME>\bin\Olite40.jar and '.'
You may change the startup type from the Windows NT service console. Highlight the Oracle Database Lite 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.
Reboot your PC.
The Oracle Database Lite Multi-User Service can be started in many ways. By default, the service property "Startup Type" is automatic; thus, the service is started every time you reboot the machine. If you modify the "Startup Type" to "Manual", then you start Oracle Lite multi-user service by entering any one of the following startup commands from the Command Prompt:
olsv2040.exe /start
net start "Oracle Lite Multiuser Service"
To stop the multi-user service, use one of the following commands:
olsv2040.exe /stop
net stop "Oracle Lite Multiuser Service"
You can query the multi-user status for the following details:
current status
current startup parameters
configuration
installed startup parameters
Issue the following command to see these details:
olsv2040.exe /query
The results of this command are as follows:
OliteService reports the following status:
The service is running...
port= 1531
wdir = C:\WINDOWS\SYSTEM32
The current status of Oracle Lite Multiuser Service:
Current State : SERVICE_RUNNING
Acceptable Control Code : (0x1) SERVICE_ACCEPT_STOP
The configuration of Oracle Lite Multiuser Service:
Service Type : (0x10) SERVICE_WIN32_OWN_PROCESS
Start Type : SERVICE_AUTO_START
Error Control : SERVICE_ERROR_NORMAL
Binary Path : C:\Oracle\product\Mobile\Sdk\bin\olsv2040.exe
Display Name : Oracle Lite Multiuser Service
Start Name : LocalSystem
Installed Service startup parameters
port = 1531
wdir = \%WINDIR%\SYSTEM32
where the port number is 1531 and the working directory is C:\WINDOWS\SYSTEM32. The service is installed under the LocalSystem account, where the startup parameters for installation are port = 1531 and working directory = \%WINDIR%\SYSTEM32. In addition, the Start Type is SERVICE_AUTO_START and the binary path as C:\Oracle\product\Mobile\Sdk\bin\olsv2040.exe, which is where you installed the Oracle Lite Multi-User Service.
|
Note: When you execute the Multi-User Service with the/debug option, then the result of the current status from the /query shows that the service is stopped. Since the /debug option is executed in a console, the Service Control Manager does not know that the service is running.
|
If the service the does not start, debug the service using the following method:
Edit the POLITE.INI file, which is available in Windows under %WINDIR%\POLITE.INI and in Linux under $OLITE_HOME/bin, to add the following entries in the [ALL_DATABASES] section:
OLITE_SERVER_TRACE=TRUE
OLITE_SERVER_LOG=<filename>. This is used for the LINUX platform only.
Should the service fail, the Multi-User Service generates the olsv.log file in the current working directory. Ensure that the PATH and CLASSPATH variables are accurate and that the PATH includes the directory that contains jvm.dll.
Correct the cause and retry.
To access the database using an ODBC or VB application, you must create the DSN enabled from the embedded connection. When you add a DSN using the ODBC Administration tool, choose the Oracle Lite 40 ODBC Driver(Client), which creates a client DSN. If you are executing the service on the same machine where the client application is running, 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 1531, you must specify the Database Port Number.
To access the database, you need not make any changes to the ODBC or VisualBasic application. The DSN automatically routes the request to the service through the ODBC driver olcl2040.dll. For a JDBC application, change the URL for the connect string, which is similar to the one used while connecting to the database using mSQL.
Using the Command Prompt, verify the connection to the multi-user service in the following ways:
Connect to A-DSN on the local host on port 1531.
msql system/passwd@jdbc:polite@::a-dsn
Connect to A-DSN on the local host on port 1000.
msql system/passwd@jdbc:polite@:1000:a-dsn
Connect to A-DSN on the local host on port 1531 using the Type4 JDBC driver.
msql system/passwd@jdbc:polite4@::a-dsn
|
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. |
For more information on JDBC and Oracle Database Lite, see Chapter 10, "JDBC Programming". For details on mSQL, see Section B.1, "The mSQL Tool".
You can move data between an Oracle Lite database and an external file either through programmatic APIs or the Load Utility (OLLOAD). The following sections describe both methods:
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 D, "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 the OLLOAD utility, see Appendix B, "OLLOAD".
For either the Mobile client or embedded solutions, you can back up the Oracle Lite database either by using the Oracle database backupdb utility or by copying the files to another location. For more information on teh backupdb utility, see the Oracle Database documentation.
Oracle Database Lite occupies one file with dependent log files that can be backed up by copying to another location. Before any files can be copied, disconnect all applications that access the database and shut down the multi-user service, if running. Once that has been accomplished, you can copy the *.odb, *.opw, and *.plg files to another directory to make a backup of the database.
For either the Mobile client of embedded solutions, you can encrypt the Oracle Lite database. Once encrypted, the data stored in the database files cannot be interpreted by examining the files. A password is used to derive a 128-bit encryption key. Oracle Database Lite uses the Advanced Encryption Standard (AES) encryption.
If you do not want to use AES encryption, then you can insert your own encryption module to supplant AES; see Section 17.2, "Providing Your Own Encryption Module for the Client Oracle Lite Database" for complete details.
For information on encrypting the database used by the Mobile client, see the ENCRYPT_DB parameter in the POLITE.INI Appendix in the Oracle Database Lite Administration and Deployment Guide; for information on encrypting the database used by an embedded application, see Section B.4, "ENCRYPDB".
Use the ODBINFO utility to discover the version number and volume identifier of the Oracle Lite database. See Section B.7, "ODBC Administrator and the Oracle Database Lite ODBC Driver" for full details.
If you need to migrate your Oracle Lite database, use the MIGRATE utility, which is described in Section B.6, "MIGRATE".
Linguistic sort is a 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.
NLSRT is not supported in the current version of Oracle Database Lite. Therefore, NCHAR data type is not yet available.
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.
After you perform a complete installation of Oracle Database Lite, the samples are available in your <ORACLE_HOME>\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 | <ORACLE_HOME>\Mobile\Sdk\samples\odbc\blob_vb_w32
|
Demonstrates the use of the Oracle BLOB datatype and the Visual Basic ODBC programming methods and object manipulation. See Section 2.12.1, "Executing the BLOB Manager Example" for more information. |
| Java | <ORACLE_HOME>\Mobile\Sdk\samples\jdbc
|
Demonstrates programming with JDBC. See Chapter 10, "JDBC Programming" for more information. |
| ODBC | <ORACLE_HOME>\Mobile\Sdk\samples\odbc\odbc
|
Provides ODBC programs written in C. |
| Visual Basic | <ORACLE_HOME>\Mobile\Sdk\samples\odbc\vb_32
|
Demonstrates the ease of querying tables in Oracle Database Lite with Visual Basic tools. See Section 2.12.2, "Executing the Visual Basic Sample Application" for more information. |
| MFS | <ORACLE_HOME>\Mobile\Sdk\samples\odbc\mfs
|
The MFS sample was documented as a tutorial. See the MFS example used in Chapter 21, "Building Offline Mobile Applications for Win32: A Tutorial". |
|
Note: Most examples use the data source name (DSN)POLITE. If you need to drop and recreate, use the REMOVEDB and CREATEDB utilities, which are documented in Section B.2, "CREATEDB" and Section B.3, "REMOVEDB".
|
The following sections provide instructions on how to use Oracle Database Lite samples.
To install the BLOB Manager example, open the <ORACLE_HOME>\Mobile\Sdk\samples\odbc\blob_vb_w32\setup folder and run setup.exe. After you complete the installation, click Start and select BLOB Manager from the Programs menu.
To uninstall the example, perform the following:
Click Start.
Select Settings.
Select Control Panel.
Select Add/Remove.
Select BLOB Manager.
Click Add/Remove.
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 before you open the Visual Basic project file and execute the project with Visual Basic. Running the program from the Programs menu automatically prepares the table in the database for you.
|
Note: BLOB Manager is for demonstration purposes. It assumes that you have installed the default database with the default DSN:POLITE. If this is not the case, create the POLITE DSN using the ODBC Administrator. Also, verify that SYSTEM is a valid user for the database.
|
The Visual Basic Sample application example uses Visual Basic 5.0 or higher and 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.
The following instructions for installing and running the Visual Basic sample application assume that you have already installed Oracle Database Lite and Visual Basic.
Section 2.12.2.2, "View the Sample Application Tables and Data"
Section 2.12.2.4, "View and Manipulate the Data in the EMP Table"
Double-click the Visual Basic icon in your Visual Basic program group to open Visual Basic.
Use the Visual Data Manager, which is available only with Visual Basic 5.0. If you are using an earlier version of Visual Basic, then skip to Step 3.
From the Add-Ins menu, select Visual Data Manager.
In the VisData window, select Open Database from the File menu.
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 <ORACLE_HOME>\Mobile\Sdk\Examples\VB directory.
Select update.mak, and click Open.
|
Note: If you do not see theupdate.mak file listed, select Files of type *.* to show all file types. You should see the file in the list.
|
From the Run menu, select Start to open the sample application and display the EMP table.
The ODBC examples are located in <ORACLE_HOME>\Mobile\Sdk\Samples and must be compiled using a C++ complier. To build them, use nmake.
There are five ODBC examples: odbctbl, odbcview, odbcfunc, odbctype, and long. You use the POLITE DSN to execute these examples. The POLITE DSN is automatically created during the Mobile Development Kit installation.
The first four examples have their own output windows listing the activity log. Closing the current example window causes the next example to be run. The output displayed in the example windows is also printed in the following 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:
This is an ODBC SQL table example, which shows how to manipulate tables using the ODBC API. It creates the EMP table with columns ID, NAME, START_DATE, SALARY. After creation, it populates this table with data, performs 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, which demonstrates how to manipulate views using the ODBC API. It creates the EMP table and the HIGH_PAID_EMP view, selecting the full name (using the CONCAT scalar function), HIRE_DATE and SALARY from the EMP table. Then, the example populates the EMP table and selects from the HIGH_PAID_EMP view to show the populated data. The salary column of EMP is updated, some rows are delete, and a select from HIGH_PAID_EMP is issued to demonstrate how the changes are reflected in the view. Finally, the view and the table are dropped.
This is an ODBC SQL scalar functions example, which shows you how to use scalar functions in the ODBC API. It creates table EMP, populates it with the data, then performs a select on ID, FULL_NAME from EMP. When it calculates the full name, it uses the ODBC scalar function CONCAT—with last and first names as arguments. The example updates the table, converting the last name to uppercase and first name to lowercase for IDs less than three using ODBC scalar functions UCASE and LCASE. The new data is selected and displayed again. Finally, the table EMP is dropped.
This is ODBC SQL types example, which shows you how to manipulate different data types using the ODBC API. This test creates the EMP table, populates it with data, selects all the rows and displays the result. However, 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 retrieve all of the information about that column, such as column name, column name length, column type, column length, column scale, and so on. This information is used to bind the column. Thus, you can see how you can retrieve 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 LONG_DATA table 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, issues a select 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 long.out file.
Currently, the Oracle Database Lite engine cannot sort any row that exceeds 4040 bytes in length. If the selected columns exceed this length, then the database engine issues an error. Therefore, you cannot recover queries that use the UNION operation where both select clauses sort intermediate results, where the returned results are long rows with size greater than 4040 bytes.
