10 JDBC Programming

This chapter discusses the Oracle Database Lite support for JDBC programming. It includes the following topics:

Section 10.1, "JDBC Compliance"
Section 10.2, "JDBC Environment Setup"
Section 10.3, "JDBC Drivers to Use When Connecting to Oracle Database Lite"
Section 10.4, "DataSource Connection"
Section 10.5, "Java Datatypes and JDBC Extensions"
Section 10.6, "Limitations"
Section 10.7, "New JDBC 2.0 Features"

10.1 JDBC Compliance

Oracle Database Lite provides a native JDBC driver that allows Java applications to communicate directly with the Oracle Database Lite object relational database engine. The Oracle Database Lite implementation of JDBC complies with JDBC 1.2. In addition, Oracle Database Lite provides certain extensions specified by JDBC 2.0, which are compatible with the Oracle database JDBC implementation. For a complete JDBC reference, see the Sun Microsystems Web site.

10.2 JDBC Environment Setup

For your Java applications using the client/server model, include the olite40.jar, which is located in OLITE_HOME/bin, in the system CLASSPATH on the server machine and in the user CLASSPATH on the client machine.

When using the Oracle Lite JDBC driver in your OC4J application, use the default classloader instead of a per-application classloader, which many J2EE containers use. Ensure that the olite40.jar is in the OC4J CLASSPATH when OC4J initiates and not in the /lib subdirectory of your application WAR file.

Note:

For more information on how to start the Multiuser Oracle Database Lite Database Service, see Section 2.5, "Oracle Database Lite Multi-User Service".

10.3 JDBC Drivers to Use When Connecting to Oracle Database Lite

Note:

JDK 1.4.2 or higher is required to connect to Oracle Database Lite.

Oracle Database Lite supports Type 2 and Type 4 drivers.

The Type 2 driver uses native code on the client side through which it interfaces with the Oracle Database Lite ODBC driver.
The Type 4 JDBC driver is a pure Java driver and uses the Oracle Database Lite network protocol to communicate with the Oracle Database Lite service. Before using this driver, ensure that you start Oracle Database Lite. Any Java applet can use the Type 4 JDBC driver.

The supported Type 2 and Type 4 drivers are described in the following sections:

Section 10.3.1, "Type 2 Driver"
Section 10.3.2, "Type4 (Pure Java) Driver Connection URL Syntax"

10.3.1 Type 2 Driver

For most applications, use the type 2 driver for connecting to the database. You can use the type 2 driver to connect either to the local Oracle Lite database or to the server where a Multi-User Service is managing the Oracle Lite databases.

To connect to the local Oracle Lite database, use the following URL syntax:
```
jdbc:polite[:uid / pwd]:localDSN[;key=value]*
```
where the localDSN is the DSN name for the local Oracle Lite database (the ODB file on the local machine) and the optional key=value pairs are listed in Table 10-1.

The following example retrieves a connection to the local Oracle Lite database, where the DSN name is polite:
```
DriverManager.getConnection("jdbc:polite:polite","system","admin");
```
To access the Oracle Lite database on a remote host where a Multi-User Service or Branch Office is located, use the following URL syntax:
```
jdbc:polite[:uid / pwd]@[host]:[port]:serverDSN [;key=value]*
```
where the host, port, and serverDSN identify the host, port and DSN of the remote host where the Oracle Lite database (the ODB file on the local machine) and the multi-user service is located. The optional key=value pairs are listed in Table 10-1.

For more information on how to install and start the Multiuser Oracle Database Lite Service, refer to Section 2.5, "Oracle Database Lite Multi-User Service".

You can provide additional configuration information in the JDBC driver URL within key-value pairs, as specified in Table 10-1, each of which are separated by a semi-colon. The information specified within the key-value pairs always overrides the information that is specified in the URL.

Table 10-1 Key/Value Pairs for JDBC Connect URL

Argument	Description
`jdbc`	Identifies the protocol as JDBC.
`polite`	Identifies the subprotocol as `polite`.
uid / pwd	The optional user ID and password for Oracle Database Lite. If specified, this overrides the specification of a username and password defined in the UID and PWD arguments. If the database is encrypted, you must include the password in the key-value pair.
host	The name of the machine that hosts the Multi-User Service or Branch Office and on which the Oracle Database Lite service `olsv2040.exe` runs. This host name is optional. If omitted, it defaults to the local machine on which the JDBC application runs.
port	The port number at which the Multi-User or Branch Office service listens. The port number is optional and if omitted defaults to port "1531".
dsn	Identifies the data source name (DSN) entry in the `odbc.ini` file. This entry contains all the necessary information to complete the connection to the server. Note: For a JDBC program, you need not create a DSN if you have supplied all the necessary values for the data directory and database through key=value pairs. On the windows platform, you can use the ODBC administrator to create a DSN. For more information, see Section 2.5.1.6, "Creating DSNs".
DataDirectory=	Directory in which the `.odb` file resides.
Database=	Name of database as given during its creation.
IsolationLevel=	Transaction isolation level: READ COMMITTED, REPEATABLE READ, SERIALIZABLE or SINGLE USER. For more information on isolation levels, see Section 15.2, "What Are the Transaction Isolation Levels?".
Autocommit=	Commit behavior, either `ON` or `OFF`.
CursorType=	Cursor behavior: DYNAMIC, FORWARD ONLY, KEYSET DRIVEN or STATIC. For more information on cursor types, see Section 15.4, "Supported Combinations of Isolation Levels and Cursor Types".
UID=	User name
PWD=	Password

Example of Using JDBC Type 2 Connection for Local Oracle Lite Database

String ConnectMe=("jdbc:polite:SCOTT/tiger:polite; DataDirectory=<ORACLE_HOME>;Database=polite;IsolationLevel=SINGLE USER; Autocommit=ON;CursorType=DYNAMIC")
 
try 
   {Class.forName("oracle.lite.poljdbc.POLJDBCDriver")
   Connection conn = DriverManager.getConnection(ConnectMe)
   }
catch (SQLException e)
{ 
...
}

Example of Using Type 2 in Multi-User Service Situation

An example of this type of connection is given below.

try {
Connection conn = DriverManager.getConnection(
   "jdbc:polite@yourhostname
        ;DataDirectory=<ORACLE_HOME>
        ;Database=polite
        ;IsolationLevel=SINGLE USER
        ;Autocommit=ON
        ;CursorType=DYNAMIC", "Scott", "tiger")
} 
catch (SQLException e)
{
}

10.3.2 Type4 (Pure Java) Driver Connection URL Syntax

Use the JDBC Type 4 driver for any pure Java application that uses the Multi-User or Branch office services. The URL syntax for the type 4 driver as follows:

jdbc:polite4[:uid/pwd]@[host]:[port]:serverDSN[;key=value]*

The parameter polite4 indicates that the JDBC type 4 driver is being used. For the rest of the parameters, see the definitions of those parameters for the type 2 driver, as described in Table 10-1.

Note:

The URL works with the Oracle Database Lite service only.

10.4 DataSource Connection

In JDBC 2.0, the DataSource object is an alternative to the DriverManager facility. The DataSource object is the preferred method for retrieving a connection and is typically registered with a naming service based on the JNDI API. A driver that is accessed through a DataSource object does not register itself with the DriverManager.

The Oracle Database Lite JDBC driver contains the basic DataSource implementation to produce a standard Connection object. The retrieved connection is identical to a connection obtained through the DriverManager.

Oracle Database Lite implements javax.sql.DataSource interface with the POLJDBCDataSource class in the oracle.lite.poljdbc package.

As with any class that implements the DataSource interface, the POLJDBCDataSource object defines properties for connecting to a specific database. In addition to the standard DataSource properties, the POLJDBCDataSource class has one additional property, which is a String to define the URL of the database connection string, as described in Section 10.3.1, "Type 2 Driver" and Section 10.3.2, "Type4 (Pure Java) Driver Connection URL Syntax".

The URL can include username and password, which then overrides any previous individual property settings. The following URL specifies a username and password of system/manager:
```
jdbc:polite4:system/manager@::polite
```
You must have a username and password defined either in the URL or in the getConnection method. If defined in both places, then the username and password in the getConnection takes precedence over the URL definitions.

See the JDBC example—JDBCEXJSR169—on the CD for how to use the POLJDBCDataSource object.

10.5 Java Datatypes and JDBC Extensions

The Oracle Database Lite JDBC driver supports JDBC 1.2 and provides extensions that support certain features defined in JDBC 2.0. The extensions include support for BLOB (large binary object) and CLOB (large character object) datatypes and scrollable result sets. The Oracle Database Lite JDBC extensions are compatible with the Oracle database JDBC implementation. However, Oracle Database Lite does not support the following Oracle database JDBC datatype extensions: Array, Struct, or REF.

The following sections list and describe the Oracle Database Lite datatypes and data access extensions. For details regarding function syntax and call parameters, see the Sun Microsystems Java 2 specification at the Sun Javasoft website.

Section 10.5.1, "Java Datatypes"
Section 10.5.2, "Datatype Extensions"
Section 10.5.3, "Data Access Extensions"

10.5.1 Java Datatypes

Oracle Database Lite performs type conversions between Java and Oracle datatypes as indicated by the following table. Table 10-2 lists the Java datatypes and the corresponding SQL datatypes that result from the type conversion.

Table 10-2 Datatype Conversions

Java Datatype	SQL Datatype
`byte[], byte[][], Byte[]`	`BINARY, RAW, VARBINARY, BLOB`
`boolean, Boolean`	`BIT`
`String, String[]`	`CHAR, VARCHAR, VARCHAR2, CLOB`
`short, short[], short[][], Short, Short[]`	`SMALLINT`
`int,int[], int[][], Integer, Integer[]`	`INT`
`float, float[], float[][], Float, Float[]`	`REAL`
`double, double[], double[][], Double, Double[]`	`DOUBLE, NUMBER` (without precision)
`BigDecimal, BigDecimal[]`	`NUMBER(n)`
`java.sql.Date, java.sql.Date[]`	`DATE`
`java.sql.Time, java.sql.Time[]`	`TIME`
`java.sql.Timestamp, java.sql.Timestamp[`]	`TIMESTAMP`
`java.sql.Connection`	Default JDBC connection to database

10.5.2 Datatype Extensions

BLOBs and CLOBs store data items that are too large to store directly in a database table. Rather than storing the data, the database table stores a locator that points to the location of the actual data. BLOBs contain a large amount of unstructured binary data items and CLOBs contain a large amount of fixed-width character data items (characters that require a fixed number of bytes per character).

You can select a BLOB or CLOB locator from the database using a standard SELECT statement. When you select a BLOB or CLOB locator using SELECT, you acquire only the locator for the large object, not the data itself. Once you have the locator, however, you can read data from or write data to the large object using access functions.

Table 10-3 lists the methods included in the Oracle Database Lite BLOB class and their descriptions:

Table 10-3 Methods in the Oracle Database Lite BLOB Class

Function	Description
`length`	Returns the length of a BLOB in bytes.
`getBinaryOutputStream`	Returns BLOB data.
`getBinaryStream`	Returns a BLOB instance as a stream of bytes.
getBytes	Reads BLOB data, starting at a specified point, into a buffer.
`getConnection`	Returns the current connection.
`isConvertibleTo`	Determines if a BLOB can be converted to a particular class.
`putBytes`	Writes bytes to a specified point in the BLOB data.
`makeJdbcArray`	Returns the JDBC array representation of a BLOB.
`toJdbc`	Converts a BLOB to a JDBC class.
`trim`	Trims to length.

Table 10-4 lists the methods included in the Oracle Database Lite CLOB class and their descriptions.

Table 10-4 Methods in the Oracle Database Lite CLOB Class

Function	Description
`length`	Returns the length of a CLOB in bytes.
`getSubString`	Retrieves a substring from a specified point in the CLOB data.
`getCharacterStream`	Returns CLOB data as a stream of Unicode characters.
`getAsciiStream`	Returns a CLOB instance as an ASCII stream.
`getChars`	Retrieves characters from a specified point in the CLOB data into a character array.
`getCharacterOutputStream`	Writes CLOB data from a Unicode stream.
`getAsciiOutputStream`	Writes CLOB data from an ASCII stream.
`getConnection`	Returns the current connection.
`putChars`	Writes characters from a character array to a specified point in the CLOB data.
`putString`	Writes a string to a specified point in the CLOB data.
`toJdbc`	Converts a CLOB to a JDBC class.
`isConvertibleTo`	Determines if a CLOB can be converted to a particular class.
`makeJdbcArray`	Returns a JDBC array representation of a CLOB.
`trim`	Trims to length.

10.5.3 Data Access Extensions

Oracle Database Lite provides access functions to set and return values of the CLOB and BLOB datatypes. In addition, stream classes provide functions that enable stream-format access to large objects.

The large object access functions are located in the OraclePreparedStatement, the OracleCallableStatement, and the OracleResultSet class.

Table 10-5 lists the data access functions included in the OracleResultSet class.

Table 10-5 Data Access Functions in the OracleResultSet Class

Function	Description
`getBLOB`	Returns a locator to BLOB data.
`getCLOB`	Returns a locator to CLOB data.

The stream format access classes are POLLobInputStream, POLLobOutputStream, POLClobReader, and POLClobWriter.

The POLLobInputStream class includes the following data access function.

Function	Description
`read`	Reads from a large object into an array.

The POLLobOutputStream class includes this data access function.

Function	Description
`write`	Writes from an output stream into a large object.

The POLClobReader class extends the class java.io.reader. It includes these data access functions.

Function	Description
`read`	Reads characters from a CLOB into a portion of an array.
`ready`	Indicates whether a stream is ready to read.
`close`	Closes a stream.
`markSupported`	Indicates whether the stream supports the `mark` operation.
`mark`	Marks the current position in the stream. Subsequent calls to the `reset` function reposition the stream to the marked location.
`reset`	Resets the current position in the stream to the marked location. If the stream has not been marked, this function attempts to reset the stream in a way appropriate to the particular stream, such as by repositioning it at its starting point.
`skip`	Skips characters in the stream.

The POLClobWriter class extends the class java.io.writer. It includes these data access functions:

Function	Description
`write`	Writes an array of characters to the output stream.
`flush`	Writes any characters in a buffer to their intended destination.
`close`	Flushes and closes the stream.

10.5.3.1 Reading from a BLOB Sample Program

The following sample uses the getBinaryStream method to read BLOB data into a byte stream. It then reads the byte stream into a byte array, and returns the number of bytes read.

// Read BLOB data from BLOB locator.

InputStream byte_stream = my_blob.getBinaryStream();

byte [] byte_array = new byte [10];

int bytes_read = byte_stream.read(byte_array);

...

10.5.3.2 Writing to a CLOB Sample Program

The following sample reads data into a character array, then uses the getCharacterOutputStream method to write the array of characters to a CLOB.

java.io.Writer writer;

char[] data = {'0','1','2','3','4','5','6','7','8','9'};


// write the array of character data to a CLOB 

writer = ((CLOB)my_clob).getCharacterOutputStream();

writer.write(data);

writer.flush();

writer.close();

...

10.6 Limitations

If data truncation occurs during a write, a SQL data truncation exception is thrown. A SQL data truncation warning results if data truncation occurs during a read.

The Oracle Database Lite JDBC classes and the JDBC 2.0 classes use the same name for certain datatypes (for example, oracle.sql.Blob and java.sql.Blob). If your program imports both oracle.sql.* and java.sql.*, attempts to access the overlapping classes without fully qualifying their names may result in compiler errors. To avoid this problem, use one of the following steps:

Use fully qualified names for BLOB, CLOB, and data classes.
Import the class explicitly (for example, import oracle.sql.Blob).

Class files always contain fully qualified class names, so the overlapping datatype names do not cause conflicts at runtime.

10.7 New JDBC 2.0 Features

This section describes JDBC 2.0 methods or interfaces that are supported by the Oracle Database Lite JDBC driver. Topics include:

Section 10.7.1, "Interface Connection"
Section 10.7.2, "Interface Statement"
Section 10.7.3, "Interface ResultSet"
Section 10.7.4, "Interface Database MetaData"
Section 10.7.5, "Interface ResultMetaData"
Section 10.7.6, "Interface PreparedStatement"

10.7.1 Interface Connection

This section describes the JDBC 2.0 Interface methods that are implemented by the Oracle Database Lite JDBC driver.

10.7.1.1 Methods

Statement

createStatement(int resultSetType, int resultSetConcurrency)

Creates a statement object that generates ResultSet objects with the given type and concurrency.

Map

getTypeMap()

Gets the TypeMap object associated with this connection.

CallableStatement

prepareCall(String sql, int resultSetType, int resultSetConcurrency)

Creates a CallableStatement object that generates ResultSet objects with the given type and concurrency.

PreparedStatement

prepareStatement(String sql, int resultSetType, int resultSetConcurrency)

Creates a PreparedStatement object that generates ResultSet objects with the given type and concurrency.

void

setTypeMap(Map map)

Installs the given type map as the type map for this connection.

10.7.2 Interface Statement

This section describes the JDBC 2.0 Interface Statement methods that are implemented by the Oracle Database Lite JDBC driver.

Connection

getConnection()

Returns the Connection object that produced this Statement object.

int

getFetchDirection()

Retrieves the direction for fetching rows from database tables that is the default for result sets generated from this Statement object. Only FETCH_FORWARD is supported for now.

int

getFetchSize()

Retrieves the number of result set rows that is the default fetch size for result sets generated from this Statement object. Only fetch size = 1 is supported for now.

int

getResultSetConcurrency()

Retrieves the result set concurrency. Only CONCUR_READ_ONLY is supported for now.

int

getResultSetType()

Determine the result set type. Only TYPE_FORWARD_ONLY and TYPE_SCROLL_INSENSITIVE are supported for now.

void

setFetchDirection(int direction)

Gives the driver a hint as to the direction in which the rows in a result set will be processed.

void

setFetchSize(int rows)

Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed.

10.7.3 Interface ResultSet

This section describes the JDBC 2.0 Interface ResultSet methods that are implemented by the Oracle Database Lite JDBC driver.

10.7.3.1 Fields

The following fields can be used to implement the Interface ResultSet feature.

static int

CONCUR_READ_ONLY

The concurrency mode for a ResultSet object that may NOT be updated.

static int

CONCUR_UPDATABLE

The concurrency mode for a ResultSet object that may be updated. Not supported for now.

static int

FETCH_FORWARD

The rows in a result set will be processed in a forward direction; first-to-last.

static int

FETCH_REVERSE

The rows in a result set will be processed in a reverse direction; last-to-first. Not supported for now.

static int

FETCH_UNKNOWN

The order in which rows in a result set will be processed is unknown.

static int

TYPE_FORWARD_ONLY

The type for a ResultSet object whose cursor may move only forward.

static int

TYPE_SCROLL_INSENSITIVE

The type for a ResultSet object that is scrollable but generally not sensitive to changes made by others.

static int

TYPE_SCROLL_SENSITIVE

The type for a ResultSet object that is scrollable and generally sensitive to changes made by others. Not supported for now.

10.7.3.2 Methods

This section describes the JDBC 2.0 ResultSet method implemented by the Oracle Database Lite JDBC driver.

boolean

absolute(int row)

Moves the cursor to the given row number in the result set.

void

afterLast()

Moves the cursor to the end of the result set, just after the last row.

void

beforeFirst()

Moves the cursor to the front of the result set, just before the first row.

boolean

first()

Moves the cursor to the first row in the result set.

Array

getArray(String colName)

Gets an SQL ARRAY value in the current row of this ResultSet object.

BigDecimal

getBigDecimal(int columnIndex)

Gets the value of a column in the current row as a java.math.BigDecimal object with full precision.

BigDecimal

getBigDecimal(String columnName)

Gets the value of a column in the current row as a java.math.BigDecimal object with full precision.

int

getConcurrency()

Returns the concurrency mode of this result set.

Date

getDate(int columnIndex, Calendar cal)

Gets the value of a column in the current row as a java.sql.Date object.

int

getFetchDirection()

Returns the fetch direction for this result set.

int

getFetchSize()

Returns the fetch size for this result set.

int

getRow()

Retrieves the current row number.

Statement

getStatement()

Returns the Statement that produced this ResultSet object.

int

getType()

Returns the type of this result set.

boolean

isAfterLast()

boolean

isBeforeFirst()

boolean

isFirst()

boolean

isLast()

boolean

last()

Moves the cursor to the last row in the result set.

boolean

previous()

Moves the cursor to the previous row in the result set.

void

refreshRow()

Refreshes the current row with its most recent value in the database. Currently does nothing.

boolean

relative(int rows)

Moves the cursor a relative number of rows, either positive or negative.

10.7.3.3 Methods that Return False

The following three methods always return false because this release does not support deletes, inserts, or updates.

boolean

rowDeleted()

Indicates whether a row has been deleted.

boolean

rowInserted()

Indicates whether the current row has had an insertion.

boolean

rowUpdated()

Indicates whether the current row has been updated.

void

setFetchDirection(int direction)

Gives a hint as to the direction in which the rows in this result set will be processed.

void

setFetchSize(int rows)

Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for this result set.

10.7.4 Interface Database MetaData

This section describes the JDBC 2.0 Interface Database MetaData methods that are implemented by the Oracle Database Lite JDBC driver.

10.7.4.1 Methods

The following methods can be used to implement the Interface Database MetaData feature.

Connection

getConnection()

Retrieves the connection that produced this metadata object.

boolean

supportsResultSetConcurrecny(int type, int concurrency)

Supports the concurrency type in combination with the given result set type.

boolean

supportsResultSetType(int Type)

Supports the given result set type.

10.7.4.2 Methods that Return False

The following methods return false, because this release does not support deletes or updates.

boolean

deletesAreDetected(int Type)

Indicates whether or not a visible row delete can be detected by calling ResultSet.rowDeleted().

boolean

insertsAreDetected(int Type)

Indicates whether or not a visible row insert can be detected by calling ResultSet.rowInserted().

boolean

othersDeletesAreVisible(int Type)

Indicates whether deletes made by others are visible.

boolean

othersInsertsAreVisible(int Type)

Indicates whether inserts made by others are visible.

boolean

othersUpdatesAreVisible(int Type)

Indicates whether updates made by others are visible.

boolean

ownDeletesAreVisible(int Type)

Indicates whether a result set's own deletes are visible.

boolean

ownInsertsAreVisible(int Type)

Indicates whether a result set's own inserts are visible.

boolean

ownUpdatesAreVisisble(int Type)

Indicates whether a result set's own updates are visible.

boolean

updatesAreDetected(int Type)

Indicates whether or not a visible row update can be detected by calling the method ResultSet.rowUpdated.

10.7.5 Interface ResultMetaData

This section lists methods that can be implemented using the Interface ResultMetaData feature.

10.7.5.1 Methods

The following method can be used to implement the Interface ResultMetaData feature.

String

getColumnClassName(int column)

Returns the fully-qualified name of the Java class whose instances are manufactured if the method ResultSet.getObject is called to retrieve a value from the column.

10.7.6 Interface PreparedStatement

This section describes methods that can be implemented using the Interface PreparedStatement feature.

10.7.6.1 Methods

The following methods can be used to implement the Interface PreparedStatement feature.

Result

SetMetaDatagetMetaData()

Gets the number, types and properties of a ResultSet's columns.

void

setDate(int parameter Index, Date x, Calendar cal)

Sets the designated parameter to a java.sql.Date value, using the given Calendar object.

void

setTime(int parameterIndex, Time x, Calendar cal)

Sets the designated parameter to a java.sql.Time value, using the given Calendar object.

void

setTimestamp(int parameter Index, Timestamp x, Calendar cal)

Sets the designated parameter to a java.sql.Timestamp value, using the given Calendar object.

10.7.6.1.1 Limitation

currently, the option setQueryTimeOut is not supported.