Skip Headers
Oracle® Database Lite Developer's Guide
10g (10.0.0)
Part No. B13788-01
  Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
Next
Next
 

B Oracle Database Lite Load Application Programming Interfaces (APIs)

This document describes the Oracle Database Lite Load APIs. Each section of this document presents a different topic. These topics include:

B.1 Overview

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 the Oracle Database Lite Tools and Utilities Guide. You can use the API calls presented in this document to make your own customizations.

B.2 Oracle Database Lite Load APIs

The Oracle Database Lite Load APIs include:

The normal mechanism for unloading and loading a table is as follows:

  1. Declare local variable, DBHandle.

  2. Connect to the database using olConnect.

  3. Optionally, set parameters for load or unload.

  4. Dump or load the data using olDump or olLoad. You may optionally delete all rows from a table by calling olTruncate.

  5. Disconnect from the database using olDisconnect.

B.2.1 Connecting to the Database: olConnect

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 B-1:

Table B-1 olConnect Arguments

Argument Description
database_path The full path to the database file (directory path and filename).
password The password used for the encrypted database, for any other database the password = NULL.
dbh 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.

B.2.2 Disconnecting from the Database: olDisconnect

Disconnects from the database.

Syntax

olError olDisconnect (DBHandle dbh);

The arguments for olDisconnect are listed in Table B-2:

Table B-2 olDisconnect Arguments

Argument Description
dbh The current application handle.

Return Value

(short) integer error code

B.2.3 Deleting All Rows from a Table: olTruncate

This API can be used to delete all rows from an existing table.

Syntax

olError olTruncate (DBHandle  dbh, char* table );

The arguments for olTruncate are listed in Table B-3:

Table B-3 olTruncate Arguments

Argument Description
dbh The current application handle.
tablename 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

B.2.4 Setting Parameters for Load and Dump Operations: olSet

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 B-4:

Table B-4 olSet Arguments

Argument Description
dbh The current application handle.
parameter_name The name of the given parameter. This is not case sensitive. See Section B.3.2, "Parameters" for a list of parameter names and their default values.
parameter_value The value to be set. This is not case sensitive for most parameters.

Return Value

(short) integer error code

B.2.5 Loading Data: olLoad

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 B-5:

Table B-5 olLoad Arguments

Argument Description
dbh The current application handle.
table The table information in the form: owner_name.table_name(col1,col2,...)

where col1,col2,... is the list of column names to load.

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

B.2.6 Dumping Data: olDump

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 B-6:

Table B-6 olDump Arguments

Argument Description
dbh The current application handle.
table The table information in the same form as olLoad.
file The file to which dump data is written.

Return Value

(short) integer error code

B.2.7 Compiling

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.

B.2.8 Linking

Linking use the file olloader40.dll and the library file olloader40.lib. Include these files in your project settings.

B.3 File Format

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.

B.3.1 Header Format

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.

B.3.2 Parameters

Header file parameters listed in Table B-7 are not case sensitive.

Table B-7 Parameters

Parameter Description
FILEFORMAT Input and output file format. The following formats are supported:
  • FixedASCII - text file with fixed field width for each datatype.

  • CSV – comma separated values format.

  • Binary - binary file format.

These key word values are not case sensitive.

SEPARATOR The separator between the values (one character), comma by default.
QUOTECHAR The quote character for the string datatype values in the file, single quote (') by default.
LOGFILE The log file name. NULL by default (no log file produced and loading stops at the first error).
NOSINGLE FALSE for single user mode (the default), or TRUE for no single user mode.
READONLY FALSE (the default). TRUE to dump the data from read-only database (such as CD-ROM).
COMMITCOUNT The number of rows processed after which olLoad, olDump, and olTruncate commit. The default value is -1, not to commit at all. Value 0 commits at the end of the operation, and values above 0 commit after the specified number of rows.
HEADER FALSE (the default). TRUE to create a header in the beginning of the file during olDump.
BITARRAY 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 olDump provides an error trying to write nulls in binary.
NONULL TRUE (the default) when trying to read or write nulls olLoad and olDump return an error. When the flag is set to FALSE nulls are supported, including binary format since the default BITARRAY value is TRUE.
DATEFORMAT 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".)

B.3.3 Data Format

The data format can be comma separated value (CSV), fixed ASCII, or binary. The following cases apply:

B.3.3.1 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.

B.3.3.2 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 B-8 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 B-8 Datatypes

Datatype Description
CHAR(n) Length of the field in n characters. Data is left aligned and padded with blanks on the right.
VARCHAR(n) Length of the field in n characters. Data is left aligned. It is padded with a null byte ('\0').
NUMERIC(p,s) 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, olDump gives an error trying to insert a number within the range of NUMERIC(p, s), but out of the range of NUMERIC(p-1, s). Therefore, the first character in the NUMERIC field must be '0' or '-'; olLoad gives an error otherwise.

DECIMAL(p,s) The same as NUMERIC(p,s).
INTEGER Length of the field is 11 characters. A negative sign or space followed by 10 digits.

Leading digits are filled with zeros.

SMALLINT Field length is 6 characters. Minus sign or space followed by 5 digits.
FLOAT 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'

REAL The same format as for double precision except that the total field length is only 16 characters instead of 23.
DOUBLE PRECISION 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'

DATE 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

TIME HH:MM:SS, 8 characters long, for example:

5:01:58 p.m. is 17:01:58

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


B.4 Limitations

Currently olLoad does not support the following features: