| Oracle® Database Lite Oracle Lite Client Guide Release 10.3 Part Number E12548-02 |
|
|
View PDF |
| Oracle® Database Lite Oracle Lite Client Guide Release 10.3 Part Number E12548-02 |
|
|
View PDF |
This appendix describes how to use the following Oracle Lite database utilities for the Windows 32 and Windows CE platforms. Table C-1 lists all of the utility names:.
Table C-1 Database Tools and Utilities
| Utility | Description |
|---|---|
|
Allows users to execute SQL statements against the Oracle Lite database. |
|
|
Use this to create your Oracle Lite database. |
|
|
Use this to remove your Oracle Lite database. |
|
|
Use this to encrypt your Oracle Lite database. |
|
|
Use this to decrypt your Oracle Lite database. |
|
|
Use this to backup your Oracle Lite database. |
|
|
Section C.7, "DefragDB to Defragment and Reduce Size of the Oracle Lite Database" |
Defragmenting to reduce the size of the OracleLite database. |
|
Section C.8, "ODBC Administrator and the Oracle Database Lite ODBC Driver" |
Use this to manage ODBC connections by creating data source names (DSNs) that associate the Oracle Database Lite ODBC Driver with the Oracle Database Lite that you want to access through the driver. |
|
Use this utility to find out the version number and volume ID of an Oracle Database Lite database. |
|
|
Use this to validate the structure of an Oracle Lite database and find any corruption of the database. |
|
|
Section C.11, "Transferring Data Between a Database and an External File" |
Use either the command-line tool or programmatic APIs 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. |
|
Support for Linguistic Sort |
Allows databases to be created with linguistic sort capability enabled. See Section 7.4, "Support for Linguistic Sort" for full details. |
|
The |
|
|
The |
Mobile SQL (mSQL) is a GUI-based application that runs on the client device (laptop and Windows CE). It allows the user to execute SQL statements against the local database. It is a development tool that enables users to execute SQL statements to the Oracle Lite database.
Using mSQL you can accomplish the following:
Create databases
View tables
Execute SQL statements
Note:
UTF8 SQL Scripts are not supported in mSQL.The following sections describe how to use the mSQL tool on two platforms:
On Windows 32 platform, the mSQL tool accesses the database through JDBC. The following sections describe how to use the mSQL command-line to access the database for the Windows 32 platform:
Start mSQL by opening the ORACLE_HOME\Mobile\SDK\Bin directory and double-click the msql.exe file. This starts the command-line interface that accepts standard SQL commands.
You can also start mSQL from the command-line, as follows:
msql <username>/<password>[@<JDBC_URL>]
Where:
<username>/<password>: The client username and password for the Oracle Lite database.
<jdbc_url>: The JDBC URL is optional. If not specified, then the JDBC URL defaults to the URL defined in the webtogo.ora file. You can specify the JDBC URL of a single Oracle database or an Oracle RAC database, as follows:
The URL for a single Oracle database has the following structure: jdbc:oracle:thin:@<host>:<port>:<SID>
The JDBC_URL for an Oracle Lite database using embedded JDBC connection is jdbc:polite:<localDSN>
The JDBC_URL for an Oracle Lite database using Type 2 JDBC driver and the Multi-User (MU) service is jdbc:polite@<host>:<PortNo>:<serverDSN>, where the Multi-User Oracle Lite database is on the <host>, the MU is listening on <PortNo> and the DSN is <ServerDSN>.
The JDBC_URL for an Oracle Lite database using Type 4 JDBC driver and the Multi-User service is jdbc:polite4@<host>:<PortNo>:<serverDSN>
The JDBC URL for an Oracle RAC database can have more than one address in it for multiple Oracle databases in the cluster and follows this URL structure:
jdbc:oracle:thin:@(DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=PRIMARY_NODE_HOSTNAME)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=SECONDARY_NODE_HOSTNAME)(PORT=1521)) ) (CONNECT_DATA=(SERVICE_NAME=DATABASE_SERVICENAME)))
For more information on commands you can execute within the mSQL command, see the Oracle Database Lite SQL Reference.
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 a SQL script from the mSQL prompt, as follows:
msql> @<ORACLE_HOME>\DBS\Poldemo.sql
You can also type:
msql> START <filename>
Note:
You do not need to include the
.sql file extension when running the script.Controls the display of output generated by commands executed from a script. OFF suppresses the display so that you can spool output from a script without seeing the output on the screen. ON displays the output. TERM OFF does not affect output from commands you enter interactively.
Controls the display of timing statistics. ON displays timing statistics on each SQL command. OFF suppresses timing of each command.
Controls whether to list the text of a SQL statement or PL/SQL command before and after replacing substitution variables with values. ON lists the text; OFF suppresses the listing.
Describes the table columns displaying the column name, type and whether it can be null.
The mSQL tool allows the user to execute SQL statements against the local database. You can use either the mSQL tool as a command-line or GUI tool.
The following sections describe the GUI or the command-line tool:
Start the mSQL GUI tool by double-clicking on msql.exe. The mSQL GUI tool provides you the ability to perform the following tasks:
Section C.1.2.1.2, "Execute SQL Statement Against Oracle Lite Database"
Section C.1.2.1.3, "Create or Encrypt the Oracle Lite Database"
Section C.1.2.1.4, "Table Contents of the Oracle Lite Database"
Select the Connect tab to connect to the Oracle Lite database on the device.
If you have more than one Oracle Lite database on the device, select the appropriate database from the pull-down.
Provide the username and password for this database.
Click Connect.
Figure C-1 Connect to the Oracle Lite Database
Select the SQL tab to execute a SQL statement against the Oracle Lite database. After connecting, enter your SQL statement and click Execute. The statement and any results are displayed in the bottom window. For more information, see the Oracle Database Lite SQL Reference.
The Tools tab enables you to create an Oracle Lite database or to encrypt or validate and existing database.
Create Database: You can create an Oracle Lite database to use embedded within a standalone application.
Encrypt/Decrypt/Validate: Select the Oracle Lite database that you want to execute the EncrypDB, DecrypDB or ValidateDB commands against. The password that you provide is the user password. Click on the appropriate button for each of these functions.
See the following sections for details on EncryptDB, DecryptDB or ValidateDB:
Figure C-3 Create, Encrypt, Decrypt, or Validate the Oracle Lite Database
When you select the Tables tab, you can select any of the tables in the Oracle Lite database and click Describe. The structure and contents of this table is displayed.
Figure C-4 Table Description for Oracle Lite Database
When you select the Views tab, you can select any of the views in the Oracle Lite database and click Describe. The view definition is displayed.
Figure C-5 Show Views of Oracle Lite Database
When you select the Sequences tab, you can select any of the sequences in the Oracle Lite database and click Describe. The sequence definition is displayed.
Figure C-6 Display Sequence Definition of Oracle Database Lite
The Oracle Lite database format is the same on Windows 32 and Windows CE platforms. Manage your snapshots, as follows:
Create and test your snapshots on Windows 32 using the Windows 32 mSQL command-line utility.
Copy the database to the Windows CE platform.
Use the Windows CE mSQL tool to manipulate the database that is on your device.
The mSQL tool enables the user to execute SQL statements against the local database and access functionality provided by the interfaces of the underlying Oracle Lite database engine.
Utility for creating a database.
CREATEDB DataSourceName DatabaseName Database_SysUser_Password [[[VolID] DATABASE_SIZE] EXTENT_SIZE] [collation sequence]
Keywords and Parameters
DataSourceName
Data source name, used to look up the ODBC.INI file for the default database directory.
Note:
If you specify an invalid DSN, Oracle Database Lite ignores the DSN and creates the database in the current directory. To access this database through ODBC, you must create a DSN for the database that points to the directory in which the database resides. For instructions on adding a DSN, see Section C.8.1, "Adding a DSN Using the ODBC Administrator".DatabaseName
Name of the database to be created. It can be a full path name or just the database name. If only the database name is given, the database is created under the Data Directory for the data source name specified in the ODBC.INI file. The extension for the database name must always be .ODB. If a name without the .ODB is given, the .ODB is appended.
DATABASE_SysUser_Password
The database system user password.
VolID
When specified, the VolID is used as the database ID, instead of the database ID from the POLITE.INI file. The ID must be unique for each database. If you specify a volumn id, then you also specify the database and extent sizes. Thus, the createdb executable knows that the volume id, database size and extent size are being specified when three numbers are provided in a row.
Note:
For the volume id, database size, and extent size, specify only the number; do not specify name=value. See the examples for more information.DATABASE_SIZE
The database size in bytes. If you want to specify the database size, then you also must specify the volume id and extent size.
EXTENT_SIZE
An incremental amount of pages in a database file. When a database runs out of pages in the current file, it extends the file by this number of pages. If you want to specify the extent size, then you also must specify the volume id and database size.
COLLATION_SEQUENCE
This parameter is a string constant which creates the database as enabled for linguistic sorting when a value other than the default is used. A collation sequence specified here overrides a collation sequence set using the NLS_SORT [collation_sequence] parameter in the POLITE.INI file. The string can also be one of the options listed in Table C-2:
Table C-2 Collation Sequence Values
| Collation Sequence | Description |
|---|---|
|
|
Default. Two strings are compared character by character and the characters are compared using their binary code value. You cannot perform a linguistic sort with an Oracle Lite database that has a binary collation sequence. |
|
|
Two strings are compared according to the collation sequence of French. Supported by ISO 8859-1 or IBM-1252. |
|
|
Two strings are compared according to the collation sequence of German. Supported by ISO 8859-1 or IBM-1252. |
|
|
Two strings are compared according to the collation sequence of Czech. Supported by ISO 8859-2 or IBM-1250. |
|
|
Two strings are compared according to the collation sequence of Xczech. Supported by ISO 8859-2 or IBM-1250. |
Note:
There is no way to alter a collation sequence after the database is created.Examples
Create the db1 database with DSN of polite and password manager: createdb polite db1 manager
Create the db2.odb database with DSN polite and password manager300: createdb polite c:\testdir\db2.odb manager300
Create polite database with DSN polite, password of manager, and a collation sequence of french: createdb polite polite manager french
Create polite database with DSN polite, password manager, volume id of 199, database size of 1000, and extent size of 1: createdb polite polite manager 199 1000 1
Utility for deleting a database.
Syntax
REMOVEDB DataSourceName Database Name
Keywords and Parameters
DataSourceName
Data source name of the database you want to remove. The DSN can be a dummy argument such as none, in which case the database name must be a fully qualified filename.
DatabaseName
The name of the database to delete. It can be a full path name or just the database name. If only the database name is given, the database is deleted from the Data Directory for the data source name specified in the ODBC.INI file.
Examples
removedb polite db1
removedb none c:\testdir\db2.odb
Enables you to encrypt Oracle Database Lite with a password, which prevents unauthorized access to the database and encrypts the database, so that the data stored in the database files cannot be interpreted. To decrypt the database, see Section C.5, "DECRYPDB".
This tool is used by embedded applications to encrypt the database used by the application. You provide the user password for the encryption.
This is more difficult on a handheld as it is sometimes difficult for users to find the RUN option in order to execute the command with arguments.
ENCRYPDB uses AES-128 encryption.
Syntax
ENCRYPDB DSN | NONE DBName [New_Password [Old_Password]]
Keywords and Parameters
DSN—Data Source Name of Oracle Database Lite that you want to encrypt. If you specify NONE, DBName must be a fully qualified database name with the full path name (without the .ODB extension). If the DSN is a value other than NONE, then the name must appear as a data source name in the ODBC.INI file.
DBName—Name of the database to be encrypted. If DSN was specified as NONE, DBName must be entered with the full path name.
New_Password and Old_Password—Optional, the password (or previously used password) for encrypting the database. This password can be 128 characters in length. If you do not enter a password, ENCRYPDB prompts you to enter one. Since both passwords are optional in the command line to invoke the utility, the command line could have three different forms:
No password given: If the database is already encrypted, then ENCRYPDB assumes that the user is trying to change the password of the database. It prompts the user for the old password once and new password twice, and encrypts the database using the new password. If the database is not already encrypted, ENCRYPDB prompts for the new password twice and encrypts the database using this new password.
One password given: This password is assumed to be the new password. If the database is already encrypted, ENCRYPDB prompts for the old password and encrypts the database using the new password.
Both passwords given: ENCRYPDB assumes that the first password is the new password and the second is the old password.
To run from the command line, you must pass in the DSN name and the database name. The following encrypts the employee database with DSN of Employee with the test password:
Encrypdb Employee employee test test
Comments
If you call this utility from another program, the possible values returned are listed in Table C-3:
Table C-3 ENCRYPDB Return Codes
| Return Code | Description |
|---|---|
|
|
Success |
|
|
Command line arguments are not properly used or are in error |
|
|
Path is too long |
|
|
I/O error while making new encrypted copy on disk |
|
|
Incorrect password supplied |
The default Oracle Database Lite (POLITE.ODB) is not encrypted. After encrypting an Oracle Database Lite, every user that attempts to establish a connection to the encrypted Oracle Database Lite must provide the valid password. If the password is not provided, Oracle Database Lite returns an error. An Oracle Database Lite database cannot be encrypted if there are any open connections to the database.
You should consider the following when encrypting and decrypting Oracle Database Lite:
You cannot decrypt an encrypted database without the password. Make sure you back up your database in a secure place before you encrypt it. Another user of the same database can create a copy with a new user name for a user who loses their password, otherwise, there is no method to recover a database where the passwords are lost.
A password encrypts the entire database. It is not a user-specific password.
Database encryption does not prevent a third party from removing an Oracle Lite Database. That is, removedb and rmdb remove a database without checking the password. Use tools that protect unauthorized users from manipulating your file system.
ODBC applications that connect to an encrypted Oracle Database Lite database need to specify a valid password. It is customary to prompt for the password at runtime rather than to code it in the application. Most ODBC applications can use the SQLDriverConnect function with the DRIVER= option, rather than the SQLConnect function, if the applications require the Oracle Database Lite ODBC driver to prompt for the password at runtime.
All sample applications provided with this release of Oracle Database Lite are designed to run against a database that is not encrypted.
You can use DECRYPDB and ENCRYPDB (in this order) to change the password of a database. However, DECRYPDB creates an Oracle Database Lite database in plain text before ENCRYPDB encrypts it. This results in a database in plain text form, for a short period of time, and is not recommended.
For encrypted databases, all user names and passwords are written to a file named DSN.OPW. Each user can then use the password as a "key" to unlock the .OPW file before the .ODB file is accessed. When you copy or back up the database, you should include the .OPW file.
Description
This tool allows you to decrypt an encrypted Oracle Lite database used with an embedded application. For more information, see Section C.6, "BACKUPDB".
This tool is used by embedded applications to decrypt the database used by the application. To encrypt an Oracle Lite database used by a client, see the ENCRYPDB executable in the Section C.4, "ENCRYPDB".
SYNTAX
DECRYPDB DSN | NONE DBName [Password]
Keywords and Parameters
DSN
Data Source Name of Oracle Database Lite that you want to decrypt. If you specify NONE, you must the enter the DBName with the full path name (without the .ODB extension).
DBName
Name of the database to be decrypted. If DSN was specified as NONE, the DBName must be entered with the full path name.
Password
Optional. The password used previously to encrypt Oracle Database Lite. If you do not enter the password, DECRYPDB prompts you to enter it.
Comments
An Oracle Database Lite database cannot be decrypted if there is any open connection to the database.
If you call this utility from another program, the possible values returned are listed in Table C-4:
Table C-4 DECRYPDB Return Codes
| Return Code | Description |
|---|---|
|
|
Success |
|
|
Command line arguments are not properly used or are in error |
|
|
Path is too long |
|
|
I/O error while making new decrypted copy on disk |
|
|
Incorrect password supplied |
For more information, see the comments in Section C.6, "BACKUPDB".
You can back up the Oracle Lite database either by using the backupdb utility or by copying the files to another location.
Oracle Database Lite uses the ODB and OBS files 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, execute the backupdb utility, which copies the *.odb, *.obs, and *.opw files to the filename of your choice to make a backup of the database.
BACKUPDB DSN|NONE DBName backup_filename [DB_password]
If you want to restore the backup, then execute the backupdb executable, with NONE and reversing the filename and the dbname, as follows:
BACKUPDB NONE backup_filename DBName
This is more difficult on a handheld as it is sometimes difficult for users to find the RUN option in order to execute the command with arguments.
Syntax
BACKUPDB DSN | NONE DBName <backup_filename> [<DB_password>]]
Keywords and Parameters
DSN—Data Source Name of Oracle Database Lite that you want to backup. If you specify NONE, DBName must be a fully qualified database name with the full path name (without the .ODB extension).
DBName—Name of the database to be encrypted. If DSN was specified as NONE, DBName must be entered with the full path name.
backup_filename—File where you want the backup to be stored. This can include an absolute or relative path. If no path is included, then the file is stored in the directory where the command is executed.
DB_password—The password you used for encrypting the database. This is only required if the database you are backing up is encrypted. You can either supply this password on the command line or when prompted during execution.
To run from the command line, you must pass in the DSN name and the database name. The following backs up the employee database with DSN of Employee into the backupemployee file:
Backupdb Employee employee backupemployee
On each client device, an Oracle Lite database stores the application data. You can optimize the Oracle Lite database with the DefragDB utility, as follows:
Reduce size of Oracle Lite databases by defragmenting the Oracle Lite database.
Remove any BLOB data from the Oracle Lite database. All BLOB data—both binary and character— and indexes are stored in separate files with the extension of .obs for Oracle Blob Store. This changes the size limit on your device to either the operating system file size limitations or 16 terabytes.
Note:
This tool removes any Blob data currently in your Oracle Lite database and stores it in its own
.obs file. However, if you do not run this tool, you can continue to work seamlessly. Any new Blob data is stored in an .obs file; any pre-existing Blob data can continue to reside in the .odb file.Use the DefragDB tool to defragment Oracle Lite databases, which reduces their size by compacting them and removing any Blob data from within the database into its own .obs file. The DefragDB tool is a UI dialog which allows you to choose which databases to defragment. This tool defragments databases by dumping each database into a file and then reloading it from this file. Alternatively, you can use the command-line interface: olmig.exe. Both tools exist in the <ORACLE_HOME>/Mobile/Sdk/bin on your desktop or in \OraCE on a WinCE device.
Note:
Currently the tool runs on Win32 desktop and Windows CE devices.The following sections describe this tool:
To start the tool, either double-click on the Oracle Database Lite Degramentation icon or execute DefragDB.exe, which brings up the following screen:
You can execute the Oracle Database Lite Defragmentation tool on the client for all applications. Before executing this tool, you must stop ALL applications, as the database is erased during this process.
All application databases are listed on this screen. Select the existing databases on your PC (or WinCE device) on which you want to perfrom the deframentation.
Click Defragment to defragment all databases. This tool performs a defragmentation on one database at a time.
Note:
In the worse case scenario, the defragmentation process requires three times the space of the database to complete the process. Thus, if you do not have enough space to defragment your larger databases, you will receive a warning notice about the database that is too large to complete the process. In order to continue, either free up enough space to enable the process to complete or return to the main screen and select all databases except for the offending one.To defragment specific databases, select the databases desired from the list and click Defragment.
Note:
To cancel out without performing any defragmentation, click Cancel. See Section C.7.2, "Pause or Cancel Defragmentation" for more information.In addition, select the following checkboxes, as appropriate:
Create backup: provides a backup of the original copy of the database before defragmentation. The backup copy has the same name with a .bak extension. For example, C:\orant\oldb40\polite.odb becomes C:\orant\oldb40\polite.odb.bak. Thus, you can restore the database if an error occurs during defragmentation. In addition, the blob storage file (.obs) is backed up. To restore to the original version, rename these files back to the original names without the .bak extensions.
Create log: provides a log, defragdb.log, of the defragmentation process, which is useful for the developer in diagnosing any problems that may occur during the defragmentation.
When you click Defragment, the process initiates, which brings up a window that displays messages about the progress of the defragmentation. This same log can be saved in the defradb.log file. See the status bar at the bottom for the final status: defragmenting, success, or fail.
If your database is encrypted, another dialog prompts for the user name and password for the encrypted database. Select the user name from the list of users and enter the password for that user. You only need to enter the password once if all databases are encrypted with the same password.
There are 2 files created during the defrag which should be deleted automatically by DefragDB when it finishes:
The dump file, which has the same name as the database, but with a .dmp extension.
The newly loaded database, which has the same name as the database, but witha _defrag suffix. This file is renamed to the database name once the load completes.
If you are defragmenting large databases, this may take some time. For example, it takes about 5 minutes on a desktop to defragment a 200 MB database; however, on WinCE devices, the defragmentation performs slower than the desktop machine. The amount of time this process takes is proportional to the size of the database. Thus, if you need to pause or cancel this process by clicking the Cancel button. This pauses the process. To continue the defragmentation, select No on the Cancel/Continue prompt. Select Yes on this prompt to stop the process entirely. If you cancel, the database remains in the original state, so your applications still perform normally.
You can use automatic defragmentation, execute defragmentation within your application, or use additional options only available with the command-line tool. The following shows two examples: the first deframents all databases, the second defragments a specific database identified by name.
olmig -defrag all or olmig -defrag dbname
The usage for olmig.exe is as follows:
olmig -dump|-load|-defrag|-restore <dbName>|all [options]
Where:
-dump : dump the database to a dump file (default dbName.odb.dmp). You can separate the functionality of the defragmentation into the dumping and loading. You can perform these functions at separate times, if desired.
-load : load database from the dump file. This completes the defragmentation process and should only be executed after a dump is performed.
-defrag : defragment database, which performs both the dump and load for the database.
-restore : restore a database from a backup. If an error occurred, restore the saved original database.
<dbName> or all : perform the actions on a specfic database or on all Oracle Lite databases on the machine or device.
Options include:
-auto : exit the dialog when upgrade is done. When you invoke the command-line, a GUI is initiated. If you want this screen to exit when finished, provide the -auto option.
-backup : backup the database to dbName.odb.bak
-readonly : connect to a database that is read-only. During a dump only, you can dump a read-only database, such as one that may exist on a CD-ROM. Since the dump normally is written to the same location as the database, you must also provide the directory location and filename for the output. Thus, this option is only valid if used in combination with the -dump and -file options.
-nosingle : do not enter single-user mode. Normally, only a single user can connect to the database while performing a dump, load, or defragmentation. That way, other users are not allowed to update an Oracle Lite database that is currently in the middle of a defragmentation, dump, or load activity. However, if you are performing a dump, you can use this option to enable other users to continue to execute their applications against the database. This cannot be allowed during any load activity.
-log logfile : append messages to the specified file
-file dumpfile : use the specified dump file, instead of the default file
-dot interval : print a dot after processing the number of objects designated by interval; set the interval to 0 to disable this option.
-commit interval : during the load process, this designates the number of rows after which to perform a commit; set the interval to 0 for no commit.
-passwd passwd : specify a connect password for encrypted databases
A Data Source Name (DSN) associates the Oracle Database Lite ODBC Driver with the Oracle Database Lite database that you want to access through the driver. The Oracle Database Lite installation process creates a default DSN, POLITE, for the Oracle Database Lite database. You can also create additional DSNs for the additional Oracle Database Lite databases that you create.
Microsoft provides the ODBC Administrator, a tool for managing the ODBC.INI file and associated registry entries in Windows 2003/XP. The ODBC.INI file and the Windows registry store the DSN entries captured through the ODBC Administrator. Using the ODBC Administrator, you can relate a DSN to the Oracle Database Lite ODBC Driver.
Note:
This document does not provide instructions on using the ODBC Administrator. See the ODBC Administrator tool online help for this information.In the ODBC Administrator, in addition to the DSN, you must specify the parameters listed in Table C-5:
Table C-5 ODBC Administrator DSN Parameters
| DSN Parameter | Description |
|---|---|
|
|
An optional description for the data source. |
|
|
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 the Oracle Database Lite Developer's Guide. The default level is " |
|
|
Commits every database update operation in a transaction when that operation is performed. Autocommit 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 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. |
|
|
|
For example, the DSN entry for POLITE in the ODBC.INI file may contain:
[POLITE] Description=Oracle Lite Data Source Data_Directory=C:\ORANT\OLDB40 Database=POLITE IsolationLevel=Repeatable Read CursorType=Dynamic
Note:
The
ODBC.INI file is available in Windows under %WINDIR% and 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.To add a DSN using the ODBC Administrator:
Start the ODBC Administrator, either by selecting its icon in the Oracle Database Lite program group, or by typing the following at a DOS prompt:
C:\>ODBCAD32
Click Add.
Double-click the Oracle Database Lite nn ODBC Driver, where nn is the release number, from the list of Installed ODBC Drivers.
Next, add the DSN name and define the parameters in the ODBC driver setup dialog. Refer the preceding table for help in defining the parameters.
Create the DSN as explained in Section C.8.1, "Adding a DSN Using the ODBC Administrator".
Add the following line to the new DSN in the ODBC.INI file:
ReadOnly = True
Note:
You can define a DSN which points to a file on a CD-ROM. Simply point the DSN to the CD-ROM drive and directory and provide the file name of the database file. Then modify theODBC.INI file to add the line ReadOnly=True to the data source definition. ODBC programmers can call the following before opening the database to enable this feature (instead of adding the line to the ODBC.INI file):
SQLSetConnectOption( hdbc, SQL_ACCESS_MODE, SQL_MODE_READ_ONLY )
Setting a database file to read-only suppresses the creation of log files. Updates, insertions, deletions, or commits appear to work on the in-memory image of tables. However, when you commit, these changes are not written to the database file. If you exit your application, reconnect, and issue your query, you see your original data.
Description
You can use ODBINFO to find out the version number and volume ID of an Oracle Database Lite database. ODBINFO can also display and set several parameters.
Syntax
To display current information without making any changes use the syntax:
odbinfo [-p passwd]DSN DBName
You can also use:
odbinfo [-p passwd] NONE dbpath\dbanme.odb
For example:
odbinfo -p tiger polite polite
odbinfo NONE c:\orant\oldb40\polite.odb
If your database is encrypted you need to include the password.
Parameters
To set or clear parameters, use one or more "+" or "-" parameter arguments before the DSN or NONE. For example:
odbinfo +reuseoid -pagelog -fsync polite polite
You can use the parameters listed in Table C-6 with the ODBINFO utility:
Table C-6 ODBINFO Parameters
| Parameter | Description |
|---|---|
|
|
By default, a commit backs up modified database pages to |
|
|
Oracle Database Lite generally forces the operating system to write all the modified buffers associated with the database back to disk during a commit. If this option is disabled ( Using Using this option has no effect on applications that seldom update the database. Setting the transaction isolation level to |
|
|
By default, Oracle Database Lite does not reuse the If you use This option is safe for pure relational applications. However, SQL applications that use |
|
|
This option (which is "on" by default) enables run-length compression of objects. Run-length compression takes very little CPU time, so you should only deselect (
Changing this option does not compress or decompress any existing objects in the database. |
This command-line tool validates the structures within the database file and if the database structure is found to be corrupted, lists the errors found in a file designated by the user. The tool checks the following:
Objects - Header information for database objects. Flags are checked for consistency in case the object was moved or compressed. Object length is checked against a valid range. If the object is a BLOB, the object's frames are checked against the volume page bitmap.
Index page entries - Checks that the creation of an index page entry results in the correct number of nodes or list of object identifiers.
Index pages - Checks that all key values on the page are sorted. All objects contained on the page are validated. Page descriptor information such as the number of objects, the number of free bytes, and the number of entries are checked against the actual objects on the page.
Groups - As each page is validated, the group descriptor information is checked against the actual number of pages and objects.
Indexes - All the pages are validated against the btree. The tool also validates all page pointers. All levels of the btree are checked to validate that key values are in the sorted order as a whole. For leaf elements of the btree, all OIDs from the leaf page entries are checked for consistency with the actual group objects.
Syntax
validatedb DSName DBName [-p password] [-l username:password] [-t schemaname.tablename] -file outputfilename
Keywords and Parameters
DSName
The data source name. This can also be NONE if no DSN is present.
DBName
If there is a DSN present, this is the database file name (without the .odb extension) if it is different from the default filename for the DSN. If there is no DSN, then VALIDATEDB uses the current directory unless the full path is specified. If there is a log file in the same directory as the database file, it is also validated.
-p password
Password for an encrypted database.
-l username:password
Optional. Provide the username/password to log into the Oracle Lite database that you are validating with the -l username:password option.
Note:
This is not available on Windows CE.The following details the behavior of this option:
If the database is encrypted, and the encryption password (the -p option) is not supplied, then the password included in the login (-l) option is used as the encryption password.
If you do supply an encryption password in the -p option as well as a login password with the -l option, then the login password is used to verify that the encryption password is correct.
-t schemaname:tablename
Optional.
schema name. The default schema name is used unless this is specified.
table name. The specified table is validated along with all of its indexes. If no table name is specified, the entire database is validated.
-file outputfilename
Optional filename for the text file where all errors and other related information revealed by VALIDATEDB are saved. The default is stdout.
Examples
validatedb polite polite -t emp -file out.txt
You can transfer data between an external file and the Oracle Lite database through either a command-line tool or programmatic APIs, as described in the following sections:
The Oracle Database Lite Load Utility (OLLOAD) is a command-line tool, which 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. Unlike SQL*Loader, OLLOAD does not use a control file in which you supply all data parameters and format information on the command-line.
When loading data, OLLOAD takes an input file that contains one record per line with a separator character between fields. The default field separator is a comma (,). These records can also include fields with values that are quoted strings. The default value is single quote ('). For more information on data parsing, see "Data Parsing".
Before executing this tool, you must stop all applications. This includes the Oracle Database Lite applications, such as the Sync Agent, DM agent, and so on. To stop the Sync Agent, see Section 5.4.2, "Start, Stop, or Get Status for Automatic Synchronization" in the Oracle Database Lite Administration and Deployment Guide.
Loading a Datafile
To load a datafile, use the following syntax.
olload [options] -load dbpath tbl [col1 col2 ...] [<datafile]
Unloading (dump) to an Outfile
olload [options] -dump dbpath tbl [col1 col2 ...] [>outfile]
This section describes keywords and parameters that are available for the OLLOAD utility.
[options]
For a list of options, see Section C.11.1.2.1, "Options".
-load
To use the load utility.
-dump
To use the unload (dump) utility.
dbpath
The path to the Oracle Database Lite (.odb) file.
tbl
The table name. OLLOAD first attempts to find a table name in the user-specified case. If this fails, it searches for the upper-case of the user-specified name.
Note:
The default user isSYSTEM. To specify an OLLOAD operation for another user name's tables, prefix the tbl parameter with the user name and a dot (.).col1 col2
The column names. OLLOAD first attempts to find a column name in the user-specified case. If this fails, it searches for the upper-case of the user-specified name.
[datafile] [outfile]
The source or destination file for the load or unload operations. If you do not specify a datafile or outfile, OLLOAD displays the output on the screen.
This section describes keyword and parameter options that are available for the OLLOAD utility.
-sep character
The field separator. If you do not specify this option, OLLOAD assumes that the separator character is a comma (,).
-quote character
The quote character. If you do not specify this option, OLLOAD assumes that the quote character is a single quote (').
-file filename
Use this option when loading and unloading data to specify the source or destination file name. When loading data, filename specifies the source file to load into Oracle Database Lite. When unloading (dumping) data, it is the destination file for the unloaded data.
Note:
To unload data from Oracle Database Lite and load (or pipe) it to another Oracle Database Lite, do not specify a file name for this option. For a description of sample syntax, see "Examples".-log logfile
Specify this option if you want to produce a log file listing rows that OLLOAD could not insert during load. If you do not specify a log file, loading stops at the first error.
-passwd passwd
The connection password for an encrypted database. You need to supply this password so that loading and unloading can occur.
-nosingle
Specify this option when you do not want to use single user mode. This degrades performance but allows other connections to the database.
-readonly
Specify this option when unloading data from a read-only Oracle Database Lite, for example, one located on a CD-ROM.
-commit count
Use this option if you want OLLOAD to commit after processing a specified number of rows. The default is 10000. OLLOAD prints an asterisk (*) to the screen each time it commits the specified number of rows. To disable the commit operation specify 0.
-mark count
Use this option if you want OLLOAD to print a dot on the screen after processing the specified number of records. The default is 1000. To disable this feature specify 0.
Data Parsing
Table C-7 lists examples for OLLOAD data parsing.
Table C-7 Data Parsing Examples
| Input | Data | Explanation |
|---|---|---|
|
'Redwood Shores, CA' |
Redwood Shores CA |
Enclosing the input string in quotes preserves spaces and punctuations within a string. |
|
'O"Brien' |
O'Brien |
Represent a single quote with its escape sequence, two single quotes. |
|
fire fly |
firefly |
Spaces in data that is not quoted is ignored. |
|
, |
NULL,NULL |
Empty fields are NULL. |
|
1,,3 |
1,NULL,3,NULL |
Empty fields are NULL. |
|
[no row inserted] |
Completely empty lines are ignored. |
If there are more values than database columns, extra values are ignored. Any missing values at the end of the line are set to NULL.
OLLOAD Utility Restrictions
OLLOAD does not support tab-delimited input files and LONG datatypes.
Examples
olload -quote \" -file p_kakaku.csv -load c:\orant\oldb40\polite.odb skkm01
olload -dump c:\orant\oldb40\polite.odb emp empno ename | olload -load myfile.odb myemp
This document describes the Oracle Database Lite Load APIs. Each section of this document presents a different topic. These topics include:
The Oracle Database Lite Load APIs allow 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 information on using the command line tool OLLOAD, see Section C.11.1, "OLLOAD". You can use the API calls presented in this document to make your own customizations.
The Oracle Database Lite Load APIs include:
Section C.11.2.2.2, "Disconnecting from the Database: olDisconnect"
Section C.11.2.2.3, "Deleting All Rows from a Table: olTruncate"
Section C.11.2.2.4, "Setting Parameters for Load and Dump Operations: olSet"
The normal mechanism for unloading and loading a table is as follows:
Declare local variable, DBHandle.
Connect to the database using olConnect.
Optionally, set parameters for load or unload.
Dump or load the data using olDump or olLoad. You may optionally delete all rows from a table by calling olTruncate.
Disconnect from the database using olDisconnect.
Use this API to connect to the database. This is the first API that you have to call. It creates a load and unload context that is used in subsequent APIs to influence the load and unload behavior. This returns an initialized database handle DBHandle.
Syntax
olError olConnect (char *database_path, char *password, DBHandle &dbh);
The arguments for olConnect are listed in Table C-8:
Table C-8 olConnect Arguments
| Argument | Description |
|---|---|
|
|
The full path to the database file (directory path and filename). |
|
|
The password used for the encrypted database, for any other database the password = NULL. |
|
|
The application handle for the current database connection. This allows multiple database connections for one application thread (each connection has a different handle). |
Return Values
(short) integer error code
Values from -1 to -8999 are used for the error codes returned by the database, values from -9000 and below are used for olLoad-specific error codes.
Disconnects from the database.
Syntax
olError olDisconnect (DBHandle dbh);
The arguments for olDisconnect are listed in Table C-9:
Return Value
(short) integer error code
This API can be used to delete all rows from an existing table.
Note:
Records removed from the server through a truncate command will not be removed from the client unless a complete refresh is triggered. The truncate command is considered a DDL operation. Consequently, the necessary DML triggers do not fire and therefore the operations are not logged for fast refresh.Syntax
olError olTruncate (DBHandle dbh, char* table );
The arguments for olTruncate are listed in Table C-10:
Table C-10 olTruncate Arguments
| Argument | Description |
|---|---|
|
|
The current application handle. |
|
|
The name of the table in the form: owner_name.table_name. where owner_name is the name of the owner of the table. |
Return Value
(short) integer error code
This is an optional API. This sets optional parameters for load and unload.
Syntax
olError olSet (DBHandle dbh, char * parameter_name, char *parameter_value);
The arguments for olSet are listed in Table C-11:
Table C-11 olSet Arguments
| Argument | Description |
|---|---|
|
|
The current application handle. |
|
|
The name of the given parameter. This is not case sensitive. See Section C.11.2.3.2, "Parameters" for a list of parameter names and their default values. |
|
|
The value to be set. This is not case sensitive for most parameters. |
Return Value
(short) integer error code
OlLoad loads data from a file into a table using current parameter settings.
Syntax
olError olLoad (DBHandle dbh, char *table, char *file);
The arguments for olLoad are listed in Table C-12:
Table C-12 olLoad Arguments
| Argument | Description |
|---|---|
|
dbh |
The current application handle. |
|
table |
The table information in the form: where This allows you to load and dump certain columns instead of the entire table. If the entire table is to be dumped, the column list need not be specified. |
|
file |
The path to the file from which loading takes place. |
Note:
If table = NULL,olLoad tries to find the table description in the file header.Return Value
(short) integer error code
OlDump dumps data from a table into a file using current parameter settings.
Syntax
olError olDump (DBHandle dbh, char *table, char *file);
The arguments for olDump are listed in Table C-13:
Table C-13 olDump Arguments
| Argument | Description |
|---|---|
|
|
The current application handle. |
|
|
The table information in the same form as |
|
|
The file to which dump data is written. |
Return Value
(short) integer error code
The declarations for the DBHandle, parameter constants and flags, and error message codes are given in the file olloader.h in the ORACLE_HOME\Mobile\SDK\include directory. For compilation of your product include olloader.h in your main source file.
Linking use the file olloader40.dll and the library file olloader40.lib. Include these files in your project settings.
The Oracle Database Lite Load APIs support three file formats FIXEDASCII, BINARY and CSV. Each file contains an optional header followed by zero or more rows of data.
The header has the following format (comments are in bold):
$$OL_BH$$ [begins header] VERSION=xx.xx.xx.xx [version number] TABLE=T1(C1, C2, ...)... [table name with list of column names dumped] FILEFORMAT=FIXEDASCII SEPARATOR=, [any other parameters in the parameter list can be listed here] $$OL_EH$$ [ends header]
The following is a header example:
$$OL_BH$$ VERSION=01.01.01.01 TABLE=T1(EMPNO,SALARY) FILEFORMAT=BINARY BITARRAY=TRUE HEADER=TRUE RDONLY=FALSE LOGFILE= COMMITCOUNT=-1 NOSINGLE=TRUE $$OL_EH$$
The header lines can be in any order and all lines except $$OL_BH$$ and $$OL_EH$$ can be considered optional. Although, during the dump, if the header flag is on, table information and all parameter settings are dumped into the header.
When executing load, parameter information in the header overwrites current parameter settings. If the table argument in olLoad is NULL, the table name and list of columns in the header prevails, otherwise the table argument of olLoad prevails over the header.
Header file parameters listed in Table C-14 are not case sensitive.
Table C-14 Parameters
| Parameter | Description |
|---|---|
|
|
Input and output file format. The following formats are supported:
These key word values are not case sensitive. |
|
|
The separator between the values (one character), comma by default. |
|
|
The quote character for the string datatype values in the file, single quote (') by default. |
|
|
The log file name. NULL by default (no log file produced and loading stops at the first error). |
|
|
FALSE for single user mode (the default), or TRUE for no single user mode. |
|
|
FALSE (the default). TRUE to dump the data from read-only database (such as CD-ROM). |
|
|
The number of rows processed after which |
|
|
FALSE (the default). TRUE to create a header in the beginning of the file during |
|
|
TRUE (the default) to support writing and reading nulls in binary format. During the dump, a bit array with the null information is dumped before each row. For FALSE |
|
|
TRUE (the default) when trying to read or write nulls |
|
|
The string for which date and timestamp columns should be written into the file and read from the file in FIXED ASCII and CSV formats. Such formats as "YYYYMMDD", "YYYY-MM-DD", and "YYYY/MM/DD" are supported. The default value is empty string (which can also be set using NULL), and the default date format is "YYYY-MM-DD". (In Oracle mode, date is treated the same as timestamp so that the date format is the default timestamp format which is "YYYY-MM-DD HH:MM:SS.SSSSSS".) |
The data format can be comma separated value (CSV), fixed ASCII, or binary. The following cases apply:
CSV Format: Each row of the table is represented as a separate line in the file. Each line is separated by a carriage return and a line feed character on the Windows platform. Each value in the row is separated by a separator character which by default is a comma.
Each value is also quoted by a quote character. Nulls are represented by an empty quoted string " ". The number of quoted strings in the file should be the same as the number of columns in the table, olLoad gives an error otherwise.
FixedAscii Format: Each row of the table is represented as a separate line in the file. Each line is separated by a carriage return and a line feed character on the Windows platform. Each line is of the same size. The datatype of a column governs its format or representation in the file. Nulls are represented by a string of n '\0' (null) characters, where n is the fixed size of the field. Table C-15 describes data representation for each data type. The total record length for each line in the file should be the same as the sum of field lengths (precision) of each column, otherwise olLoad returns an error.
Table C-15 Datatypes
| Datatype | Description |
|---|---|
|
|
Length of the field in n characters. Data is left aligned and padded with blanks on the right. |
|
|
Length of the field in n characters. Data is left aligned. It is padded with a null byte ('\0'). |
|
|
The default mode: length of the field is p+1 characters if scale s is zero or is not present. Otherwise, the length of the field is (p+2) characters. The value is right aligned in the output field. Format is optional negative sign, followed by zeros if required, followed by significant digits. If there is no negative sign, then '0' instead, for example, Number(5,2) 12.3 -> ' 012.30' -12.3 -> '-012.30' 1.23 -> ' 001.23' -1.23 -> '-001.23' The custom mode: the field length is one less: p if scale is not present, or zero and p+1 otherwise. The actual number stored in the file is of type NUMERIC(p-1, s). Correspondingly, |
|
|
The same as NUMERIC(p,s). |
|
|
Length of the field is 11 characters. A negative sign or space followed by 10 digits. Leading digits are filled with zeros. |
|
|
Field length is 6 characters. Minus sign or space followed by 5 digits. |
|
|
Field length is 23 characters. In Oracle mode, it is minus sign or space, followed by leading zeroes, followed by some number of digits, followed by dot, followed by some number of digits. For example: 0 -> ' 0000000000000000000000' -12.34 -> '-0000000000000000012.34' In SQL92 mode the E (exponent) is always present and there is only 1 digit before the decimal point. For example: 0 -> ' 00000000000000000000E0' -12.34 -> '-000000000000001.234E10' |
|
|
The same format as for double precision except that the total field length is only 16 characters instead of 23. |
|
|
Field length is 23 characters. Minus sign or space followed by 22 characters which are digits, dot, or E, floating point number followed by E, followed by the exponent digits. In Oracle mode, if the number is small enough to fit in the field without using the exponent, E is not used. In SQL92 mode, E is always used. There is always one meaningful digit before the floating point, except 0. For example, in SQL92 mode: 0 -> ' 00000000000000000000E0' -1.79E10 -> '-0000000000000001.79E10' 12 -> ' 00000000000000001.2E10' For example, in Oracle mode: 1.2E75 -> ' 00000000000000001.2E75' -1.33333 -> '-0000000000000001.33333' -1.79E10 -> '-0000000000017900000000' |
|
|
In SQL92 mode: YYYY-MM-DD, 10 characters long, for example: October 1, 1999 -> 1999-10-01 In Oracle mode the date is dumped as timestamp. If it is not the default date format parameter, the date format corresponds to the specified date format string, for example: DATEFORMAT = "YYYYMMDD" October 1, 1999 -> 19991001 |
|
|
HH:MM:SS, 8 characters long, for example: 5:01:58 p.m. is 17:01:58 |
|
|
Date format, space, time format, dot, 6 digits after dot (precision of microseconds), total length of 26 characters: YYYY-MM-DD HH:MM:SS.SSSSSS If it is not the default date format parameter, the timestamp format corresponds to the specified date format string. If no time is specified in the date format string, the time information in the timestamp is omitted when dumping into a file. Note: |
Currently olLoad does not support the following features:
Columns of the datatype Interval, Time with time zone, Timestamp with time zone, BLOB, and CLOB.
Binary data is not supported.
The only "var" type supported is varchar.
|
 Copyright © 1997, 2010, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|