11
Data Sources

This chapter describes how to configure and use data sources in your Oracle9iAS Containers for J2EE (OC4J) application. A data source is a vendor-independent encapsulation of a connection to a database server. A data source instantiates an object that implements the javax.sql.DataSource interface.

This chapter covers the following topics:

Introduction

A data source is a Java object that implements the javax.sql.DataSource interface. Data sources offer a portable, vendor-independent method for creating JDBC connections. Data sources are factories that return JDBC connections to a database. J2EE applications use JNDI to look up DataSource objects. Each JDBC 2.0 driver provides its own implementation of a DataSource object, which can be bound into the JNDI namespace. Once bound, you can retrieve this data source object through a JNDI lookup.

Because data sources are vendor-independent, we recommend that J2EE applications retrieve connections to data servers using data sources.

Defining Data Sources

You define OC4J data sources in an XML file known as data-sources.xml.

Defining Location of the Data Source XML Configuration File

Your application can know about the data sources defined in this file only if the application.xml file knows about it. The path attribute in the <data-sources> tag in the application.xml file must contain the name and path to your data-sources.xml file, as follows:

<data-sources
  path = "data-sources.xml"
/>

The path attribute of the <data-sources> tag contains a full pathname for the data-sources.xml file. The path can be fixed, or it can be relative to where the application.xml is located. Both the application.xml and data-sources.xml files are located in $J2EE_HOME/config/application.xml. Thus, the path contains only the name of the data-sources.xml file.

Defining Data Sources

The $J2EE_HOME/config/data-sources.xml file is pre-installed with a default data source. For most uses, this default is all you will need. However, you can also add your own customized data source definitions.

The default data source is an emulated data source. You can use this data source for applications that access and update only a single data server. If you need to update more than one database, you must use a non-emulated data source. For a full discussion of emulated versus non-emulated data sources, see "Types of Data Sources" .

The following is a simple data source definition that you can modify for most applications:

<data-source
  class="com.evermind.sql.DriverManagerDataSource"
  name="OracleDS"
  location="jdbc/OracleCoreDS"
  xa-location="OracleDS"  
  ejb-location="jdbc/OracleDS"
  connection-driver="oracle.jdbc.driver.OracleDriver"
  username="scott"
  password="tiger"
  url="jdbc:oracle:thin:@localhost:5521:oracle"
  inactivity-timeout="30"
/>

The class attribute defines the type of data source you want to use.
The location, xa-location, and ejb-location attributes are JNDI names that this data source is bound to within the JNDI namespace. We recommend that you use only the ejb-location JNDI name in the JNDI lookup for retrieving this data source.
The ejb-location attribute is the JNDI name that this data source is bound to within the JNDI namespace.
The connection-driver attribute defines the type of connection you expect to be returned to you from the data source.
The URL, username, and password identify the database, its username, and password.

"Using Data Sources" describes all data source attributes.

Retrieving a Connection from a Data Source

One way to modify data in your database is to retrieve a JDBC connection and use JDBC or SQLJ statements. We recommend that you instead use data source objects in your JDBC operations.

Note:

Data sources always return logical connections.

Do the following to modify data within your database:

Retrieve the DataSource object through a JNDI lookup on the data source definition in the data-sources.xml file.

The lookup is performed on the logical name of the default data source, which is an emulated data source that is defined in the ejb-location tag in the data-sources.xml file.

You must always cast or narrow the object that JNDI returns to the DataSource, because the JNDI lookup() method returns a Java object.
Create a connection to the database represented by the DataSource object.

Once you have the connection, you can construct and execute JDBC statements against this database specified by the data source.

The following code represents the preceding steps:

Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("jdbc/OracleDS");
Connection conn = ds.getConnection();

Use the following methods of the DataSource object in your application code to retrieve a connection to your database:

getConnection();

The username and password are those defined in the data source definition.
getConnection(String username, String password);

This username and password overrides the username and password defined in the data source definition.

If the data source refers to an Oracle database, you can cast the connection object returned on the getConnection method to oracle.jdbc.OracleConnection and use all the Oracle extensions. This is shown in the following example:

oracle.jdbc.OracleConnection conn = 

  (oracle.jdbc.OracleConnection) ds.getConnection();

After you retrieve a connection, you can execute SQL statements against the database through either SQLJ or JDBC.

Note:

We strongly recommend that you restart OC4J whenever a database crashes. Because connections obtained through data sources are cached, a connection may become invalid if the database referenced by the data source crashes. This is especially true if you have set the min-connections attribute or specified a high inactivity-timeout. (See Table 11-1, "Data Source Attributes", for a discussion of these attributes.)

Types of Data Sources

There are several types of data sources. Three types are especially important to understand: emulated data sources, non-emulated data sources, and non-JTA data sources.

Emulated Data Sources-- Emulated data sources support local and global transactions. However, instead of relying on underlying database support for JTA, emulated data sources support JTA by emulating the XA API without relying on the relational database manager's XA implementation. This means that emulated data sources do not support two-phase commit operations. The pre-installed default data source is an emulated data source.
Non-Emulated Data Sources--Non-emulated data sources support local and global transactions. Non-emulated data sources rely on the underlying database implementation of JTA and XA, and so have full support for two-phase commit.
Non-JTA Data Sources--Non-JTA data sources support only local transactions; they do not support global transactions. These data sources can be provided by Oracle or by any other JDBC-compliant implementation.

Emulated Data Sources

Connections obtained from emulated data sources are extremely fast, because the connections emulate the XA API without providing full XA global transactional support. In particular, emulated data sources do not support two-phase commit. We recommend that you use emulated data sources for local transactions or when your application uses global transactions without requiring two-phase commit. For efficiency, any JNDI-retrieved connection to the an emulated data source shares the same connection with the first identified username within the same transaction.

You can use the same emulated data source to obtain connections to different databases by changing the values of url and connection-driver. The following is a definition of an emulated data source:

<data-source
  class="com.evermind.sql.DriverManagerDataSource"
  name="OracleDS"
  location="jdbc/dsLocation"
  xa-location="jdbc/xa/OracleXADS"
  ejb-location="jdbc/OracleDS"
  connection-driver="oracle.jdbc.driver.OracleDriver"
  username="scott"
  password="tiger"
  url="jdbc:oracle:thin:@localhost:5521:oracle"
  inactivity-timeout="30"
/>

When looking up a DataSource object in the JNDI namespace, use the ejb-location logical name, as follows:

Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("jdbc/OracleDS");
// This lookup could also be done as
// DataSource ds = (DataSource) ic.lookup("java:comp/env/jdbc/OracleDS");
Connection con = ds.getConnection();

This connection opens a database session for SCOTT/TIGER.

Note:

Previous releases supported the location and xa-location attributes for retrieving data source objects. These are now strongly deprecated; applications, EJBs, servlets, and JSPs should use only the JNDI name ejb-location in emulated data source definitions for retrieving the data source.

When using an emulated data source, you cannot use global transactions. The XAResource that you enlist with the transaction manager is an emulated XAResource, so the underlying database is unaware of global transactions. It provides only local transactional support. If you want to use two-phase commit in global transactions, you must use a non-emulated data source.

Retrieving multiple connections from a data source using the same username and password within a single global transaction causes the logical connections to share a single physical connection. The following code shows two connections--conn1 and conn2--that share a single physical connection. They are both retrieved off the same data source object. They also authenticate with the same username and password.

Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("jdbc/OracleCMTDS1");
Connection conn1 = ds.getConnection("scott", "tiger");
Connection conn2 = ds.getConnection("scott", "tiger");

If you provide different a different username and password for the second connection from this data source, an error condition occurs. You can avoid this problem by using the "dedicated.connection" JNDI property. This is described in "Using Different Usernames for Two Connections to a Single Data Source".

Non-Emulated Data Sources

Non-emulated data sources provide full XA and JTA global transactional support. These are the only data sources that support global two-phase commit transactions.

We recommend that you use these data sources for distributed database communications, recovery, and reliability. Non-emulated data sources share physical connections for several logical connections to the same database for the same user.

The following is an example of a non-emulated data source definition.

<data-source
  class="com.evermind.sql.OrionCMTDataSource"
  name="OracleDS"
  location="jdbc/OracleCMTDS"
  connection-driver="oracle.jdbc.driver.OracleDriver"
  username="scott"
  password="tiger"
  url="jdbc:oracle:thin:@hostname:TTC port number:DB SID"
  inactivity-timeout="30"
/>

The following are the expected attribute definitions:

The location attribute is the JNDI name that this data source is bound to within the JNDI namespace. You use the location JNDI name in the JNDI lookup for retrieving this data source.
The connection-driver attribute defines the type of connection you expect to be returned to you from the data source.
The URL, username, and password identifies the database, its username, and password.
The class attribute defines what type of data source class to bind in the namespace. For example, you can define a non-emulated data source with the com.evermind.sql.OrionCMTDataSource class, as shown above.

Non-JTA Data Sources

Non-JTA data sources provide no support for global transactions. If you use OracleDataSource, no connection pooling is available; if you use OracleConnectionCacheImpl, connection pooling is supported.

You can use any of the Oracle DataSource objects listed in the Oracle9i JDBC Developer's Guide. For example, to define a non-emulated data source with the OracleXADataSource class, you would configure the following in the data-sources.xml file:

<data-source
  class="oracle.xa.client.OracleXADataSource"
  name="OracleXADS"
  location="jdbc/OracleXADS"
  connection-driver="oracle.jdbc.driver.OracleDriver"
  username="scott"
  password="tiger"
  url="jdbc:oracle:thin:@hostname:TTC port number:DB SID"
  inactivity-timeout="30"
/>

Non-Emulated Data Sources Cannot Mix Transaction Types

When you are using a non-emulated data source, you cannot mix local and global transactions. You must use either one or the other. The following code shows an invalid mixture of local and global transactions:

Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("jdbc/OracleCMTDS1");
Connection conn1 = ds.getConnection("scott", "tiger"); 
javax.transaction.UserTransaction txn = (javax.transaction.UserTransaction)
  ic.lookup("java:comp/env/UserTransaction");
conn1.work();     // perform work on conn1 in a local transaction
// start global transaction
txn.start();
conn1.morework(); // perform work on conn1 within a global transaction ERROR!

This example mixes transaction types in a different (but also incorrect) way:

Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("jdbc/OracleCMTDS1");
Connection conn1 = ds.getConnection("scott", "tiger"); 
javax.transaction.UserTransaction txn = (javax.transaction.UserTransaction)
  ic.lookup("java:comp/env/UserTransaction");
//start global transaction
txn.start();
conn1.work();     // perform work on conn1 in a global transaction
txn.commit();
conn1.morework(); // perform work on conn1 within a local transaction ERROR!

Even though you have committed the global transaction, you are still mixing global and local transactional work within the same bean.

Mixing Data Sources

A single application may use several different types of data source. If your application mixes data sources, you should be aware of the following issues:

Only emulated and non-emulated data sources support JTA transactions. You cannot enlist connections obtained from non-JTA data sources in a JTA transaction.
Only non-emulated data sources support two-phase commit. To enlist multiple connections in a two-phase commit transaction, all the connections must use non-emulated data sources.
You cannot use both emulated data sources and non-emulated data sources in the same transaction.
If your application does not use JTA transactions, you can obtain connections from any data source.
If your application has opened a javax.transaction.UserTransaction, all future transaction work must be performed through that object. If you try to invoke the connection's rollback() or commit() methods, you will receive the SQLException "calling commit() [or rollback()] is not allowed on a container-managed transactions Connection".

Two-Phase Commits and Data Sources

Oracle's two-phase-commit coordinator is a DTC Engine that performs two phase commit with appropriate recovery. The two-phase commit engine is responsible for ensuring that when the transaction ends, all changes to all databases are either totally committed or fully rolled back. The two-phase commit engine can be one of the databases that participates in the global transaction or it can be a separate database. If multiple databases or multiple sessions in the same database participate in a transaction, then you must specify a two-phase commit coordinator. Otherwise you cannot commit the transaction.

You can specify a commit coordinator in the following ways:

You can specify one commit coordinator for all applications using the global application.xml in the J2EE_HOME/config directory.
You can override this commit coordinator for an individual application in the application's orion-application.xml.

For example:

<commit-coordinator>
    <commit-class class="com.evermind.server.OracleTwoPhaseCommitDriver" />
    <property name="datasource"
              value="jdbc/OracleCommitDS" />
     <property name="username"
               value="system" />
      <property name="password"
               value="manager" />
</commit-coordinator>

If you specify a username and password in the global application.xml, these values override the values in datasource.xml. If these values are null, then the username and password in datasource.xml are used to connect to the commit coordinator.

The username and password used to connect to the commit coordinator (for example, System) must have "force any transaction" privilege. By default, during installation, the commit-coordinator is specified in he global application.xml with username and password as null.

Each data source participating in a two-phase commit should specify dblink information in the OrionCMTDatasource data source. This dblink should be the name of the dblink created in the commit coordinator database to connect to this database.

For example, if db1 is the database for the commit coordinator and db2 and db3 are participating in the global transactions, you would create link2 and link3 in the db1 database as shown in the following example.

connect commit_user/commit_user
create database link link2 using "inst1_db2"; // link from db1 to db2
create database link link3 using "inst1_db3"; // link from db1 to db3;

Next, you would define a data source called jdbc/OracleCommitDS in application.xml:

<data-source
    class="com.evermind.sql.OrionCMTDataSource"
    name="OracleCommitDS"
    location="jdbc/OracleCommitDS"
    connection-driver="oracle.jdbc.driver.OracleDriver"
    username="system"
    password="manager"
    url="jdbc:oracle:thin:@localhost:5521:db1"
   inactivity-timeout="30"/>

Here is the data source description of db2 which participates in the global transaction. Note that link2, which was created in db1, is specified as a property here:

        <data-source
                class="com.evermind.sql.OrionCMTDataSource"
                name="OracleDB2"
                location="jdbc/OracleDB2"
                connection-driver="oracle.jdbc.driver.OracleDriver"
                username="system"
                password="manager"
                url="jdbc:oracle:thin:@localhost:5521:db2"
                inactivity-timeout="30">
               <property name="dblink"
                         value="LINK2.REGRESS.RDBMS.EXAMPLE.COM"/>
       </data-source>

Here is the data source description of db3 which participates in the global transaction. Note that link3, which is created in db1, is specified as a property here:

        <data-source
                class="com.evermind.sql.OrionCMTDataSource"
                name="OracleDB3"
                location="jdbc/OracleDB3"
                connection-driver="oracle.jdbc.driver.OracleDriver"
                username="system"
                password="manager"
                url="jdbc:oracle:thin:@localhost:5521:db3"
                inactivity-timeout="30">
               <property name="dblink"
                         value="LINK3.REGRESS.RDBMS.EXAMPLE.COM"/>
       </data-source>

Using Data Sources

The following sections describe the data sources that your application can use and how to access them:

Configuring Data Source Objects

For most purposes, you can use the data sources that are already defined in the server data-sources.xml configuration file.

To define a new data source object, use the Oracle Enterprise Manager. To find out how to use the Administrative tools, see the Oracle9iAS Containers for J2EE User's Guide. For Oracle Enterprise Manager information, see Oracle Enterprise Manager Administrator's Guide. This chapter explains how to set up and manage data sources by editing the configuration files directly.

Configuration Files

One main configuration file establishes data sources at the OC4J server level: $J2EE_HOME/config/data-sources.xml. To edit the information in this file, use the Enterprise Manager and drill down to the Data Source page. OC4J parses the data-sources.xml file when it starts, instantiates data source objects, and binds them into the server JNDI namespace. When you add a new data source specification, you must restart the OC4J server to make the new data source available for lookup.

Each application also has a separate JNDI namespace. The files web.xml, ejb-jar.xml, orion-ejb-jar.xml, and the orion-web.xml contain entries that you can use to map application JNDI names to data sources, as the next section describes.

Data Source Attributes

A data source can take many attributes. Some are required, but most are optional; the required attributes are marked below. The attributes are specified in a <data-source> tag. Table 11-1 lists the attributes and their meaning.

Table 11-1 Data Source Attributes

Attribute Name	Meaning of Value	Default Value
`class`	Required. Names the class that implements the data source. For non-emulated, the class attribute can be "`com.evermind.sql.OrionCMTDataSource`". For emulated, the class attribute should be "`com.evermind.sql.DriverManagerDataSource`".	N/A
`location`	(The JNDI logical name for the data source object. OC4J binds the class instance into the application JNDI namespace with this name. This JNDI lookup name is used for non-emulated data sources. In future releases, `ejb-location` will be the only supported attribute for JNDI lookup of emulated data sources.	N/A
`name`	The name of the data source. Must be unique within the application.	If this name is not supplied, `ejb-location` is used as the name.
`connection-driver`	The JDBC-driver class name for this data source, which is needed by some data sources that deal with `java.sql.Connection`. For most data sources, the driver should be `"oracle.jdbc.driver.OracleDriver"`.	None.
`username`	The optional name of the schema to connect to.	None.
`password`	The optional password for the schema.	None.
`URL`	The URL for database connections. Must be supplied for Oracle database connections.	None.
`xa-location`	(Deprecated) The logical name of an XA data source. This attribute is supported only for emulated data sources. In future releases, `ejb-location` will be the only supported attribute for JNDI lookup.	None.
`ejb-location`	The logical name of an EJB data source. Use this attribute if you are using JTA for single-phase commit transactions or if you are looking up emulated data sources. If you use it to retrieve the data source, you can map the returned connection to `oracle.jdbc.OracleConnection`.	None.
`inactivity-timeout`	Time (in seconds) to cache unused connections before closing them.	60 seconds
`connection-retry- interval`	The interval to wait (in seconds) before retrying a failed connection attempt.	1 second
`max-connections`	The maximum number of open connections for a pooled data source.	Depends on the data source type.
`min-connections`	The minimum number of open connections for a pooled data source. OC4J does not open these connections until `DataSource.getConnection` method is invoked.	0
`wait-timeout`	The number of seconds to wait for a free connection if the pool is used up (that is, has reached max-connections used).	60
`max-connect-attempts`	The number of times to retry making a connection. This is useful when the network is not stable or the environment is unstable for any other reason that will sometimes make connection attempts fail.	3
`property`	This element is used to specify either a database link for two-phase commit transactions (`dblink`) or a database caching scheme (`cache_scheme`).	None

Data Source Methods

You can call the following methods on a DataSource object:

getConnection();

Attempt to establish a database connection.

getConnection(String uid, String password);

Attempt to retrieve a database connection, specifying the username and password.

getLoginTimeout();

Retrieve the maximum time in seconds that this data source can wait while attempting to connect to a database

setLoginTimeout(int seconds);

Set the maximum time in seconds that this data source will wait while attempting to connect to a database.

getLogWriter();

Retrieve the log writer for this data source. Returns a java.io.Printwriter object.

setLogWriter(PrintWriter out);

Set the log writer for this data source.

Portable Data Source Lookup

When the OC4J server starts, the data sources in the data-sources.xml file in the j2ee/home/config directory are added to the OC4J JNDI tree. When you look up a data source using JNDI, you specify the JNDI lookup as follows:

DataSource ds = ic.lookup("jdbc/OracleCMTDS1");

The OC4J server looks in its own internal JNDI tree for this data source.

However, it is recommended--and much more portable--for an application to look up a data source in the application JNDI tree, using the portable java:comp/env mechanism. Place an entry pointing to the data source in the application web.xml or ejb-jar.xml files, using the <resource-ref> tag. For example:

<resource-ref>
     <res-ref-name>jdbc/OracleDS</res-ref-name>
     <res-type>javax.sql.DataSource</res-type>
     <res-auth>Container</res-auth>
</resource-ref>

where <res-ref-name> can be one of the following:

The actual JNDI name--such as "jdbc/OracleDS"--that is defined in the data-sources.xml. In this situation, no mapping is necessary. This is demonstrated by the above code example. The <res-ref-name> is the same as the JNDI name bound in the data-sources.xml file.

You would retrieve this data source without using "java:comp/env" as shown by the following JNDI lookup:
```
InitialContext ic = new InitialContext();
DataSource ds = ic.lookup("jdbc/OracleDS");
```
A logical name that is mapped to the actual JNDI name in the OC4J-specific files, orion-web.xml or orion-ejb-jar.xml. The OC4J-specific XML files then define a mapping from the logical name in the web.xml or ejb-jar.xml file to the actual JNDI name defined in the data-sources.xml file.

Example 11-1 Mapping Logical JNDI Name to Actual JNDI Name

The following demonstrates option #2 above. If you want to choose a logical name of "jdbc/OracleMappedDS" to be used within your code for the JNDI retrieval. Then you would have the following in your web.xml or ejb-jar.xml files:

<resource-ref>
     <res-ref-name>jdbc/OracleMappedDS</res-ref-name>
     <res-type>javax.sql.DataSource</res-type>
     <res-auth>Container</res-auth>
</resource-ref>

In order for the actual JNDI name to be found, you must have a <resource-ref-mapping> element that maps the "jdbc/OracleMappedDS" to the actual JNDI name in the data-sources.xml file. If we are using the default emulated data source, then the ejb-location would be defined with "jdbc/OracleDS" as the actual JNDI name. Thus, the following line would be contained in the OC4J-specific XML file:

<resource-ref-mapping name="jdbc/OracleMappedDS" location="jdbc/OracleDS" />

You can then look up the data source in the application JNDI namespace using the Java statements:

InitialContext ic = new InitialContext();
DataSource ds = ic.lookup("java:comp/env/jdbc/OracleMappedDS");

Using Oracle JDBC Extensions

To use Oracle JDBC extensions, cast the returned connection to oracle.jdbc.OracleConnection, as follows:

Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("jdbc/OracleCMTDS1");
oracle.jdbc.OracleConnection conn = 

  (oracle.jdbc.OracleConnection) ds.getConnection();

You can use any of the Oracle extensions on the returned connection, "conn".

// you can create oracle.jdbc.* objects using this connection

oracle.jdbc.Statement orclStmt =

  (oracle.jdbc.OracleStatement)conn.createStatement();

// assume table is varray_table

oracle.jdbc.OracleResultSet rs =

  orclStmt.executeQuery("SELECT * FROM " + tableName);

while (rs.next()) 

{

  oracle.sql.ARRAY array = rs.getARRAY(1);

...

}

Behavior of a Non-Emulated Data Source Object

The physical behavior of a non-emulated data source object changes depending on whether you retrieve a connection off the data source within a global transaction or not. The following discusses these differences:

Retrieving a Connection Outside a Global Transaction

If you retrieve a connection from a non-emulated data source and you are not involved in a global transaction, every getConnection method returns a logical handle. When the connection is used for work, a physical connection is created for each connection created. Thus, if you create two connections outside of a global transaction, both connections use a separate physical connection. When you close each connection, it is returned to a pool to be used by the next connection retrieval.

Retrieving a Connection Within a Global Transaction

If you retrieve a connection from a non-emulated data source and you are involved in a global JTA transaction, all physical connections retrieved from the same DataSource object by the same user within the transaction share the same physical connection.

For example, if you start a transaction and retrieve two connections from the "jdbc/OracleCMTDS1" DataSource with the "scott" user, both connections share the physical connection. In the following example, both conn1 and conn2 share the same physical connection.

Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("jdbc/OracleCMTDS1");
//start txn
txn.start();
Connection conn1 = ds.getConnection("scott", "tiger");
Connection conn2 = ds.getConnection("scott", "tiger");

However, separate physical connections are retrieved for connections retrieved from separate DataSource objects. The following example shows both conn1 and conn2 retrieved from different DataSource objects--"jdbc/OracleCMTDS1" and "jdbc/OracleCMTDS2". Both conn1 and conn2 will exist upon a separate physical connection.

Context ic = new InitialContext();
DataSource ds1 = (DataSource) ic.lookup("jdbc/OracleCMTDS1");
DataSource ds2 = (DataSource) ic.lookup("jdbc/OracleCMTDS2");
//start txn
txn.start();
Connection conn1 = ds1.getConnection();
Connection conn2 = ds2.getConnection();

Using Database Caching Schemes

You can define the database caching scheme to use within the data source definition. There are three types of caching schemes: DYNAMIC_SCHEME, FIXED_WAIT_SCHEME, and FIXED_RETURN_NULL_SCHEME. To specify a caching scheme, you specify an integer value for a <property> element named cacheScheme. The supported values are shown in Table 11-2.

Table 11-2 Database Caching Schemes

Value	Cache Scheme
1	`DYNAMIC_SCHEME`
2	`FIXED_WAIT_SCHEME`
3	`FIXED_RETURN_NULL_SCHEME`

The following example is a data source using the DYNAMIC_SCHEME.

<data-source
  class="com.evermind.sql.OrionCMTDataSource"
  name="OracleDS"
  location="jdbc/OracleCMTDS1"
  connection-driver="oracle.jdbc.driver.OracleDriver"
  username="scott"
  password="tiger"
  url="jdbc:oracle:thin:@<hostname>:<TTC port number>:<DB SID>"
  inactivity-timeout="30">
  <property name="cacheScheme" value="1" />
</data-source>

Connection Retrieval Error Conditions

The following mistakes can create an error condition:

Using Different Usernames for Two Connections to a Single Data Source

When you retrieve a connection from the a DataSource object with a username and password, this username and password is used on all subsequent connection retrievals within the same transaction. This is true for all data source types. For example, suppose an application retrieves a connection from the "jdbc/OracleCMTDS1" data source with the "scott" user. When the application retrieves a second connection from the same data source with a different username, such as "adams", the username provided is ignored. Instead, the "scott" user is used.

Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("jdbc/OracleCMTDS1");
//start txn
txn.start();
Connection conn1 = ds.getConnection("scott", "tiger"); //uses scott/tiger 
Connection conn2 = ds.getConnection("adams", "wood"); //uses scott/tiger also

Thus, you cannot authenticate using two different users to the same data source. If you try to access the tables as "adams/wood", you enter into an error condition.

Using the OCI JDBC Drivers

The examples of Oracle data source definitions in this chapter use the Oracle JDBC thin driver. However, you can use the Oracle JDBC OCI (thick) driver as well. Set the following before you start the OC4J server:

install the Oracle Client on the same machine on which OC4J is installed
set the ORACLE_HOME variable
set LD_LIBRARY_PATH (or the equivalent environment variable for your OS) to $ORACLE_HOME/lib
set TNS_ADMIN to a valid Oracle administration directory with a valid tnsnames.ora file

The URL to use in the url attribute of the <data-source> element definition can have any of these forms:

jdbc:oracle:oci8:@: this TNS entry is for a database on the same system as the client, and the client connects to the database in IPC mode
jdbc:oracle:oci8:@<TNS service name>: where the TNS service name is an entry in the instance tnsnames.ora file
jdbc:oracle:oci8:@<full_TNS_listener_description>: the complete TNS service specification, as described in the Oracle Net Administrator's Guide

Using DataDirect Drivers

When your application must connect to heterogeneous databases, use DataDirect JDBC drivers. DataDirect JDBC drivers are not meant to be used with an Oracle database but for connecting to non-Oracle databases, such as Microsoft, SQLServer, Sybase and DB2. If you want to use DataDirect drivers with OC4J, add corresponding entries for each database in the data-sources.xml file.

Please see the DataDirect documentation for information on installing the DataDirect JDBC drivers.

The following is an example of a data source entry for SQLServer. For more detailed information, see the DataDirect Connect JDBC User's Guide and Reference.

<data-source 
   class="com.evermind.sql.DriverManagerDataSource"
   name="MerantDS" 
   location="jdbc/MerantCoreSSDS" 
   xa-location="jdbc/xa/MerantSSXADS" 
   ejb-location="jdbc/MerantSSDS" 
   connection-driver="com.merant.datadirect.jdbc.sqlserver.SQLServerDriver"
   username="test" 
   password="secret" 
   url="jdbc:sqlserver//hostname:port;User=test;Password=secret"
   inactivity-timeout="30" 
 />

For a DB2 database, here is a data source configuration sample:

<data-source 
  class="com.evermind.sql.DriverManagerDataSource"
  name="MerantDS" 
  location="jdbc/MerantDB2DS" 
  xa-location="jdbc/xa/MerantDB2XADS" 
  ejb-location="jdbc/MerantDB2DS" 
  connection-driver="com.merant.datadirect.jdbc.db2.DB2Driver"
  username="test" 
  password="secret" 
  url="jdbc:sqlserver//hostname:port;LocationName=jdbc;CollectionId=default;
  inactivity-timeout="30" 
/>

For a Sybase database, here is a data source configuration sample:

<data-source 
  class="com.evermind.sql.DriverManagerDataSource"
  name="MerantDS" 
  location="jdbc/MerantCoreSybaseDS" 
  xa-location="jdbc/xa/MerantSybaseXADS" 
  ejb-location="jdbc/MerantSybaseDS" 
  connection-driver="com.merant.datadirect.jdbc.sybase.SybaseDriver"
  username="test" 
  password="secret" 
  url="jdbc:sqlserver//hostname:port;User=test;Password=secret"
  inactivity-timeout="30" 
/>

You can also use vendor-specific data sources in the class attribute directly. That is, you do not need to use an OC4J-specific data source in the class attribute.

11 Data Sources

Introduction

Defining Data Sources

Defining Location of the Data Source XML Configuration File

Defining Data Sources

Retrieving a Connection from a Data Source

Types of Data Sources

Emulated Data Sources

Non-Emulated Data Sources

Non-JTA Data Sources

Non-Emulated Data Sources Cannot Mix Transaction Types

Mixing Data Sources

Two-Phase Commits and Data Sources

Using Data Sources

Configuring Data Source Objects

Configuration Files

Data Source Attributes

Table 11-1 Data Source Attributes

Data Source Methods

Portable Data Source Lookup

Example 11-1 Mapping Logical JNDI Name to Actual JNDI Name

Using Oracle JDBC Extensions

Behavior of a Non-Emulated Data Source Object

Retrieving a Connection Outside a Global Transaction

Retrieving a Connection Within a Global Transaction

Using Database Caching Schemes

Table 11-2 Database Caching Schemes

Connection Retrieval Error Conditions

Using Different Usernames for Two Connections to a Single Data Source

Using the OCI JDBC Drivers

Using DataDirect Drivers

11
Data Sources