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

Previous
Previous
Next
Next
 

3 JDBC Programming

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

3.1 JDBC Compliance

JDBC is an application programmer's interface for accessing relational databases from Java programs. Oracle Database Lite supplies a native JDBC driver that allows Java applications to communicate directly with Oracle Database Lite's object relational database engine. Oracle Database Lite's implementation of JDBC complies with JDBC 1.22. In addition, Oracle Database Lite provides certain extensions specified by JDBC 2.0. Oracle Database Lite's extensions are compatible with the Oracle8i JDBC implementation. For a complete JDBC reference, see the Sun Microsystems web site.

3.2 JDBC Environment Setup

If you are using the client/server model, include the olite40.jar in the 'system' classpath on the server machine. Include the 'user' classpath on the client machine.

For more information on how to start the Multiuser Oracle Database Lite Database Service, see Section 2.2.1.2, "Starting a Multi-User Oracle Database Lite Database Service," in the Oracle Database Lite Developer’s Guide.

3.3 Connect to Oracle Database Lite

JDK 1.3.x or higher is required to connect to Oracle Database Lite.

There are three ways to connect to Oracle Database Lite.

Oracle Database Lite supports two types of drivers namely, Type 2 and Type 4. The Type 2 driver requires a native code on the client side. The Type 2 driver interfaces with the Oracle Database Lite ODBC driver through this native code.


Note:

On the Windows platform, the Type 2 driver uses the oljdbc40.dll.

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. A Java applet can use the Type 4 JDBC driver.

Type 2 Driver Connection URL Syntax

DriverManager.getConnection("jdbc:polite@URL_Name:100:polite","system","admin");

This syntax is used to make a direct connection to a database on a client machine. Enter the URL definition as given below.

jdbc:polite@host:port:dsn

The following arguments can be made as part of the URL clause or as a separate key-value pair. There may be none or many occurrences of the key-value pair which provide additional information to the driver. All information that can be specified in the URL can be specified as a key-value pair. The information that is specified as a key-value pair always overrides the information that is specified in the URL.

The URL interpretation and key-value pair options for each argument are described in the following table.

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 user ID and password. If the database is encrypted, you must include the password in the key-value pair.
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, refer the Oracle Database Lite Developer’s Guide.

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 the Oracle Database Lite Developer’s Guide.
Autocommit= Commit behavior, either ON or OFF.
CursorType= Cursor behavior: DYNAMIC, FORWARD ONLY, KEYSET DRIVEN or STATIC. For more information on cursor types, see the Oracle Database Lite Developer’s Guide.
UID= User name
PWD= Password

Example

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)
{
 
 
 
   ...
}

Type2 Client/Server Driver Connection URL Syntax

jdbc:polite[:uid / pwd]@[host]:[port]:dsn [;key=value]*

The URL can be used to connect to the Oracle Database Lite service using the Type 2 JDBC driver. For more information on how to install and start the Multiuser Oracle Database Lite Database Service, refer to Section 2.2.1.2, "Starting a Multi-User Oracle Database Lite Database Service," in the Oracle Database Lite Developer’s Guide.

Argument Description
host The name of the machine that hosts Oracle Database Lite 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 Oracle Database Lite service listens. The port number is optional and if omitted defaults to port "100".

Example

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)
{

You should enclose the getConnection method in a try-catch block to intercept any SQL exception thrown during the connection attempt. You can insert an exception handling statement in the catch block.

Type4 (Pure Java) Driver Connection URL Syntax

The URL syntax for the type4 driver is given below.

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

The parameter 4 indicates that the type4 driver is being used. For the rest of the parameters, see the definitions of those parameters for the type2 driver as described above.


Note:

The URL works with the Oracle Database Lite service only. For more information on how to start and stop the Oracle Database Lite service, refer the Oracle Database Lite Developer’s Guide.

3.4 Executing Java Stored Procedures from JDBC

After creating a Java stored procedure, you can execute the procedure from a JDBC application by performing the following steps.

The executeQuery method executes table-level and row-level stored procedures. CallableStatement currently only supports execution of table-level stored procedures.

3.4.1 Using the executeQuery Method

To call a stored procedure using the executeQuery method, first create a Statement object, which you assign the value returned by the createStatement method of the current connection object. You then execute the Statement.executeQuery method, by passing the SQL SELECT string that invokes the Java stored procedure.

For example, suppose you want to execute a row-level procedure SHIP on a table named INVENTORY with the argument value stored in the variable q. The variable p contains the product ID for the product (row) for which you want to execute the stored procedure.

int res = 0;
Statement s = conn.createStatement();
ResultSet r = s.executeQuery("SELECT SHIP(" + q + ")" + 
   "FROM INVENTORY WHERE PID = " + p);
if(r.next()) res = r.getInt(1);
r.close();
s.close();
return res;

If you need to execute a procedure repeatedly with varying parameters, use PreparedStatement instead of Statement. Because the SQL statements in a PreparedStatement are pre-compiled, PreparedStatements execute more efficiently. Additionally, a PreparedStatement can accept IN parameters, represented in the statement with a question mark (?). However, if the PreparedStatement takes a "long" type parameter, such as LONG or LONG RAW, you must bind the parameter using the setAsciiStream, setUnicodeStream, or setBinaryStream methods.

In the preceding example, if the procedure SHIP updates the database and the isolation of the transaction that issues the above query is READ COMMITTED, you must append the FOR UPDATE clause to the SELECT statement, as given below.

"SELECT SHIP(" + q + ")" +
   FROM INVENTORY WHERE PID = " + 
   p + "FOR UPDATE");

3.4.2 Using a Callable Statement

To execute the stored procedure using a callable statement, create a CallableStatement object and register its parameters as given below.

CallableStatement cstmt = conn.prepareCall(
   "{?=call tablename.methodname() }");
cstmt.registerOutParameter(1, ...);
cstmt.executeUpdate();
cstmt.get..(1);
cstmt.close();

The following restrictions apply to JDBC callable statements.

  • JDBC callable statements can only execute table-level stored procedures.

  • Both IN and OUT parameters are supported. However, not all Java datatypes can be used as OUT parameters. For more information, see Chapter 4, "Java Stored Procedures and Triggers".

  • Procedure names correspond to the Java method names, and are case-sensitive.

  • As with prepared statements, if the callable statement has a "long" type, such as: LONG, LONG VARBINARY, LONG VARCHAR, LONG VARCHAR2, or LONG RAW, you must bind the parameter using the setAsciiStream, setUnicodeStream, or setBinaryStream methods.


    Note:

    When no longer needed, you should reclaim system resources by closing JDBC objects, such as Resultset and Statement objects.

3.5 Oracle Database Lite Extensions

The Oracle Database Lite JDBC driver supports JDBC 1.22 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 Oracle8i JDBC implementation. However, Oracle Database Lite does not support the Oracle8i JDBC datatype extensions, Array, Struct, or REF.

This section lists and describes the Oracle Database Lite datatype and data access extensions. For details regarding function syntax and call parameters, see the Sun Microsystems Java 2 specification at the Sun Javasoft website.

3.5.1 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 3-1 lists the methods included in the Oracle Database Lite BLOB class and their descriptions:

Table 3-1 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 3-2 lists the methods included in the Oracle Database Lite CLOB class and their descriptions.

Table 3-2 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.

3.5.2 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 3-3 lists the data access functions included in the OracleResultSet class.

Table 3-3 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.

3.5.2.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);

...

3.5.2.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();

...

3.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:

  1. Use fully qualified names for BLOB, CLOB, and data classes.

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

3.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:

3.7.1 Interface Connection

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

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

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

3.7.3 Interface ResultSet

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

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

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

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

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

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

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

3.7.5 Interface ResultMetaData

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

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

3.7.6 Interface PreparedStatement

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

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

3.7.6.1.1 Limitation

currently, the option setQueryTimeOut is not supported.