Oracle9i JDBC Developer's Guide and Reference Release 1 (9.0.1) Part Number A90211-01 |
|
JDBC is used to connect to database resources. However, to include all changes to multiple databases within a transaction, you must use the JDBC connections within a JTA global transaction, which is described fully in this chapter. The process of including database SQL updates within a transaction is referred to as enlisting a database resource.
This chapter assumes that you have a working knowledge of JTA. The discussion focuses mostly on examples and explaining the differences between Sun Microsystems's JTA specification and the Oracle JTA implementation. See http://www.javasoft.com
for the Sun Microsystems JTA specification.
Transactions manage changes to multiple databases within a single application as a unit of work. That is, if you have an application that manages data within one or more databases, you can ensure that all changes in all databases are committed at the same time if they are managed within a transaction.
Transactions are described in terms of ACID properties, which are as follows:
The JTA implementation, specified by Sun Microsystems, relies heavily on the JDBC 2.0 specification and XA architecture. The result is a complex requirement on applications in order to ensure that the transaction is managed completely across all databases. Sun Microsystems's specifies Java Transaction API (JTA) 1.0.1 and JDBC 2.0 on http://www.javasoft.com
.
You should be aware of the following when using JTA within the Oracle9i environment:
Whenever your application connected to a database using JDBC or a SQL server, you were creating a transaction. However, the transaction involved only the single database and all updates made to the database were committed at the end of these changes. This is referred to as a local transaction.
A global transaction involves a complicated set of management objects--objects that track all of the objects and databases involved in the transaction. These global transaction objects--TransactionManager
and Transaction
--track all objects and resources involved in the global transaction. At the end of the transaction, the TransactionManager
and Transaction
objects ensure that all database changes are atomically committed at the same time.
Within a global transaction, you cannot execute a local transaction. If you try, the following error will be thrown:
ORA-2089
"COMMIT is not allowed in a subordinate session
."
Some SQL commands implicitly execute a local transaction. All SQL DDL statements, such as "CREATE TABLE", implicitly starts and commits a local transaction under the covers. If you are involved in a global transaction that has enlisted the database that the DDL statement is executing against, the global transaction will fail.
A transaction is said to be demarcated, which means that each transaction has a definite beginning and end point. For example, in an interactive tool such as SQL*Plus, a transaction is started with the SQL BEGIN
statements. Then, each SQL DML statement is part of the transaction. A transaction ends when a SQL COMMIT
or ROLLBACK
statement is issued.
For JTA, transactions are demarcated programmatically through the UserTransaction
object, which must be bound within the namespace. The client or server starts the transaction by invoking the UserTransaction.begin
method; it ends the transaction by invoking either the UserTransaction.commit
or UserTransaction.rollback
methods.
The originating client or object that starts the transaction must also end the transaction with a commit or rollback. If the client begins the transaction, calls out to a server object, the client must end the transaction after the invoked method returns. The invoked server object cannot end the transaction.
The following are the methods that you can use for transaction demarcation. These methods are defined within the javax.transaction.UserTransaction
interface:
public abstract void begin() throws NotSupported, SystemException;
Creates a new transaction and associates the transaction with the thread.
Exceptions:
NotSupportedException
: Thrown if the thread is already involved with a transaction. Nested transactions are not supported.
SystemException
: Thrown if an unexpected error condition occurs.
public abstract void commit() throws RollbackException, HeuristicMixedException, HeuristicRollbackException, SecurityException, IllegalStateException, SystemException;
Completes the existing transaction by saving all changes to resources involved in the transaction. The thread is disassociated from this transaction when this method finishes.
Exceptions:
RollbackException
: Thrown if any resource within the transaction could not commit successfully. All resource changes are rolled back.
HeuristicMixedException
: Thrown to indicate that some of the resources were committed; some were rolled back.
HeuristicRollbackException
: Thrown to indicate that some updates to resources involved in the transaction were rolled back.
SecurityException
: Thrown when the thread is not allowed to commit the transaction based on a security violation.
IllegalStateException
: Thrown if the current thread has not been associated with a transaction. This occurs if you try to commit a transaction that was never started.
SystemException
: Thrown if an unexpected error condition occurs.
public abstract void rollback() throws IllegalStateException, SecurityException, SystemException;
Roll back the transaction associated with the current thread.
Exceptions:
SecurityException
: Thrown when the thread is not allowed to roll back the transaction based on a security violation.
IllegalStateException
: Thrown if the current thread has not been associated with a transaction. This occurs if you try to roll back a transaction that was never started.
SystemException
: Thrown if an unexpected error condition occurs.
public abstract int getStatus() throws SystemException;
Retrieve the transaction status associated with the current thread.
Exceptions:
SystemException
: Thrown if an unexpected error condition occurs.
public abstract void setRollbackOnly() throws IllegalStateException, SystemException;
Modify the transaction associated with the current thread so that the outcome results in a rollback.
Exceptions:
IllegalStateException
: Thrown if the current thread has not been associated with a transaction. This occurs if you try to set for a roll back a transaction that was never started.
SystemException
: Thrown if an unexpected error condition occurs.
public abstract setTransactionTimeout(int seconds) throws SystemException;
Set the timeout value in seconds for the transaction associated with this current thread.
Exceptions:
Each resource, such as an Oracle database, that you want managed in the global transaction must be enlisted in that transaction. The transaction manager tracks all resources involved in the global transaction.
While there are several methods for retrieving a JDBC connection to a database, only one of these methods causes the database to be included in a JTA transaction. The following table lists the normal methods for retrieving JDBC connections:
Of these methods, only the DataSource
object can be used to include a database resource in the global transaction. You must bind any DataSource
object to be used within a JTA transaction with the -jta
option of the bindds
command. This binds an Oracle-specific JTA DataSource
within the namespace. You cannot use any other type of DataSource
for global transactions.
In order to ensure that the statements are included within a global transaction, you must do the following:
DataSource
object (OracleJTADataSource
) in the JNDI namespace. There are several types of DataSource
objects that you can bind. You must bind the JTA type in order for this database to be included in the global transaction. You must retrieve the connection within the context of a global transaction; that is, retrieve the connection after the begin
method of the UserTransaction
object.
DataSource
object from the JNDI namespace after the global transaction has started.
DataSource
object using the getConnection
method.
An object--client or server object--opens a JDBC connection to a remote database through the DataSource
object within the context of a global transaction. The client can open connections to remote databases through any client JDBC driver. Server objects can only open connections to databases through either a JDBC KPRB driver or a JDBC server-side Thin driver.
If your transaction involves more than one database, you must specify an Oracle9i database as the two-phase commit engine. See "Configuring Two-Phase Commit Engine" for more information.
One of the primary advantages for a global transaction is the number database resources managed as a single unit within the transaction. If your global transaction involves more than one database resource, you must specify a two-phase commit engine, which is an Oracle9i database designated to manage the changes to all databases within the transaction. 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.
On the other hand, if your global transaction has multiple server objects, but only a single database resource, you do not need to specify a two-phase commit engine. The two-phase commit engine is required only to synchronize the changes for multiple databases. If you have only a single database, single-phase commit can be performed by the transaction manager.
Note: Your two-phase commit engine can be any Oracle9i database. It can be the database where your server object exists, or even a database that is not involved in the transaction at all. See "Configuring Two-Phase Commit Engine" for a full explanation of the two-phase commit engine setup. |
Figure 17-1 shows two databases enlisted in the transaction and another database that is designated as the two-phase commit engine. Databases A and B are enlisted when a JDBC connection is opened, within a global transaction, to each database. The two-phase commit engine must be identified within the UserTransaction
object and database links from the two-phase commit engine to each database involved in the transaction must be created. See "Configuring Two-Phase Commit Engine" on how to perform this setup.
When the global transaction ends, the two-phase commit engine ensures that all changes made to the databases A and B are committed or rolled back simultaneously.
The following are the portions of the JTA specification that Oracle9i does not support.
Nested transactions are not supported in this release. If you attempt to begin a new transaction before committing or rolling back any existing transaction, the transaction service throws a SubtransactionsUnavailable
exception.
The transaction services supplied with this release do not interoperate with other JTA implementations.
Oracle only supports a database resource idle timeout through the UserTransaction
object. Thus, Oracle does not recommend that you use timeouts.
The following sections summarize the details for demarcating the transaction and enlisting the database in the transaction. These details are explained and demonstrated in the rest of the chapter. However, these tables provide a reference point for you.
Before you can retrieve the UserTransaction
or DataSource
bound objects from the JNDI namespace, you must provide the following before the JNDI lookup:
Table 17-2 Environment Setup For Transactional Object Retrieval
The DataSource
object is used to explicitly enlist the database in the JTA transaction. In order for the database to be correctly enlisted, the DataSource
must be bound correctly, and the retrieval mechanism can be one of three methods. These are discussed below:
Table 17-4 summarizes the single-phase commit scenario. It covers the JNDI binding requirements and the application implementation runtime requirements.
Aspect | Description |
---|---|
Binding |
|
Runtime |
Table 17-5 summarizes the two-phase commit scenario.
For most global transactions, you will need to bind at least one of the following objects in the namespace:
The bindut
command binds a UserTransaction
object in the namespace. This object is used for demarcation of global transactions.
You must bind a UserTransaction
object for both single and two-phase commit transactions through the bindut
command of the sess_sh
tool.
The options used to bind a UserTransaction
object depend on whether the transaction uses a single or two-phase commit, as described below:
Single-phase commit requires the JNDI bound name for the UserTransaction
object. You do not need to provide the address to a two-phase commit engine. For example, the following binds a UserTransaction
with the name of "/test/myUT
" that exists for a single-phase commit transaction:
bindut /test/myUT
To bind a UserTransaction
object to the name "/test/myUT
" in the namespace located on nsHost
through the sess_sh
command, execute the following:
sess_sh -service jdbc:oracle:thin:@nsHost:5521:ORCL -user SCOTT -password TIGER & bindut /test/myUT
Two-phase commit binding requires the JNDI bound name for the UserTransaction
object and the address to a two-phase commit engine. You provide a URL for the two-phase commit engine in the bindut
command, which can be either a JDBC URL or a sess_iiop
URL.
In addition, you can bind a username and password with the UserTransaction
object.
UserTransaction
, the user that retrieved the UserTransaction
will be the same user that is used to perform the commit or rollback for the two-phase commit on all involved databases.
UserTransaction
, then this is the username that the two-phase commit will be committed or rolled back with on all involved databases. The transaction will be started by the user that retrieves the UserTransaction
object; it will be completed by the user bound with the UserTransaction
object.
The username that is used to commit or rollback the two-phase commit transaction must be created on the two-phase commit engine and on each database involved in the transaction. It needs to be created so that it can open a session from the two-phase commit engine to each of the involved databases using database links. Secondly, it must be granted the CONNECT
, RESOURCE
, CREATE
SESSION
privileges to be able to connect to each of these databases. For example, if the user that is needed for completing the transaction is SCOTT, you would do the following on the two-phase commit engine and each database involved in the transaction:
CONNECT SYSTEM/MANAGER;
CREATE USER SCOTT IDENTIFIED BY SCOTT;
GRANT CONNECT, RESOURCE, CREATE SESSION TO SCOTT;
Lastly, if you bound a username and password with the UserTransaction
object, it will be using a different username to finalize the transaction than the username used to start the transaction. For this to be allowed, you must grant the FORCE
ANY
TRANSACTION
privileges on each database involved in the transaction in order for two separate users to start and stop the transaction. If SCOTT
is the username bound with the UserTransaction
object, you would need to do the following in addition to the previous grant:
GRANT FORCE ANY TRANSACTION TO SCOTT;
The following binds a UserTransaction
with the name of "/test/myUT
" and a two-phase commit engine at "2pcHost
" using a JDBC URL:
bindut /test/myUT -url jdbc:oracle:thin:@2pcHost:5521:ORCL
To bind the UserTransaction
in the namespace designating the two-phase commit engine at dbsun.mycompany.com
with a sess_iiop
URL:
bindut /test/myUT -url sess_iiop://dbsun.mycompany.com:2481:ORCL
When the transaction commits, the UserTransaction
communicates with the two-phase engine designated in the -url
option to commit all changes to all included databases. In this example, the username and password were not bound with the UserTransaction
object, so the user that retrieves the UserTransaction
object from the JNDI namespace is used to start and stop the transaction. Thus, this user must exist on all involved databases and the two-phase commit engine. The UserTransaction
tracks all databases involved in the transaction; the two-phase commit engine uses the database links for these databases to complete the transaction.
The bindds
command binds a DataSource
object in the JNDI namespace. In order to enlist any database in a global transaction--including the local database--you must bind a JTA DataSource
object to identify each database included in the transaction. There are multiple types of DataSource
objects for use with certain scenarios. However, for use with JTA transactions, you must bind a JTA DataSource
object, also known as an OracleJTADataSource
object, to identify each database included in the transaction. See the bindds
command of the sess_sh
tool in the Oracle9i Java Tools Reference for a description of other DataSource
object types.
In a single-phase commit scenario, the transaction only includes a single database in the transaction. Since no coordination for updates to multiple databases is needed, you do not need to specify a coordinator. Instead, you simply provide the JNDI bound name and the URL address information for this database within the OracleJTADataSource
object. You do not need to provide a database link for a transaction coordinator.
Use the bindds
command of the sess_sh
tool to bind an DataSource
object in the namespace. The full command is detailed in the Oracle9i Java Tools Reference. For example, the following binds an OracleJTADataSource
with the name of "/test/empDS
" that exists within a single-phase commit transaction with the bindds
command:
bindds /test/empDS -url jdbc:oracle:thin:@empHost:5521:ORCL -dstype jta
After binding the DataSource
object in the namespace, the server can enlist the database within a global transaction.
If multiple databases are to be included in the global transaction, you will need a two-phase commit engine, which is an Oracle9i database that is configured to be the transaction coordinator. Basically, the two-phase commit engine must have database links to each of the databases involved in the transaction. When the transaction ends, the transaction manager notifies the two-phase commit engine to either coordinate the commit of all changes to all involved databases or coordinate a roll back of these same changes.
In order to facilitate this coordination, you must configure the following:
OracleJTADataSource
object.
DataSource
(OracleJTADataSource
) object for each database in the transaction. You must include the following in the bindds command:
The following example binds the empDS
JTA DataSource
into the namespace with 2pcToEmp
as the database link name created on the two-phase commit engine:
% bindds /test/empDS -url jdbc:oracle:thin:@dbsun:5521:ORCL
-dstype jta -dblink 2pcToEmp.oracle.com
For JTA, client-side demarcated transactions are programmatically demarcated through the UserTransaction
interface (see "UserTransaction Interface"). A UserTransaction
object must be bound with the bindut
command into the namespace (see "Bind UserTransaction Object in the Namespace"). With client-side transaction demarcation, the client controls the transaction. The client starts a global transaction by invoking the UserTransaction
begin
method; it ends the transaction by invoking either the commit
or rollback
methods. In addition, the client must always set up an environment including a Hashtable
with authentication information and namespace location URL.
To include a remote database within the transaction from a client, you must use a DataSource
object, which has been bound in the namespace as a JTA DataSource
. Then, invoke the getConnection
method of the DataSource
object after the transaction has started, and the database is included in the global transaction. See "Enlisting Resources" for more information.
The following must occur in the client runtime to demarcate the transaction:
Hashtable
environment with the namespace address and authentication information.
UserTransaction
object from the namespace within the client logic. When you retrieve the UserTransaction
object from the client, the URL must consist of "jdbc_access://
" prefix before the JNDI name.
UserTransaction.begin()
.
UserTransaction.commit()
or UserTransaction.rollback()
.
Example 17-1 shows a client that invokes a server object and enlists a single database within the transaction.
Before starting the client, you must first bind the UserTransaction
and DataSource
objects in the JNDI namespace. See "Bind UserTransaction Object in the Namespace" and "Bind DataSource Object in the Namespace" for directions on the binding these objects.
sess_sh -service jdbc:oracle:thin:@nsHost:5521:ORCL -user SCOTT -password TIGER > bindut /test/myUT > bindds /test/DataSource/empDB -url jdbc:oracle:thin:@empHost:5521:ORCL -dstype jta
//Set up the service URL to where the UserTransaction object //is bound. Since from the client, the connection to the database //where the namespace is located can be communicated with over either //a Thin or OCI JDBC driver. This example uses a Thin JDBC driver.String namespaceURL = "jdbc:oracle:thin:@nsHost:1521:ORCL";
// lookup usertransaction object in the namespace//1.(a) Authenticate to the database.
// create InitialContext and initialize for authenticating client Hashtable env = new Hashtable (); env.put (Context.URL_PKG_PREFIXES, "oracle.aurora.jndi"); env.put (Context.SECURITY_PRINCIPAL, "SCOTT"); env.put (Context.SECURITY_CREDENTIALS, "TIGER"); env.put (Context.SECURITY_AUTHENTICATION, ServiceCtx.NON_SSL_LOGIN);//1.(b) Specify the location of the namespace where the transaction objects
// are bound.
env.put(jdbc_accessURLContextFactory.CONNECTION_URL_PROP, namespaceURL);
Context ic = new InitialContext (env);//2. Retrieve the UserTransaction object from JNDI namespace
ut = (UserTransaction)ic.lookup ("jdbc_access://test/myUT");
//3. Start the transaction
ut.begin();
//4.(a) Retrieve the DataSource
(that was previously bound with bindds in // the namespace. After retrieving the DataSource... // get a connection to a database. You need to provide authentication info // for a remote database lookup, similar to what you would do from a client. // In addition, if this was a two-phase commit transaction, you must provide // the username and password.DataSource ds = (DataSource)ic.lookup ("jdbc_access://test/empDB");
//4.(b). Get connection to the database
through DataSource.getConnection // in this case, the database requires the same username and password as // set in the environment.Connection conn = ds.getConnection ("SCOTT", "TIGER");
//5. Retrieve the server object reference
// lookup employee object in the namespace Employee employee = (Employee)ic.lookup (sessiiopURL + objectName);//6. Perform business logic.
...//7. Close the database connection.
conn.close ();//8. End the transaction
//Commit the updated value ut.commit (); }
If the database where the server is located also contains the namespace, you can do an in-session lookup. That is, you do not need to provide any environment setup. However, if the server is located on a separate machine than the namespace, you must initialize the environment exactly as specified in "JTA Client-Side Demarcation". This section assumes that the namespace and the server object exist on the same machine and demonstrates an in-session lookup of the UserTransaction
and DataSource
objects.
You do the following for a server to demarcate the transaction and enlist a database within the transaction:
UserTransaction
object within the namespace.
DataSource
object within the namespace.
If using more than one database, you will need to setup for a two-phase commit. See "Configuring Two-Phase Commit Engine" for more information.
Note:
UserTransaction
object from the namespace using in-session activate. No environment is required and no URL prefix is required. The lookup requires only the JNDI name of the bound object.
UserTransaction.begin()
.
DataSource
object from the namespace using in-session activate. No URL prefix is required. The lookup requires only the JNDI name of the bound object.
DataSource.getConnection()
. If the username and password that was used to authenticate to this server is the correct authentication information, provide no arguments. However, if the authentication requires a different username and password, provide these as input parameters to the getConnection()
method call.
UserTransaction.commit()
or UserTransaction.rollback()
.
The following example shows how a server object can enlist the local database in the transaction. The global transaction is initialized, the connection is retrieved through the DataSource.getConnection()
method, and statements are executed against the local database. These statements are committed when the global transaction is committed.
Since the UserTransaction
and DataSource
objects are bound in the namespace local to this host, you can perform an in-session activation. This means that no environment information is necessary (unless performing a two-phase commit scenario) and the lookup uses only the JNDI name, without the "jdbc_access://
" prefix.
//retrieve the initial context. InitialContext ic = new InitialContext (); // lookup the usertransaction UserTransaction ut = (UserTransaction)ic.lookup ("/test/myUT"); //start the transaction ut.begin (); // get a connection to the local database. If this was a two-phase commit // transaction, you would provide the username and password for the 2pc engine DataSource ds = (DataSource)ic.lookup ("/test/empDS"); // get connection to the local database through DataSource.getConnection Connection conn = ds.getConnection ("SCOTT", "TIGER"); //perform your SQL against the database. //prepare and execute a sql statement. retrieve the employee's selected benefits PreparedStatement ps = conn.prepareStatement ("update emp set ename = :(employee.name),
sal = :(employee.salary) where empno = :(employee.number)"); .... //do work ps.close(); } //close the connection conn.close(); // commit the transaction ut.commit (); //return the employee information. return new EmployeeInfo (name, empno, (float)salary);
As in Example 17-2, you would retrieve the JTA DataSource
from the JNDI provider, retrieve the connection, retrieve a context from that connection, and then provide the context on the SQLJ command-line.
//retrieve the initial context. InitialContext ic = new InitialContext (); // lookup the usertransaction UserTransaction ut = (UserTransaction)ic.lookup ("/test/myUT"); //start the transaction ut.begin (); // get a connection to the local database. If this was a two-phase commit // transaction, you would provide the username and password for the 2pc engine DataSource ds = (DataSource)ic.lookup ("/test/empDS"); // get connection to the local database through DataSource.getConnection Connection conn = ds.getConnection ("SCOTT", "TIGER"); //setup the context for issuing SQLJ against the database DefaultContext defCtx = new DefaultContext (conn); //issue SQL DML statemetns against the database #sql [defCtx] { update emp set ename = :(remoteEmployee.name), sal = :(remoteEmployee.salary) where empno = :(remoteEmployee.number) }; //close the connection conn.close(); // commit the transaction ut.commit (); //return the employee information. return new EmployeeInfo (name, empno, (float)salary);
When multiple databases are included in a global transaction, the changes to these resources must all be committed or rolled back at the same time. That is, when the transaction ends, the transaction manager contacts a coordinator--also known as a two-phase commit engine--to either commit or roll back all changes to all included databases. The two-phase commit engine is an Oracle9i database that is configured with the following:
In order to facilitate this coordination, you must configure the following:
CREATE
DATABASE
LINK
command) from the two-phase commit engine to each database that may be involved in the global transaction. This is necessary for the two-phase commit engine to communicate with each database at the end of the transaction. These database link names must be included when binding the JTA DataSource
(OracleJTADataSource)
object.
DataSource
(OracleJTADataSource
) object for each database in the transaction. You must include the following in the bindds
command:
Provide the fully-qualified database link name in the -dblink
option of bindds
for each individual database when binding that database's DataSource
into the namespace.
bindds /test/empDS -url jdbc:oracle:thin:@empHost:5521:ORCL
-dstype jta -dblink 2pcToEmp.oracle.com
CONNECT
, RESOURCE
, and CREATE
SESSION
privileges. If the user that completes the transaction is different from the user that starts the transaction, you also need to grant the FORCE
ANY
TRANSACTION
privilege. These privileges must be granted on all databases included in the transaction.
The decision on whether the FORCE
ANY
TRANSACTION
privilege is needed is determined by whether you bound a username and password with the UserTransaction
object.
UserTransaction
, the user that retrieved the UserTransaction
will be the same user that is used to perform the commit or rollback for the two-phase commit on all involved databases.
UserTransaction
, then this is the username that the two-phase commit will be committed or rolled back with on all involved databases. The transaction will be started by the user that retrieves the UserTransaction
object.
Both types of users must be created, so that it can open a session from the two-phase commit engine to each of the involved databases. Secondly, it must be granted the CONNECT
, RESOURCE
, CREATE
SESSION
privileges to be able to connect to each of these databases. For example, if the user that is needed for completing the transaction is SCOTT, you would do the following on the two-phase commit engine and each database involved in the transaction:
CONNECT SYSTEM/MANAGER;
CREATE USER SCOTT IDENTIFIED BY SCOTT;
GRANT CONNECT, RESOURCE, CREATE SESSION TO SCOTT;
Lastly, if you bound a username and password with the UserTransaction
object, it will be using a different username to finalize the transaction than the username used to start the transaction. For this to be allowed, you must grant the FORCE
ANY
TRANSACTION
privileges on each database involved in the transaction in order for two separate users to start and stop the transaction.
The advantage of binding a username with the UserTransaction
is that it is treated as a global user is always committing all transactions started with this UserTransaction
object. Thus, if you have more than one JTA transactions, you will only have to create one user and grant privileges to that user on all involved databases.
For example, if SCOTT
is the username bound with the UserTransaction
object, you would need to do the following in addition to the previous grant:
GRANT FORCE ANY TRANSACTION TO SCOTT;
UserTransaction
into the namespace. You must provide the two-phase commit engine's fully-qualified database address. At this point, you should decide (based on the discussion in step 3) on whether to bind it with a username and password. The following assumes a global username is bound with the UserTransaction
.
bindut /test/myUT -url sess_iiop://dbsun.mycompany.com:2481:ORCL
-user SCOTT -password TIGER
The following example shows a server object that performs an in-session activation to retrieve both the UserTransaction
and DataSource
objects that have been bound locally. The UserTransaction
was bound with the two-phase commit engine's URL, username, and password. The DataSource
objects were all bound with the proper database links.
//with the environment set, create the initial context. InitialContext ic = new InitialContext (); UserTransaction ut = (UserTransaction)ic.lookup ("/test/myUT"); //With the same username and password for the 2pc engine, // lookup the local datasource and a remote database. DataSource localDS = (DataSource)ic.lookup ("/test/localDS"); //remote lookup requires environment setup Hashtable env = new Hashtable (); env.put (Context.URL_PKG_PREFIXES, "oracle.aurora.jndi"); env.put (Context.SECURITY_PRINCIPAL, user); env.put (Context.SECURITY_CREDENTIALS, password); env.put (Context.SECURITY_AUTHENTICATION, ServiceCtx.NON_SSL_LOGIN); env.put(jdbc_accessURLContextFactory.CONNECTION_URL_PROP, namespaceURL); Context ic = new InitialContext (env); //retrieve the DataSource for the remote database DataSource remoteDS = (DataSource)ic.lookup ("jdbc_access://test/NewYorkDS"); //retrieve connections to both local and remote databases Connection localConn = localDS.getConnection (); Connection remoteConn = remoteDS.getConnection (); ... //close the connections localConn.close(); remoteConn.close(); //end the transaction ut.commit();
If you want to bind only a single DataSource
object in the namespace to be used for multiple database resources, you must do the following:
DataSource
without specifying the URL, host, port, SID, or driver type. Thus, you execute the bindds
tool with only the -dstype
jta
option, as follows:
sess_sh -service jdbc:oracle:thin:@nsHost:5521:ORCL -user SCOTT -password TIGER & bindds /test/empDS -dstype jta
DataSource
in your code. When you perform the lookup, you must cast the returned object to OracleJTADataSource
instead of DataSource
. The Oracle-specific version of the DataSource
class contains methods to set the DataSource
properties.
OracleJTADataSource
.getConnection
method as indicated in the other examples.
The following example retrieves a generically bound DataSource
from the namespace using in-session lookup and initializes all relevant fields.
//retrieve an in-session generic DataSource object OracleJTADataSource ds =
(OracleJTADataSource)ic.lookup ("java:comp/env/test/empDS"); //set all relevant properties for my database //URL is for a local database so use the KPRB URL ds.setURL ("jdbc:oracle:kprb:"); //Used in two-phase commit, so provide the fully qualified database link that //was created from the two-phase commit engine to this database ds.setDBLink("localDB.oracle.com"); //Finally, retrieve a connection to the local database using the DataSource Connection conn = ds.getConnection ();
If you are using JDBC calls in your server object to update a database, and you have an active transaction context, you should not also use JDBC to perform transaction services, by calling methods on the JDBC connection. Do not code JDBC transaction management methods. For example:
Connection conn = ... ... conn.commit(); // DO NOT DO THIS!!
Doing so will cause a SQLException
to be thrown. Instead, you must commit using the UserTransaction
object retrieved to handle the global transaction. When you commit using the JDBC connection, you are instructing a local transaction to commit, not the global transaction. When the connection is involved in a global transaction, trying to commit a local transaction within the global transaction causes an error to occur.
In the same manner, you must also avoid doing direct SQL commits or rollbacks through JDBC. Code the object to either handle transactions directly using the UserTransaction
interface.
Within a global transaction, you cannot execute a local transaction. If you try, the following error will be thrown:
ORA-2089
"COMMIT is not allowed in a subordinate session
."
Some SQL commands implicitly execute a local transaction. All SQL DDL statements, such as "CREATE TABLE", implicitly starts and commits a local transaction under the covers. If you are involved in a global transaction that has enlisted the database that the DDL statement is executing against, the global transaction will fail.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|