Oracle® Database Lite Oracle Lite Client Guide Release 10.3 Part Number E12548-02 |
|
|
View PDF |
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:
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:
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 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".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 |
---|---|
|
An optional description for the data source. Use only for Windows environment. |
|
The path to the data directory where the database resides. This is an existing path. |
|
Oracle Database Lite database name to be created. Do not include the |
|
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 |
|
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 |
|
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 |
---|---|
|
Provide the server machine hostname or IP address where the database service is running. |
|
The port number where the database service is listening for incoming requests. The default port number is 1160. |
|
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 |
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.
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.
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.
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 |
---|---|
|
Enables the user to create other users and grant privileges other than
|
|
Enables the user to issue the following DDL statements which otherwise can only be issued by All ADMIN privileges, |
|
The
|
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 libraries in your application.
The following sections describe what libraries to include for each operating system platform:
To package an embedded application on Windows, 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
, olcl2040.dll
, 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
, 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.
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 Appendix A, "POLITE.INI Parameters for the Oracle Lite Database" for more information on how to configure thePOLITE.INI
file.To package an embedded application on Linux, perform the following:
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.
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
If you are using the multi-user service, copy the following into the OLITE_HOME
/bin
directory:
olsv oldaemon libolsv2040.so
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.
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
Create the DSN and register the ODBC driver by modifying the OLITE_HOME
/bin/odbc.ini
file.
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 thePOLITE.INI
file.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".
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 |
< |
Demonstrates programming with JDBC. See Chapter 10, "JDBC Programming" for more information. |
ODBC |
< |
Provides ODBC programs written in C See Section 9.2, "Executing the ODBC Examples". |
Visual Basic |
< |
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 |
< |
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.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.
To open the sample application, select Open Project from the File menu of Visual Studio 2005.
In the dialog box, navigate to the <ORACLE_HOME
>\Mobile\Sdk\samples\odbc\win32\update
directory.
Select update.vbproj
, and click Open.
Follow the instructions in readMe.txt
in the same location to execute the sample.
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.
To manipulate data in the EMP
table, use the Add, Update, and Delete features.