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.