Skip Headers

Oracle® Database Lite Oracle Lite Client Guide Release 10.3 Part Number E12548-02 |
Home | Book List | Contents | Index | Contact Us

Previous | Next
| View PDF

# 4 Building an Embedded Application

Perform the following when building an application with an embedded Oracle Lite database:

- Create the Oracle Lite database.

- Create the users.

- Package your application.

The following sections describe how to perform these tasks:

- Section 4.1, "Creating the Oracle Lite Database"

- Section 4.2, "Creating Users for the Oracle Lite Database"

- Section 4.3, "Packaging Your Embedded Application With the Oracle Database Lite Runtime"

- Section 4.4, "Connecting to the Oracle Lite Database"

- Section 4.5, "Using Oracle Database Lite Samples"

## 4.1 Creating the Oracle Lite Database

To create the Oracle Lite database, you must first create a data source name (DSN) for the database and then create the database. This is described in the following sections:

- Section 4.1.1, "Creating a Data Source Name with ODBC Administrator"

- Section 4.1.2, "Creating a New Oracle Lite Database"

### 4.1.1 Creating a Data Source Name with ODBC Administrator

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:

- Section 4.1.1.1, "Creating DSN on a Windows System"

- Section 4.1.1.2, "Creating DSN on a LINUX System"

#### 4.1.1.1 Creating DSN on a Windows System

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 2003/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.

The `ODBC.INI` file is available in Windows under `%WINDIR%`. For more information on DSN properties, see Table 4-1 and Table 4-2.

Note:

The name of the ODB file is used in the next step: Section 4.1.2, "Creating a New Oracle Lite Database". For more information on the ODBC Administrator, and for instructions on creating a data source name using the tool, refer to Appendix C, "ODBC Administrator and the Oracle Database Lite ODBC Driver".

#### 4.1.1.2 Creating DSN on a LINUX System

In order to create a DSN on a LINUX platform, add the DSN in the `ODBC.INI` file. In this file, add the DSN in its own section, where the section name is the DSN name. The `ODBC.INI` file is available in Linux under `$OLITE_HOME/bin`. For the Linux platform, you must have write permissions on the directory where this is located to be able to modify them.

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 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 Chapter 5, "Building a Client/Server Environment".

    [Polite]
    Description=Oracle Lite 40 Data Source
    Data_Directory=/home/olite
    Database=polite
    IsolationLevel=Read Committed
    Autocommit=Off
    CursorType=Forward Only

    [Politecl]
    Description=Oracle Lite 40 Data Source
    Data_Directory=/home/olite
    ServerHostName=localhost
    ServerPortNumber=1160
    Database=polite
    IsolationLevel=Read Committed
    Autocommit=Off
    CursorType=Static

The default port number is 1160.

The parameters that you can use are listed in Table 4-1:

Table 4-1 ODBC.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?". 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. On: DML and DDLs are automatically committed. Off: An application has to explicitly issue the transaction commit or rollback commands. 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 `SQLSetConnectAtrr` or `SQLSetConnectOption` method with `SQL_AUTOCOMMIT_OFF` as the value of the `SQL_AUTOCOMMIT` option. Then, the `SQLEndTrans / SQLTransact` calls will commit as defaulted within Oracle Database Lite. Thus, if you want auto-commit on, turn it on only within Oracle Database Lite.
`Cursor Type` | Forward Only: Default. A non-scrollable cursor which only moves forward but not backward through the result set. As a result, the cursor cannot go back to previously fetched rows. Dynamic: Capable of detecting changes to the membership, order, or values of a result set after the cursor is opened. If a dynamic cursor fetches rows that are subsequently deleted or updated by another application, it detects those changes when it fetches those rows again. Keyset Driven: Does not detect change to the membership or order of a result set, but detects changes to the values of rows in the result set. Static: Does not detect changes to the membership, order or values of a result set after the cursor is opened. If a static cursor fetches a row that is subsequently updated by another application, it does not detect the changes even if it fetches the row again. 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 Chapter 5, "Building a Client/Server Environment"—then the DSN entries have the following additional parameters:

Table 4-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. The default port number is 1160.
`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.

### 4.1.2 Creating a New Oracle Lite Database

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 Appendix C, "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.

## 4.2 Creating Users for the Oracle Lite Database

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. Thus, the user can access database objects in its schema without prefixing them with the schema name. Users with the appropriate privileges can create additional schemas with the `CREATE SCHEMA` command.

You connect to the database with the username. All schemas are owned by the user who created them. If the schema name is different from the username, you must provide the schema name prefix in order to access objects in that schema.

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.

Note:

For more information on the `CREATEDB` utility, see Appendix C, "CREATEDB".

To access data and perform operations in another user schema, a user must grant you DBA or ADMIN privileges. The `SYSTEM` user can access all data, as it 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.

Note:

Both username and passwords are limited to a maximum of 28 characters.

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.

- Section 4.2.1, "Pre-Defined Roles"

- Section 4.2.2, "Building and Populating Demo Tables"

### 4.2.1 Pre-Defined Roles

Oracle Database Lite combines some privileges into pre-defined roles for convenience. In many cases it is easier to grant a user a pre-defined role than to grant specific privileges in another schema. Oracle Database Lite does not support creating or dropping roles. Following is a list of Oracle Database Lite pre-defined roles:

Table 4-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: `CREATE SCHEMA`, `CREATE USER`, `ALTER USER`, `DROP USER`, `DROP SCHEMA`, `GRANT`, `REVOKE`
`DBA` | Enables the user to issue the following DDL statements which otherwise can only be issued by `SYSTEM`: All ADMIN privileges, `CREATE TABLE`, `CREATE ANY TABLE`, `CREATE VIEW`, `CREATE ANY VIEW`, `CREATE INDEX`, `CREATE ANY INDEX`, `ALTER TABLE`, `ALTER VIEW`, `DROP TABLE`, `DROP VIEW`, and `DROP INDEX`.
`RESOURCE` | The `RESOURCE` role grants the same level of control as the `DBA` role, but only over the user's own schema. The user can execute any of the following commands in a SQL statement: `CREATE TABLE`, `CREATE VIEW`, `CREATE INDEX`, `CREATE CONSTRAINT`, `ALTER TABLE`, `ALTER VIEW`, `ALTER INDEX`, `ALTER CONSTRAINT`, `DROP TABLE`, `DROP VIEW`, `DROP INDEX`, `DROP CONSTRAINT`, and `GRANT` or `REVOKE` privileges on any object under a user's own schema.

General Note:

Unlike the Oracle database server, Oracle Database Lite does not commit data definition language (DDL) commands until you explicitly issue the `COMMIT` command.

### 4.2.2 Building and Populating Demo Tables

Oracle Database Lite comes with a script called `POLDEMO.SQL`, which enables you to build the same tables that are in your Oracle 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.

## 4.3 Packaging Your Embedded Application With the Oracle Database Lite Runtime

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 libraries in your application.

The following sections describe what libraries to include for each operating system platform:

- Section 4.3.1, "Packaging an Embedded Application on Windows"

- Section 4.3.2, "Packaging an Embedded Application on Linux"

### 4.3.1 Packaging an Embedded Application on Windows

To package an embedded application on Windows, perform the following:

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

2. If you are using the Multi-User Service, copy `olsv2040.exe`, `olcl2040.dll`, and `olsvmsg.dll` into your PATH where your application DLLs are located.

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

4. 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`

5. Manage ODBC for creating the DSN and registering the ODBC driver. On Linux, modify the `ODBC.INI` file. On Windows, perform the following:

   1. To use Microsoft ODBC for the ODBC environment—including DSN creation support—or to create and manage DSN names programmatically, place the `olad2040.dll`, `olAES.dll`, `olCast5.dll`, `olil125x.dll`, `olr24US.dll`, `mfc71.dll`, `msvcp71.dll`, and `msvcr71.dll` in the `PATH`. The `olad2040.dll` provides a plug-in to programmatically access the ODBC administration tool—`odbcad32`—that is used to create DSNs.

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

6. 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 Appendix A, "POLITE.INI Parameters for the Oracle Lite Database" for more information on how to configure the `POLITE.INI` file.

### 4.3.2 Packaging an Embedded Application on Linux

To package an embedded application on Linux, perform the following:

1. Define `OLITE_HOME` as the installation directory where your application resides. All Oracle Database Lite files are located in the `OLITE_HOME``/bin` directory. Include the `OLITE_HOME``/bin` directrory in the PATH variable as well as in the `LD_LIBRARY_PATH` variable.

2. Copy the following files from the Mobile Development Kit library located in `ORACLE_HOME``/Mobile/Sdk` into `OLITE_HOME``/bin`.

       olite40.msb
       liboljdbc40.so
       libokapi.so
       libolobj40.so
       libolodbc.so
       libolsql.so
       libolstd.so
       libolutil.so
       libolaes.so
       libolcast5.so
       libolil125x.so

3. If you are using the multi-user service, copy the following into the `OLITE_HOME``/bin` directory:

       olsv
       oldaemon
       libolsv2040.so

4. To use any Java program with Oracle Database Lite, include the `olite40.jar` file, which is installed in `OLITE_HOME``/bin`, in the application `CLASSPATH`. If the Java program uses the multi-user service, also place this JAR file in the `CLASSPATH`. The `olite40.jar` file contains the JDBC driver for Oracle Database Lite. Your environment must provide a Java Runtime Environment from Sun, version 1.4.2 or higher.

5. To provide the mSQL command-line tool for querying and managing the Oracle Lite database, place the following files in the `OLITE_HOME``/bin`:

       libmsql.so
       msql
       msql.jar

6. Create the DSN and register the ODBC driver by modifying the `OLITE_HOME``/bin/odbc.ini` file.

7. Configure the `POLITE.INI` file and place it in `OLITE_HOME``/bin` as follows:

       [All Databases]
       NLS_LANGUAGE=ENGLISH
       NLS_LOCALE=ENGLISH
       DB_CHAR_ENCODING=Native
       DATA_DIRECTORY=<default_directory_to_create_database_files>

Note:

See Appendix A, "POLITE.INI Parameters for the Oracle Lite Database" for more information on how to configure the `POLITE.INI` file.

## 4.4 Connecting to the Oracle Lite Database

Connect to the file-based Oracle Lite database using your application or mSQL, which is a command line interface. See Appendix C, "The mSQL Tool" for full details.

When connecting to the database from an application, use the DSN name that you created in Section 4.1.1, "Creating a Data Source Name with ODBC Administrator". and the database name (ODB name) that you defined in Section 4.1.2, "Creating a New Oracle Lite Database".

To connect to a database with the `POLITE` database (ODB) name, `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.

The Oracle Lite database has its own JDBC driver, which you can use to connect to the client Oracle Lite database.

- Local Connection—Use the following URL syntax to initiate a local connection to a client database:

      jdbc:polite:<DSN>

  The following example connects to a client Oracle Lite database with the default DSN: `polite`:

      jdbc:polite:polite

- Remote Connection—Use the following JDBC URL to initiate a remote connection to the client Oracle Lite database:

      jdbc:polite[@<hostname>]:[<port>]:<DSN>

  The hostname and port are optional. The following example connects to the local machine on port 1000 to the `polite` database.

      jdbc:polite@:1000:polite

These URLs default to using the type 2 JDBC driver. You can specify that the connection uses a type 4 JDBC driver. For full details on both drivers and all options for connection, see Section 10.3, "JDBC Drivers to Use When Connecting to the Oracle Lite Database".

## 4.5 Using Oracle Database Lite Samples

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

Table 4-4 Sample File Directory

Tool | Location of Sample Applications | Description
--- | --- | ---
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\win32\c_samples` | Provides ODBC programs written in C See Section 9.2, "Executing the ODBC Examples".
Visual Basic | <`ORACLE_HOME`>`\Mobile\Sdk\samples\odbc\win32\update` | Demonstrates the ease of querying tables in Oracle Database Lite with Visual Basic application. See Section 4.5.1, "Executing the Visual Basic Sample Application" for more information.
Multiple Field Service | <`ORACLE_HOME`>`\Mobile\Sdk\samples\odbc\win32\mfs` | The Multiple Field Service sample uses ODBC to access the Oracle Lite database.

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 Appendix B.2, "CREATEDB" or Appendix B.3, "REMOVEDB" in the Oracle Database Lite Client Guide.

### 4.5.1 Executing the Visual Basic Sample Application

The Visual Basic Sample application example uses Visual Basic 2005 to demonstrate 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.

1. Section 4.5.1.1, "Open the Sample Application"

2. Section 4.5.1.2, "View and Manipulate the Data in the EMP Table"

#### 4.5.1.1 Open the Sample Application

1. To open the sample application, select Open Project from the File menu of Visual Studio 2005.

2. In the dialog box, navigate to the <`ORACLE_HOME`>`\Mobile\Sdk\samples\odbc\win32\update` directory.

3. Select `update.vbproj`, and click Open.

4. Follow the instructions in `readMe.txt` in the same location to execute the sample.

#### 4.5.1.2 View and Manipulate the Data in the EMP Table

1. To view data in the `EMP` table:

   - Click Show to show the `EMP` table data.

   - Click Next to show the next record.

   - Click Previous to show the previous record.

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