5 JDBC Standards Support

Oracle JDBC supports several different versions of JDBC, including JDBC 2.0 and 3.0.

This chapter provides an overview of JDBC 2.0 and 3.0 support in the Oracle JDBC drivers. The following topics are discussed:

• Introduction

• JDBC 2.0 Support: JDK 1.2.x and Higher Versions

• JDBC 3.0 Support: JDK 1.4 and Previous Releases

• Overview of Supported JDBC 3.0 Features

• Transaction Savepoints

• JDBC 3.0 LOB Interface Methods

Introduction

The Oracle JDBC drivers provide substantial support for the JDBC 3.0 specification. Oracle makes supported JDBC 3.0 features are available in JDK1.2 through Oracle extensions. The following changes have been made as part of this support:

• The oracle.jdbc2 package has been removed.

• JDK1.1.x is no longer supported.

The Oracle JDBC drivers support most JDBC 3.0 features, including:

• Using global and distributed transactions on the same connection (see "Oracle XA Packages" )

• Transaction savepoints (see "Transaction Savepoints" )

• Re-use of prepared statements by connection pools (also known as statement caching; see Chapter 6, " Statement Caching ")

• Full support for JDK1.4 (see "JDBC 3.0 Support: JDK 1.4 and Previous Releases" in this chapter)

All of these features are provided in the packages oracle.jdbc and oracle.sql. These packages support all JDK releases from 1.2 through 1.4; JDBC 3.0 features that depend on JDK1.4 are made available to earlier JDK versions through Oracle extensions.

JDBC 2.0 Support: JDK 1.2.x and Higher Versions

Standard JDBC 2.0 features are supported for all JDK versions at 1.2 or higher. There are three areas to consider:

• datatype support—such as for objects, arrays, and LOBs—which is handled through the standard java.sql package

• standard feature support—such as result set enhancements and update batching—which is handled through standard objects such as Connection, ResultSet, and PreparedStatement under JDK 1.2.x and higher

• extended feature support—features of the JDBC 2.0 Optional Package (also known as the Standard Extension API), including datasources, connection pooling, and distributed transactions—under JDK 1.2. and higher

This section also discusses performance enhancements available under JDBC 2.0—update batching and fetch size—that are also still available as Oracle extensions, then concludes with a brief discussion about migration from JDK 1.1.x to JDK 1.2.x.

Datatype Support

Oracle JDBC fully supports JDK 1.2.x, which includes standard JDBC 2.0 functionality through implementation of interfaces in the standard java.sql package. These interfaces are implemented as appropriate by classes in the oracle.sql and oracle.jdbc packages.

Standard Feature Support

In a JDK 1.2.x environment (using the JDBC classes in classes12.jar), JDBC 2.0 features such as scrollable result sets, updatable result sets, and update batching are supported through methods specified by standard JDBC 2.0 interfaces.

Extended Feature Support

Features of the JDBC 2.0 Optional Package (also known as the Standard Extension API), including datasources, connection pooling, and distributed transactions, are supported in a JDK 1.2.x or later environment.

The standard javax.sql package and classes that implement its interfaces are included in the JDBC classes12.jar file.

Standard versus Oracle Performance Enhancement APIs

There are two performance enhancements available under JDBC 2.0, which had previously been available as Oracle extensions:

• update batching

• fetch size / row prefetching

In each case, you have the option of using the standard model or the Oracle model. Do not, however, try to mix usage of the standard model and Oracle model within a single application for either of these features.

For more information, see the following sections:

• "Update Batching"

• "Fetch Size"

• "Oracle Row Prefetching"

Migration from JDK 1.1.x

The only migration requirements in upgrading from JDK 1.1.x are as follows:

• Remove your imports of the oracle.jdbc2 package

• Replace any direct references to oracle.jdbc2.* interfaces with references to the standard java.sql.* interfaces.

• Type map objects (for mapping SQL structured objects to Java types), which must extend the java.util.Dictionary class under JDK 1.1.x, must implement the java.util.Map interface under JDK 1.2.x. Note, however, that the class java.util.Hashtable satisfies either requirement. If you used Hashtable objects for your type maps under JDK 1.1.x, then no change is necessary. For more information, see "Creating a Type Map Object and Defining Mappings for a SQLData Implementation".

If these points do not apply to your code, then you do not need to make any code changes or recompile to run under JDK 1.2 and higher releases.

JDBC 3.0 Support: JDK 1.4 and Previous Releases

This release adds or extends the following interfaces and classes.

Table 5-1 JDBC 3.0 Feature Support

New feature	JDK1.4 implementation	Pre-JDK1.4 implementation
Savepoints (new class)	java.sql.Savepoint	oracle.jdbc.OracleSavepoint
Savepoints (connection extensions)	java.sql.connection	oracle.jdbc. OracleConnection
Querying parameter capacities (new class)	java.sql.ParameterMetaData	oracle.jdbc. OracleParameterMetaData
Querying parameter capacities (interface change)	Not applicable	oracle.jdbc. OraclePreparedStatement
Resource adapters	oracle.jdbc. connector	oracle.jdbc.connector
WebRowSet	oracle.jdbc.rowset. OracleWebRowSet	oracle.jdbc.rowset. OracleWebRowSet
LOB modification	Not applicable	oracle.sql.BLOB oracle.sql.CLOB

Overview of Supported JDBC 3.0 Features

Table 5-2 lists the JDBC 3.0 features supported at this release and gives references to a detailed discussion of each feature.

Table 5-2 Key Areas of JDBC 3.0 Functionality

Feature	Comments and References
Transaction savepoints	See "Transaction Savepoints" for information.
Connection sharing	Re-use of prepared statements by connection pools (see Chapter 6, " Statement Caching ".
Switching between local and global transactions	See "Switching Between Global and Local Transactions".
LOB modification	See "JDBC 3.0 LOB Interface Methods" .
Named SQL parameters	See "Interface oracle.jdbc.OracleCallableStatement" and "Interface oracle.jdbc.OraclePreparedStatement" .
WebRowSet	See Chapter 18, " Row Set ".

Unsupported JDBC 3.0 Features

The following JDBC 3.0 features are not supported at this release:

• Retrieval of auto-generated keys

• ResultSet holdability

• Multiple open ResultSets

Transaction Savepoints

The JDBC 3.0 specification supports savepoints, which offer finer demarcation within transactions. Applications can set a savepoint within a transaction and then roll back (but not commit) all work done after the savepoint. Savepoints relax the atomicity property of transactions. A transaction with a savepoint is atomic in the sense that it appears to be a single unit outside the context of the transaction, but code operating within the transaction can preserve partial states.

Note: Savepoints are supported for local transactions only. Specifying a savepoint within a global transaction causes SQLException to be thrown.

JDK1.4 specifies a standard savepoint API. Oracle JDBC provides two different savepoint interfaces: one (java.sql.Savepoint) for JDK1.4 and one (oracle.jdbc.OracleSavepoint) that works across all supported JDK versions. JDK1.4 adds savepoint-related APIs to java.sql.Connection; the Oracle JDK version-independent interface oracle.jdbc.OracleConnection provides equivalent functionality.

Creating a Savepoint

You create a savepoint using either Connection.setSavepoint(), which returns a java.sql.Savepoint instance, or OracleConnection.oracleSetSavepoint(), which returns an oracle.jdbc.OracleSavepoint instance.

A savepoint is either named or unnamed. You specify a savepoint's name by supplying a string to the setSavepoint() method; if you do not specify a name, the savepoint is assigned an integer ID. You retrieve a name using getSavepointName(); you retrieve an ID using getSavepointId().

Note: Attempting to retrieve a name from an unnamed savepoint or attempting to retrieve an ID from a named savepoint throws an SQLException.

Rolling back to a Savepoint

You roll back to a savepoint using Connection.rollback(Savepoint svpt) or OracleConnection.oracleRollback(OracleSavepoint svpt). If you try to roll back to a savepoint that has been released, SQLException is thrown.

Releasing a Savepoint

You remove a savepoint using Connection.releaseSavepoint(Savepoint svpt) or OracleConnection.oracleReleaseSavepoint(OracleSavepoint svpt).

Note: As of 10g Release 1 (10.1), releaseSavepoint() and oracleReleaseSavepoint() are not supported; if you invoke either message, SQLException is thrown with the message "Unsupported feature".

Checking Savepoint Support

You query whether savepoints are supported by your database by calling oracle.jdbc.OracleDatabaseMetaData.supportsSavepoints(), which returns true if savepoints are available, false otherwise.

Savepoint Notes

• After a savepoint has been released, attempting to reference it in a rollback operation will cause an SQLException to be thrown.

• When a transaction is committed or rolled back, all savepoints created in that transaction are automatically released and become invalid.

• Rolling a transaction back to a savepoint automatically releases and makes invalid any savepoints created after the savepoint in question.

Savepoint Interfaces

The following methods are used to get information from savepoints. These methods are defined within both the java.sql.Connection and oracle.jdbc.OracleSavepoint interfaces:

public int getSavepointId() throws SQLException;

Return the savepoint ID for an unnamed savepoint.

Exceptions:

• SQLException: Thrown if self is a named savepoint.

public String getSavepointName() throws SQLException;

Return the name of a named savepoint.

Exceptions:

• SQLException: Thrown if self is an unnamed savepoint.

These methods are defined within the java.sql.Connection interface:

public Savepoint setSavepoint() throws SQLException;

Create an unnamed savepoint.

Exceptions:

• SQLException: Thrown on database error, or if Connection is in auto-commit mode or participating in a global transaction.

publicSavepoint setSavepoint(String name) throws SQLException;

Create a named savepoint. If a Savepoint by this name already exists, this instance replaces it.

Exceptions:

• SQLException: Thrown on database error or if Connection is in auto-commit mode or participating in a global transaction.

public void rollback(Savepoint savepoint) throws SQLException;

Remove specified Savepoint from current transaction. Any references to the savepoint after it is removed cause an SQLException to be thrown.

Exceptions:

• SQLException: Thrown on database error or if Connection is in auto-commit mode or participating in a global transaction.

public void releaseSavepoint(Savepoint savepoint) throws SQLException;

Not supported at this release. Always throws SQLException.

Pre-JDK1.4 Savepoint Support

These methods are defined within the oracle.jdbc.OracleConnection interface; except for using OracleSavepoint in the signatures, they are identical to the methods above.

public OracleSavepoint oracleSetSavepoint() throws SQLException;

public OracleSavepoint oracleSetSavepoint(String name) throws SQLException;

public void oracleRollback(OracleSavepoint savepoint) throws SQLException;

public void oracleReleaseSavepoint(OracleSavepoint savepoint) throws SQLException;

JDBC 3.0 LOB Interface Methods

Before 10g Release 1 (10.1), Oracle provided proprietary interfaces for modification of LOB data. JDBC 3.0 adds methods for these operations. In 9iR2, the JDBC 3.0 methods were present in ojdbc14.jar but were not functional. The JDBC 3.0 standard LOB methods differ slightly in name and function from the Oracle proprietary ones. In 10g Release 1 (10.1), the JDBC 3.0 standard methods are implemented in both ojdbc14.jar and classes12.jar. In order to use these methods with JDK1.2 or 1.3, LOB variables must be typed as (or cast to) oracle.sql.BLOB or oracle.sql.CLOB as appropriate. With JDK1.4, LOB variables may be typed as java.sql.Blob or java.sql.Clob. The Oracle proprietary methods are marked as deprecated and will be removed in a future release.

Table 5-3 and Table 5-4 show the conversions between Oracle proprietary methods and JDBC 3.0 standard methods.

Table 5-3 BLOB Method Equivalents

Oracle Proprietary Method	JDBC 3.0 Standard Method Replacement
putBytes(long pos, byte [] bytes)	setBytes(long pos, byte[] bytes)
putBytes(long pos, byte [] bytes, int length)	setBytes(long pos, byte[] bytes, int offset, int len)
getBinaryOutputStream(long pos)	setBinaryStream(long pos)
trim (long len)	truncate(long len)

Table 5-4 CLOB Method Equivalents

Oracle Proprietary Method	JDBC 3.0 Standard Method Replacement
putString(long pos, String str)	setString(long pos, String str)
not applicable	setString(long pos, String str, int offset, int len)
getAsciiOutputStream(long pos)	setAsciiStream(long pos)
getCharacterOutputStream(long pos)	setCharacterStream(long pos)
trim (long len)	truncate(long len)