Chapter 8   Using JDBC for Database Access


This chapter describes how to use the Java Database Connectivity (JDBC) API for database accesses with the iPlanet Application Server. This chapter provides high level JDBC implementation instructions for servlets and EJBs using the iPlanet Application Server; it also describes the specific iPlanet Application Server resources affected by JDBC statements when those resources have clear programming ramifications. In an iPlanet Application Server, EJBs support database access primarily through the JDBC API. The iPlanet Application Server supports the entire JDBC 2.0 API, as well as, many of the emerging JDBC 2.0 extensions, including result set enhancements, batch updates, distributed transactions, row sets, and Java Naming and Directory Interface (JNDI) support for datasource name lookups.

While this chapter assumes familiarity with JDBC 2.0, it also describes specific implementation issues that may have programming ramifications. For example, the JDBC specification does not make it clear what constitute JDBC resources. In the specifications, some JDBC statements—such as, any Connection class methods that close database connections—release resources without specifying exactly what those resources are.

This chapter contains the following sections:

• Introducing JDBC

• Using JDBC in Server Applications

• Handling Connections

• Working with JDBC Features

Introducing JDBC

---

From a programming perspective, JDBC is a set of Java classes and methods that allows embedding database calls in server applications. That is all you need to know in order to start using JDBC in your server application.

More specifically, JDBC is a set of interfaces that every server vendor, such as iPlanet, must implement according to the JDBC specifications. The iPlanet Application Server provides a JDBC type 2 driver which supports a variety of Enterprise Information Systems (EISs) databases. The driver processes the JDBC statements in your application and routes the SQL arguments they contain to your database engines.

JDBC lets you write high level, easy-to-use programs that operate seamlessly with and across many different databases without you knowing most of the low level database implementation details.

Supported Functionality

The JDBC specification is a broad, database vendor independent set of guidelines. The guidelines encompass the broadest database functionality range possible in a simple framework. At a minimum, JDBC assumes the database supports the SQL-2 database access language. The JDBC specification has three parts:

• JDBC 2.0 describes the core database access and functionality that a server vendor must implement to be JDBC compliant. The iPlanet Application Server fully meets the compliance standard. From a database vendor's perspective, JDBC 2.0 describes a database access model that permits full access to the standard SQL-2 language, the standard language portions each vendor supports, and the language extensions each vendor implements.

• JDBC 2.0 describes additional database access and functionality. Primarily, this functionality involves support for newly defined SQL-3 features, data types, and mappings. The iPlanet Application Server implementation of JDBC supports most JDBC feature enhancements, but omits support for the new SQL-3 data types, such as blobs, clobs, and arrays. Currently, many database vendors do not fully support them in their relational database management systems. The iPlanet Application Server JDBC implementation also omits support for SQL-3 data type mapping.

• JDBC 2.0 Standard Extension API describes advanced support features, many of which offer improved database performance. The iPlanet Application Server JDBC implementation currently supports Java Naming and Directory Interface (JNDI) and row sets.

Understanding Database Limitations

When using JDBC in your server applications, you may encounter situations where you do not obtain the results you desire or expect. You may think the problem lies in JDBC or in the iPlanet Application Server JDBC driver implementation. However, the vast majority of these problems are limitations in your database engine.

Because JDBC covers the broadest possible database support, it enables you to attempt operations not every database supports. For example, most database vendors support most of the SQL-2 language, but no vendor provides fully unqualified support for all of the SQL-2 standard. Most vendors built SQL-2 support on top of their existing proprietary relational database management systems, and either those proprietary systems offer features not in SQL-2 or SQL-2 offers features not available in those systems. Most vendors have added non standard SQL-2 extensions to their SQL implementation to support their proprietary features. JDBC provides ways to access vendor specific features, but realize these features may not be available for all databases you use.

This is especially true when you build an application that uses databases from two or more vendors. As a result, not all vendors fully support all aspects of every available JDBC class, method, and method arguments. More importantly, a set of SQL statements embedded as an argument in a JDBC method call may or may not be supported by the database or databases your server application uses. In order to maximize JDBC usage, consult your database documentation about which SQL and JDBC aspects they support. Therefore, first eliminate your database as causing the problem before calling iPlanet technical support for database problems.

Understanding the iPlanet Application Server Limitations

Like JDBC, the iPlanet Application Server supports the broadest spectrum of database engines and features. In some cases, the iPlanet Application Server itself or the iPlanet Application Server JDBC driver may not fully support a particular database feature, or it may report incorrect information. If you cannot access a database feature from your iPlanet Application Server application and you have eliminated the database as the problem, check this section in the documentation and the Release Notes to determine if the problem you encounter is a documented iPlanet Application Server limitation. If not, fully document the problem and contact iPlanet technical support.

---

Note	Some JDBC access problems can result if you attempt to access JDBC features that are either partially supported or not supported by the iPlanet Application Server JDBC driver. Almost all feature limitations apply to JDBC 2.0.

---

Table 8-1 lists the JDBC features that are not supported, either partially or completely in the iPlanet Application Server:

Table 8-1    JDBC Feature Limitations

Feature	Limitation
Connection.setTransactionIsolation	Works only with isolation levels supported by your database vendors.
Connection.getTypeMap	Type maps are not supported.
Connection.setTypeMap	Type maps are not supported.
Connection.cancel	Works only with databases that support it.
PreparedStatement.setObject	Works only with simple data types.
PreparedStatement.addBatch	Works only with supported data manipulation statements that return a count of records changed.
PreparedStatement.setRef	References are not supported.
PreparedStatement.setBlob	Blobs are not supported. Use setBinaryStream() instead.
PreparedStatement.setClob	Clobs are not supported. Use setBinaryStream() instead.
PreparedStatement.setArray	Arrays are not supported. Use setBinaryStream() instead.
PreparedStatement.getMetaData	Not supported.
CallableStatement.getObject	Works only with scalar types. JDBC 2.0 offers a second version of this method that includes a map argument. The map argument is ignored.
CallableStatement.getRef	References are not supported.
CallableStatement.getBlob	SQL3-style blobs are not supported.
CallableStatement.getClob	SQL3-style clobs are not supported.
CallableStatement.getArray	Arrays are not supported.
CallableStatement	Updatable ResultSet is not supported.
ResultSet.getCursorName	Behavior differs depending on database: For Oracle, if user does not specify a cursor name with SetCursorName, an empty string is returned. For Sybase, if the result set is not updatable, a cursor name is automatically generated by the iPlanet Application Server. Otherwise an empty string is returned. For ODBC, Informix, and DB2, the driver returns a cursor name if none is specified.
ResultSet.getObject	Works only with scalar types. JDBC 2.0 offers two other versions of this method that includes a map argument. The map argument is ignored.
ResultSet.updateObject	Works only with scalar types.
ResultSet.getRef	References are not supported.
ResultSet.getBlob	SQL3-style blobs are not supported.
ResultSet.getClob	SQL-style clobs are not supported.
ResultSet.getArray	Arrays are not supported.
ResultSetMetaData.getTableName	Returns an empty string for non-ODBC database access.
DatabaseMetaData.getUDTs	Not supported.

For more information about working with ResultSet, ResultSetMetaData, and PreparedStatement, see the appropriate sections later in this chapter.

Supported Databases

Table 8-2 lists the databases currently supported by the iPlanet Application Server.

Table 8-2    Supported Databases

Database	Notes
Oracle	Support is through the Oracle OCI interface. Both Oracle 7 and 8 database instances are supported in a fully multi-threaded environment. The iPlanet Application Server coexists with all Oracle RDBMS tools and utilities, such as SQL*Plus, Server Manager, and Oracle Backup.
Informix	Support is through Informix CLI interface. Both Informix Online Dynamic Server and Informix Universal Server are supported.
Sybase	Support is through Sybase CTLIB. Sybase server version 12 and client version 12 are supported.
Microsoft SQL Server	Support is through the Microsoft ODBC interface. Microsoft SQLServer on Windows NT only is supported.
DB2	Support is through the DB2 CLI client interface. DB2 versions 5.1 and 6.1, and client version 7.1 are supported.
ODBC	The iPlanet Application Server does not specifically support or certify any ODBC 2.0 or 3.0 compliant driver sets, though they may work.

---

Note	Because the databases supported by the iPlanet Application Server are constantly being updated and database vendors are consistently upgrading their products, always check with the iPlanet technical support for the latest database support information.

---

Using JDBC in Server Applications

---

JDBC is part of the iPlanet Application Server runtime environment. This means JDBC is always available any time you use Java to program an application. In a typical multi-tiered server application, you use JDBC to access an EIS database from a client, from the presentation layer, in servlets, and in EJBs.

However, in practice it makes sense—for security and portability reasons—to restrict database accesses to the middle layers of a multi-tiered server application. In the iPlanet Application Server programming model, this means placing all JDBC calls in servlets and EJBs, with the preference being towards EJBs.

There are two reasons for this programming preference:

• Placing all JDBC calls inside EJBs makes your application more modular and more portable.

• EJBs provide built-in mechanisms for transaction control.

Placing JDBC calls in well designed EJBs frees you from programming explicit transaction control using JDBC or java.transaction.UserTransaction that provide low level transaction support under JDBC.

---

Note	Always use a globally available datasource to create a global (bean-wide) connection so that the EJB transaction manager controls the transaction.

---

Using JDBC in EJBs

Placing your JDBC calls in EJBs ensures a high degree of server application portability. It also frees you from having to manage transaction control with explicit JDBC calls. Because EJBs are components, use them as building blocks for many applications with little or no changes, and maintain a common interface to your EIS database.

Managing Transactions with JDBC or javax.transaction.UserTransaction

Using the EJB transaction attribute property to manage transactions is recommended, but not mandatory. There may be times when explicit transaction management programming using JDBC or javax.transaction.UserTransaction is appropriate for an application. In these cases, program the transaction management in the bean yourself. Using an explicit transaction in an EJB is called a bean managed transactions.

Transactions can be local to a specific method (method-specific) or they can encompass the entire bean (bean-wide).

There are two steps for creating a bean managed transaction:

1. Set the EJB transaction attribute property to TX_BEAN_MANAGED in the bean's deployment descriptor.

2. Program the appropriate JDBC or transaction management statements in the bean, including statements to start the transaction, and to commit or roll it back.

Do not program explicit transaction handling in EJBs when the transaction attribute property is not TX_BEAN_MANAGED. For more information about handling transactions with JDBC, see the JDBC 2.0 API specification.

Specifying Transaction Isolation Level

Specify or examine the transaction level for a connection using the setTransactionIsolation() and getTransactionIsolation() methods, respectively. Note that you cannot call setTransactionIsolation() during a transaction.

Table 8-3 defines the transaction isolation levels, as follows:

Table 8-3    Transaction Isolation Levels

Transaction Isolation Level	Description
TRANSACTION_NONE	Transactions are not supported. Only used with Connection.getTransactionIsolation()
TRANSACTION_READ_COMMITTED	Dirty reads are prevented; non-repeatable reads and phantom reads can occur.
TRANSACTION_READ_UNCOMMITTED	Dirty reads, non-repeatable reads and phantom reads can occur.
TRANSACTION_REPEATABLE_READ	Dirty reads and non-repeatable reads are prevented; phantom reads can occur.
TRANSACTION_SERIALIZABLE	Dirty reads, non-repeatable reads and phantom reads are prevented.

Before specifying a bean's transaction isolation level, verify the level is supported by your database management system. Not all databases support all isolation levels. Test your database programmatically by using the method supportsTransactionIsolationLevel() in java.sql.DatabaseMetaData, as shown in the following example:

java.sql.DatabaseMetaData db;
if (db.supportsTransactionIsolationLevel(TRANSACTION_SERIALIZABLE) {
   Connection.setTransactionIsolation(TRANSACTION_SERIALIZABLE);
}

For more information about these isolation levels and what they mean, see the JDBC 2.0 API specification.

Using JDBC in Servlets

Servlets are at the heart of an iPlanet Application Server application. They stand between a client interface, such as an HTML page on a browser, the JSP that generated the HTML, and the EJBs that do the bulk of an application's work.

The iPlanet Application Server applications use JDBC embedded in EJBs for most database accesses. This is the preferred method for database accesses using the iPlanet Application Server because it enables you to take advantage of the transaction control built-in to EJBs and their containers. Servlets, however, can also provide database access through JDBC.

In some situations, accessing a database directly from a servlet can offer a speed advantage over accessing a database from EJBs. There is less call overhead, if an application is spread across servers so that EJBs are accessible only through the Java Remote Method Interface (RMI). Use direct database service through servlets sparingly. If providing database access from servlets, restrict access to very short durations, the transaction is read-only, and take advantage of the JDBC 2.0 RowSet class.

If access to a database is from a servlet, use the JDBC 2.0 RowSet interface to interact with the database. A row set is a Java object that encapsulates a set of rows that have been retrieved from a database or other tabular datasource, such as a spreadsheet. The RowSet interface provides JavaBean properties that allow a RowSet instance to be configured to connect to a datasource and retrieve a set of rows. For more information about working with row sets, see "Working with RowSet."

Handling Connections

---

The iPlanet Application Server implements the JDBC 2.0 compliant interface java.sql.Connection. The connection behavior depends on if it is a local, global or container managed local connection.

Local Connections

A Connection object is called a local connection if its transaction context is not managed by an EJB container. The transaction context in a local connection cannot propagate across processes or datasources; it is local to the current process and to the current datasource.

The transaction context on this connection type is managed using the setAutoCommit(), commit(), and rollback() methods.

Registering a Local Datasource

The first step to create a local connection is to register the datasource with the iPlanet Application Server. Once the datasource is registered, the datasource can be used to make connections to the listed database using getConnection().

Register the datasource by creating an XML resource descriptor file that describes the datasource properties. Next, register the properties with the iPlanet Application Server using the Administration Tool or the resreg utility. resreg takes as its argument, the resource descriptor file name describing the datasource.

---

Note	When run, resreg overwrites existing entries.

---

For example, to register a datasource called SampleDS which connects to an Oracle database using the username kdemo, password kdemo, database ksample and server ksample, create an XML descriptor file like the following, and name it SampleDS.xml (use the iPlanet Application Server Deployment Tool to create an XML file):

<ias-resource>
   <resource>
      <jndi-name>jdbc/SampleDS</jndi-name>
      <jdbc>
         <database>ksample</database>
         <datasource>ksample</datasource>
         <username>kdemo</username>
         <password>kdemo</password>
         <driver-type>ORACLE_OCI</driver-type>
      </jdbc>
   </resource>
</ias-resource>

Then use this resource descriptor file to register the datasource with the following command:

resreg SampleDS.xml

For more information about resource descriptor files, see Chapter 10 "Deployment Packaging." For more information about the iPlanet Application Server Administration Tool, see the Administrator's Guide.

Global Connections

A Connection object is called a global connection if its transaction context is managed by the EJB container. The transaction context in a global connection can be propagated across datasources. The transaction context is managed implicitly by the EJB container for container managed transactions, or explicitly for bean managed transactions. For more information about transactions, see Chapter 7 "Handling Transactions with EJBs."

Transaction management methods are disabled for global connections, for example, setAutoCommit(), commit(), and rollback().

Using Resource Managers

The datasource collection in which a global transaction participates is known as a resource manager. All resources managers must be registered with the iPlanet Application Server and be enabled to participate in global transactions. Resource managers are set up at install time or they are set up using the iPlanet Application Server Administration Tool (for more information, see the Administrator's Guide). A global connection must be associated with a resource manager.

Registering a Global Datasource

The first step in creating a global connection is to register the datasource with the iPlanet Application Server. Once the datasource is registered, the datasource is used to make connections to the listed database using getConnection().

Register the datasource by creating an XML resource descriptor file that describes the datasource properties. Next, register the properties with the iPlanet Application Server using the Administration Tool or the resreg utility. resreg takes as its argument, the resource descriptor file name describing the datasource.

---

Note	When run, resreg overwrites existing entries.

---

For example, to register a datasource called GlobalSampleDS which connects to an Oracle database using the username kdemo, password kdemo, database ksample and server ksample, create a XML descriptor file like the following, and name it GlobalSampleDS.xml (use the iPlanet Application Server Deployment Tool to create the XML file):

<ias-resource>
   <resource>
      <jndi-name>jdbc/GlobalSampleDS</jndi-name>
      <jdbc>
         <database>ksample</database>
         <datasource>ksample</datasource>
         <username>kdemo</username>
         <password>kdemo</password>
         <driver-type>ORACLE_OCI</driver-type>
         <resource-mgr>ksample_rm</resource-mgr>
      </jdbc>
   </resource>
</ias-resource>

Use the resource descriptor file to register the datasource with the following command:

resreg GlobalSampleDS.xml

For more information about resource descriptor files, see Chapter 10 "Deployment Packaging." For more information about the iPlanet Application Server Administration Tool, see the Administrator's Guide.

Creating a Global Connection

The following program demonstrates how a datasource is looked up and a connection created from it. As illustrated, the string that is looked up is the same as specified in the <jndi-name> tag in the resource descriptor file.

InitialContext ctx = null;
String dsName1 = "jdbc/GlobalSampleDS";
DataSource ds1 = null;

try
{
   ctx = new InitialContext();
   ds1 = (DataSource)ctx.lookup(dsName1);

   UserTransaction tx = ejbContext.getUserTransaction();

   tx.begin();

   Connection conn1 = ds1.getConnection();

   // use conn1 to do some database work -- note that conn1.commit(),
   // conn1.rollback() and conn1.setAutoCommit() can not used here

   tx.commit();

} catch(Exception e) {
   e.printStackTrace(System.out);
}

Container Managed Local Connections

A Connection object is considered a container managed local connection when the transaction context is managed by the EJB container and global transactions are disabled. With container managed transactions, the transaction context is managed implicitly by the EJB container and with bean managed transactions the transaction context is handled explicitly.

Connection object methods setAutoCommit(), commit(), and rollback() are disabled for this connection type.

For more information on how to enable or disable global transactions in an EJB container, see the Administrator's Guide.

Registering a Container Managed Local Datasource

The container managed local datasource registering process is the same as for the local and global datasources. For more information, see "Registering a Local Datasource."

Working with JDBC Features

---

While this chapter is not a JDBC primer, it does introduce how to use JDBC in EJBs with the iPlanet Application Server. The following sections describe various JDBC interfaces and classes that either have special requirements in the iPlanet Application Server environment, or are new JDBC 2.0 features you are encouraged to use when developing an iPlanet Application Server application.

For example, "Working With Connections" describes the resources the iPlanet Application Server releases when a connection is closed because this information differs among different JDBC implementations. On the other hand, "Pooling Connections" and "Working with RowSet" offer more extensive coverage because these are new JDBC 2.0 features that offer increased power, flexibility, and server application speed.

Working With Connections

When opening a JDBC connection, the iPlanet Application Server allocates connection resources. Call Connection.close() when a connection is no longer needed, to free the connection resources. Always reestablish connections before continuing database operations after you call Connection.close().

Use Connection.isClose() to test whether the connection is closed. This method returns false if the connection is open, and returns true only after Connection.close() is called. To determine if a database connection is invalid by catching the exception that is thrown when a JDBC operation is attempted on a closed connection.

Finally, opening and closing connections is an expensive operation. If an application uses several connections, and if connections are frequently opened and closed, the iPlanet Application Server automatically provides connection pooling. Connection pooling provides a connection cache that automatically closes when necessary.

---

Note	Connection pooling is an automatic feature of the iPlanet Application Server; the API is not exposed.

---

setTransactionIsolation
Not all database vendors support all transaction isolation levels available in JDBC. The iPlanet Application Server permits specifying any isolation level your database supports, but throws an exception against values your database does not support. For more information, see "Specifying Transaction Isolation Level."

getTypeMap, setTypeMap
The iPlanet Application Server JDBC driver implementation does not support type mapping, a new SQL-3 feature that most database vendors do not support.

cancel
cancel() is supported for all databases that support cancel().

Pooling Connections

Two costlier database operations to execute in JDBC are for creating and destroying database connections. Connection pooling permits a single connection cache for connection requests. A connection is returned to the pool for later reuse without actually destroying it. A later call to create a connection merely retrieves an available connection from the pool.

The iPlanet Application Server automatically provides JDBC connection pooling wherever you make JDBC calls. The process of pooling database connections works differently for each connection type.

• For local connections, the database connections are pooled when they are closed by the application.

• For global connections, the database connections are tied to the thread that initiated the transaction. These connections are later reused by transactions that execute on the thread.

• For container managed local connections, the connection.close() method does not release the connection to the connection pool immediately. When the transaction that the connection is participating in is finished, the connection is released back to the connection pool by the iPlanet Application Server.

In each Java engine, each driver (Oracle, Sybase, Informix and DB2) has its own connection pool. Each connection pool size is according to the application requirements. For more information on the connection pool settings (such as, maximum number of connections, connection timeout and so on), see the Administrator's Guide.

Working with ResultSet

ResultSet is a class that encapsulates the data returned by a database query. Be aware of the following behaviors or limitations associated with this class.

Concurrency Support

The iPlanet Application Server supports concurrency for FORWARD-ONLY READ-ONLY and for SCROLL-INSENSITIVE READ-ONLY result sets. On callable statements, the iPlanet Application Server also supports concurrency for FORWARD-ONLY UPDATABLE result sets.

SCROLL-SENSITIVE concurrency is not supported.

Updatable Result Set Support

In the iPlanet Application Server, creation of updatable result sets is restricted to queries on a single table. The SELECT query for an updatable result set must include the FOR UPDATE clause:

SELECT...FOR UPDATE [OF column_name_list]

---

Note	Use join clauses to create read-only result sets against multiple tables; however, these result sets are not updatable.

---

For Sybase, the select list must include a unique index column. Sybase also permits calling execute() or executeQuery() to create an updatable result set. However, the statement must be closed before you can execute any other SQL statements.

To use an updatable result set with Oracle 8, you must wrap the result set query in a transaction, as follows:

conn.setAutoCommit(false);
ResultSet rs =
   stmt.executeQuery("SELECT...FOR UPDATE...");
...
rs.updateRows();
...
conn.commit();

For Microsoft SQL Server, if concurrency for a result set is CONCUR_UPDATABLE, the SELECT statement in the execute() or executeQuery() methods must not include the ORDER BY clause.

getCursorName
One result set method, getCursorName(), enables the determining of the cursor name used to fetch a result set. If a cursor name is not specified by the query itself, different database vendors return different information. The iPlanet Application Server attempts to handle these differences as transparently as possible. Table 8-4 indicates the cursor name returned by different database vendors if no cursor name is specified in the initial query.

Table 8-4    Cursor Name

Database Vendor	getCursorName Value Returned
Oracle	If a cursor name is not specified with setCursorName(), an empty string is returned.
Sybase	If a cursor name is not specified with setCursorName(), and the result set is not updatable, a unique cursor name is automatically generated by the iPlanet Application Server. Otherwise an empty string is returned.
Informix, DB2, ODBC	If a cursor name is not specified with setCursorName(), the driver automatically generates a unique cursor name.

getObject
The iPlanet Application Server implements this JDBC method and it only works with scalar data types. JDBC 2.0 adds additional method versions that include a map argument. The iPlanet Application Server does not implement maps and ignores map arguments.

getRef, getBlob, getClob, and getArray
References, blobs, clobs, and arrays are new SQL-3 data types. The iPlanet Application Server does not implement these data objects or their methods. However, to work with references, blobs, clobs, and arrays use getBinaryStream() and setBinaryStream().

Working with ResultSetMetaData

The getTableName() method only returns meaningful information for OBDC compliant databases. For all other databases, this method returns an empty string.

Working with PreparedStatement

PreparedStatement is a class that encapsulates a query, update, or insert statement that is used repeatedly to fetch data. Be aware of the following behaviors or limitations associated with this class.

---

Note	Use the iPlanet Application Server feature SqlUtil.loadQuery() to load an iASRowSet with a prepared statement. For more information, see the SqlUtil class entry in the Foundation Class Reference (Java).

---

setObject
This method may only be used with scalar data types.

addBatch
This method enables ganging of a set of data manipulation statements together to pass to the database as if it were a single statement. addBatch() only works with SQL data manipulation statements that return a count of the number of rows updated or inserted. Contrary to the claims of the JDBC 2.0 specification, addBatch() does not work with any SQL data definition statements such as CREATE TABLE.

setRef, setBlob, setClob, setArray
References, blobs, clobs, and arrays are new SQL-3 data types. The iPlanet Application Server does not implement these data objects or the methods that work with them. However, to work with references, blobs, clobs, and arrays use getBinaryStream() and setBinaryStream().

getMetaData
Not all database systems return complete metadata information. See your database documentation to determine what kind of metadata your database provides to clients.

Working with CallableStatement

CallableStatement is a class that encapsulates a database procedure or function call for databases that support returning result sets from stored procedures. Be aware of the following limitation associated with this class. The JDBC 2.0 specfication states that callable statements can return an updatable result set. This feature is not supported in the iPlanet Application Server.

getRef, getBlob, getClob, getArray
References, blobs, clobs, and arrays are new SQL-3 data types. The iPlanet Application Server does not implement these data objects or the methods that work with them. However, to work with references, blobs, clobs, and arrays use getBinaryStream() and setBinaryStream().

Handling Batch Updates

The JDBC 2.0 specification provides a batch update feature to an application to pass multiple SQL update statements (INSERT, UPDATE, DELETE) in a single database request. This statement ganging can result in a significant performance increase when a large number of update statements are pending.

The Statement class includes two new methods for executing batch updates:

• addBatch() permits adding a SQL update statement (INSERT, UPDATE, DELETE) to a group of statements prior to execution. Only update statements that return a simple update count can be grouped using this method.

• executeBatch() permits execution of a collection of SQL update statements as a single database request.

In order to use batch updates, an application must disable auto commit options, as follows:

...
// turn off autocommit to prevent each statement from commiting separately
con.setAutoCommit(false);

Statement stmt = con.createStatement();

stmt.addBatch("INSERT INTO employees VALUES(4671, 'James Williams')");
stmt.addBatch("INSERT INTO departments VALUES(560, 'Produce')");
stmt.addBatch("INSERT INTO emp_dept VALUES( 4671, 560)");

//submit the batch of updates for execution
int[] updateCounts = stmt.executeBatch();
con.commit();

Call clearBatch() to remove all ganged statements from a batch operation before executeBatch() is called (for example, because an error is detected).

---

Note

The JDBC 2.0 specification erroneously implies that batch updates can include Data Definition Language (DDL) statements, such as, CREATE TABLE. DDL statements do not return a simple update count, and cannot be grouped for a batch operation. Also, some databases do not allow data definition statements in transactions.

---

Creating Distributed Transactions

The JDBC 2.0 specification provides the capability for handling distributed transactions. A distributed transaction is a single transaction that applies to multiple, heterogeneous databases that may reside on separate server machines.

Distributed transaction support is already built-in to the iPlanet Application Server EJB container. If an EJB does not specify the TX_BEAN_MANAGED transaction attribute, automatic support for distributed transactions in an application is enabled.

In servlets and EJBs that specify the TX_BEAN_MANAGED transaction attribute, you can still use distributed transactions, but you must manage transactions using the JTS UserTransaction class. For example:

InitialContext ctx = null;
String dsName1 = "jdbc/SampleDS1";
String dsName2 = "jdbc/SampleDS2";
DataSource ds1 = null;
DataSource ds2 = null;

   try {
      ctx = new InitialContext();
      ds1 = (DataSource)ctx.lookup(dsName1);
      ds2 = (DataSource)ctx.lookup(dsName2);

   } catch(Exception e) {
      e.printStackTrace(System.out);
   }

UserTransaction tx = ejbContext.getUserTransaction();

tx.begin();

Connection conn1 = ds1.getConnection();
Connection conn2 = ds2.getConnection();

// do some work here

tx.commit();

In this example, ds1 and ds2 must be registered with the iPlanet Application Server as global datasources. In other words, their datasource properties files must include a ResourceMgr entry whose value must be configured at install time.

DataBase=ksample
DataSource=ksample
UserName=kdemo
PassWord=kdemo
DriverType=ORACLE_OCI
ResourceMgr=orarm

In this example, orarm must be a valid ResourceMgr entry and must be enabled to obtain a global connection successfully. In order to be a valid ResourceMgr entry, an resource manager must be listed the registry in CCS0\RESOURCEMGR, and the entry itself must have the following properties.

DatabaseType (string key)
IsEnabled (integer type)
Openstring ( string type key)
ThreadMode ( string type key)

Working with RowSet

A RowSet is an object that encapsulates a set of rows retrieved from a database or other tabular data store, such as a spreadsheet. To implement a RowSet, a program must import javax.sql, and implement the RowSet interface. RowSet extends the java.sql.ResultSet interface, permitting it to act as a JavaBean component.

Because a RowSet is a JavaBean, you can implement RowSet events and set properties on the RowSet. Furthermore, because RowSet is a ResultSet extension, you can iterate through a RowSet just as you would iterate through a ResultSet.

To fill a RowSet call the RowSet.execute() method. The execute() method uses property values to determine the datasource and retrieve data. The actual properties to set and examine depends upon the implementation of RowSet invoked.

For more information about the RowSet interface, see the JDBC 2.0 Standard Extension API Specification.

Using iASRowSet

The iPlanet Application Server provides a RowSet class called iASRowSet for convenience. iASRowSet extends ResultSet, therefore call methods are inherited from the ResultSet object. iASRowSet overrides the getMetaData() and close() methods of ResultSet. Because iASRowSet is not a driver-level class, it is easier to use than ResultSet.

The RowSet interface is fully supported except as noted in Table 8-5.

Table 8-5    RowSet Interface Support Exceptions

Method	Argument	Exception Thrown	Reason
setReadOnly()	false	SQLException	iASRowSet is already read-only.
setType()	TYPE_SCROLL_INSENSITIVE	SQLException	SCROLL_INSENSITIVE is not supported.
setConcurrency()	CONCUR_UPDATABLE	SQLException	iASRowSet is read-only.
addRowSetListener()	any	None	Not supported.
removeRowSetListener()	any	None	Not supported.
setNull()	any type name	Arguments ignored	Not supported.
setTypeMap()	java.util.Map	None	Map is a JDBC 2.0 feature that is not currently supported.

RowSetReader
iASRowSet provides a full RowSetReader class implementation.

RowSetWriter
iASRowSet is read-only, but an interface for this class is provided for future expansion. At present, its only method, writeData() throws SQLException.

RowSetInternal
This internal class is used by RowSetReader to retrieve information about the RowSet. It has a single method, getOriginalRow(), which returns the original ResultSet instead of a single row.

Using CachedRowSet

The JDBC specification provides a RowSet class called CachedRowSet. CachedRowSet permits data retrieval from a datasource, then detaches from the datasource while examining, and modifying the data. A cached row set keeps track of the original data retrieved and any data changes made by an application. If the application attempts to update the original datasource, the row set is reconnected to the datasource, and only those rows that have changed are merged back into the database.

Creating a RowSet

To create a row set in an the iPlanet Application Server application:

iASRowSet rs = new iASRowSet();

Using JNDI

All JDBC driver managers, such as the JDBC driver manager implemented in the iPlanet Application Server, must find and access a JDBC driver by looking up the driver and a JDBC URL for connecting to the database. However, a JDBC URL may not only be specific to a particular vendor's JDBC implementation, but also to a specific machine and port number. Such hard-coded dependencies make it hard to write portable applications that can easily be shifted to different JDBC implementations and machines at a later time.

JDBC 2.0 specifies using JNDI to provide a uniform, platform and JDBC vendor independent way for an application to find and access remote services over the network. In place of this hard-coded information, JNDI permits assigning a logical name to a particular datasource. Once the logical name is established, you need only modify it a single time to change the deployment and application location.

JDBC 2.0 specifies that all JDBC datasources are registered in the jdbc naming subcontext of a JNDI namespace, or in one of its child subcontexts. The JNDI namespace is hierarchical, like a file system's directory structure, so it is easy to find and nest references. A datasource is bound to a logical JNDI name. The name identifies a subcontext, jdbc, of the root context, and a logical name. In order to change the datasource, just change its entry in the JNDI namespace without having to modify the application.

For more information about JNDI, see the JDBC 2.0 Standard Extension API.