B
Oracle Lite Utilities

B.1 Running Command Line Utilities on a CE Device

For PocketPCs, running command line applications requires executing a sequences of button clicks which are slightly different for different models.

B.2 CREATEDB

Description

Utility for creating a database.

Syntax

CREATEDB DataSourceName DatabaseName [VolID]

Key Words 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 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.

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.

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.

Examples

createdb polite db1

createdb polite c:\testdir\db2.odb 300

B.3 DECRYPDB

Description

This tool allows you to decrypt an encrypted Oracle Lite database. See Section B.4, "ENCRYPDB" for more information.

Syntax

DECRYPDB DSN | NONE DBName [Password

Key Words and Parameters

DSN

Data source name of the Oracle Lite database that you want to decrypt. If you specify NONE, DBName has to be entered with the full path name (without the .ODB extension).

DBName

Name of the database to be decrypted. If DSN was specified as NONE, DBName must be entered with the full path name.

Password

Optional. The password used previously to encrypt the Oracle Lite database. If you do not enter the password, DECRYPDB prompts you to enter it.

Comments

An Oracle Lite database cannot be decrypted if there is any open connection to the database.

If you call this utility from another program, the values which can be returned are listed in Table B-2:

Table B-2 ECRYPDB Return Codes

Return Code	Description
EXIT_SUCCESS	Success.
EXIT_USAGE	Command line arguments are not properly used or are in error.
EXIT_PATH_TOO_LONG	Path is too long.
EXIT_SYSCALL	I/O error while making new decrypted copy on disk.
EXIT_BAD_PASSWD	Incorrect password supplied.

See the comments in Section B.4, "ENCRYPDB" for more information.

B.4 ENCRYPDB

Description

This tool allows you to encrypt an Oracle Lite database with a password and to change a database password. The password prevents unauthorized access to the database and encrypts the database, so that the data stored in the database files cannot be interpreted. See Section B.3, "DECRYPDB" for more information.

ENCRYPDB uses CAST5 encryption, which is a 128-bit, DES compliant encryption scheme.

Syntax

ENCRYPDB DSN | NONE DBName [New_Passwdor [Old_Passwdor]]

Key Words and Parameters

DSN

Data source name of the Oracle Lite database 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 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_Passwd and Old_Passwd

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.

Comments

If you call this utility from another program, the values which can be returned are listed in Table B-3:

Table B-3 ENCRYPDB Return Codes

Return Code	Description
EXIT_SUCCESS	Success.
EXIT_USAGE	Command line arguments are not properly used or are in error.
EXIT_PATH_TOO_LONG	Path is too long.
EXIT_SYSCALL	I/O error while making new encrypted copy on disk.
EXIT_BAD_PASSWD	Incorrect password supplied.

The default Oracle Lite database (POLITE.ODB) is not encrypted. After encrypting an Oracle Lite database every user that attempts to establish a connection to the encrypted Oracle Lite database must provide the valid password. If the password is not provided, Oracle Lite returns an error. An Oracle Lite database cannot be encrypted if there are any open connections to the database.

You should consider the following when encrypting and decrypting Oracle Lite databases:

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

• Do not run ENCRYPDB while other Oracle Lite applications are running. You will receive an error if there are other applications connected to the database file.

• After encrypting the database, you must include the password in the connect string to connect to the database.

• 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 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 Lite ODBC driver to prompt for the password at runtime.

• All sample applications provided with this release of Oracle 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 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.

B.5 Mobile SQL

Mobile SQL is an application that allows the user to execute SQL statements against the local database. It is both a developers tool and a code example. It allows you to access functionality provided by the ODBC and Oracle Lite OKAPI interfaces of the underlying Oracle Lite database engine.

B.6 ODBINFO

Description

You can use ODBINFO to find out the version number and volume ID of an Oracle 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\dbname.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.

To set or clear options, use one or more "+" or "-" option arguments before the DSN or NONE. For example:

odbinfo +reuseoid -pagelog -fsync polite polite

Parameters

You can use the parameters listed in Table B-4 with the ODBINFO utility:

Table B-4 ODBInfo Parameters

Parameter	Description
pagelog	By default, a commit backs up modified database pages to <filename>.plg before actually writing the changes to <filename>.ODB. If an application or the operating system experiences a failure during a commit, the transaction is cleanly rolled back during the next connect. If -pagelog is specified, no backup is created and the database can become corrupted if a failure occurs.
fsync	Oracle 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 (-fsync), the operating system can keep the changes in memory until a later time. If the system (but not the application) crashes before the buffers are flushed, the database can become corrupted. Using odbinfo -fsync -pagelog improves the performance of applications that use many small transactions (with autocommit on) or ones with massive updates. However, if the database is corrupted, there is no straightforward way to repair it or recover the data. Therefore these two options should only be cleared during initial loading of the database, if (1) the .ODB file is backed up on regular basis, or (2) the data in the database can be recovered from some other source. Using this option has no effect on applications that seldom update the database. Setting the transaction isolation level to SINGLE USER has more impact in this case.
reuseoid	By default, Oracle Lite does not reuse the ROWID of any row that exists in a table until the table is dropped. The "Slot Deleted" error is returned when accessing a deleted object. This uses two bytes of storage for each deleted object, causing performance and disk space usage to degrade over time if rows are constantly inserted and deleted. If you use odbinfo +reuseoid, new rows can reuse ROWIDs of previously deleted rows. However, this may not free all the space in a table that already has many deleted objects. For best results, you should set this option immediately after you create your database. This option is safe for pure relational applications. However, SQL applications that use ROWID and JAC/OKAPI applications that use direct pointers between objects need to verify that all references to an object are set to NULL before the object is deleted. Otherwise, dangling references may eventually point to some other, unrelated object.
compress	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 (-compress) this option if: Operating system-level file compression is used, such as DriveSpace or a NTFS compressed attribute. In this case not compressing the same data twice provides a better compression ratio. Most objects in the database are frequently updated to a highly compressible state (for example, all columns set to NULL), and the data cannot be compressed well (such as binary columns with random data). In these cases, using this option (+compress) can result in highly fragmented tables. Changing this option does not compress or decompress any existing objects in the database.

B.7 REMOVEDB

Description

Utility for deleting a database.

Syntax

REMOVEDB DataSourceName DatabaseName

Key Words and Parameters

DataSourceName

Data source name of the database you want to remove.

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 polite c:\testdir\db2.odb