Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 897
  • Last Modified:

about OracleXADataSource

I can't describe situation for now.( because I don't know the system very well.)
For short, in Weblogic,
if i use datasource in the pool with oracle.jdbc.driver.OracleDriver, it works well.
if i use datasource in the pool with oracle.jdbc.xa.client.OracleXADataSource, it complains as follows;

SQL operations are not allowed with no global transaction by default for XA drivers. If the XA driver
 supports performing SQL operations with no global transaction, explicitly allow
it by setting "SupportsLocalTransaction" JDBC connection pool property to true.
 In this case, also remember to complete the local transaction before using the connection
again for global transaction, else a XAER_OUTSIDE XAException may result.
To complete a local transaction, you can either set auto commit to true or call Connection.commit() or Connection.rollback().

Give the above, I know the answer is hard to given.

What is the checkpoint I have to check?

Thanks.
0
iamjhkang
Asked:
iamjhkang
  • 11
  • 9
2 Solutions
 
vzilkaCommented:
Which WebLogic version are you using?
What are you trying to do?
0
 
iamjhkangAuthor Commented:
8.1
0
 
vzilkaCommented:
What are you trying to do when you get the exception?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
iamjhkangAuthor Commented:
you mean rollback or commit?
rollback i want.
0
 
vzilkaCommented:
Can you post a short code sample? I do not understand what happens before you get this exception
0
 
iamjhkangAuthor Commented:
   public Vector getApplicationNumberList(String applno) throws SQLException, RMConfigurationException, Exception {
        Vector value = new Vector();
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sql = "SELECT A.APPLNO, A.RSNO, A.RS_DT, A.PROC_STCD, D.CD_DESC " +
            "FROM SN_AP10 A, SN_AP01 B, SN_CO06 C, SN_CO08 D " +
            "WHERE  A.APPLNO < '40' " +
            "AND    A.APPLNO = ? " +
...
...
...
            "ORDER BY A.RS_DT, A.AMND_VERSION, A.RSNO" ;
            con = new RMDataSourceProxy().getConnection();
            con.setAutoCommit(false);
            stmt = con.prepareStatement(sql);
            stmt.setString(1, applno);
            stmt.setString(2, applno);
            rs = stmt.executeQuery();
            while (rs.next()) {
                HashMap map = new HashMap();
                map.put("APPLNO", rs.getString(1));
                map.put("RSNO", rs.getString(2));
                map.put("RS_DT", rs.getString(3));
                map.put("PROC_STCD", rs.getString(4));
                map.put("CD_DESC", rs.getString(5));
                value.addElement(map);
            }
            con.commit();
        } catch (Exception e) {
            con.rollback();
            throw new SQLException(e.getMessage());
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (con != null) {
                con.close();
            }
        }
        return value;
    }
0
 
vzilkaCommented:
Is that code written inside an EJB? Basically, you can not use XA adapters without creating a JTA transaction (using UserTransaction ut = context.lookup("UserTransaction");)
If you use EJB modify the container transaction.
If you are not using EJBs, and since you are doing the commit/rollback by hand, it seems you do not need 2-phase-commit, and don't need an XA Datasource.
0
 
iamjhkangAuthor Commented:
I have to use XA Datasource for some reasons.

you mean like this ?

public Vector getApplicationNumberList(String applno) throws SQLException, RMConfigurationException, Exception {
        Vector value = new Vector();
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sql = "SELECT A.APPLNO, A.RSNO, A.RS_DT, A.PROC_STCD, D.CD_DESC " +
            "FROM SN_AP10 A, SN_AP01 B, SN_CO06 C, SN_CO08 D " +
            "WHERE  A.APPLNO < '40' " +
            "AND    A.APPLNO = ? " +
...
...
...
            "ORDER BY A.RS_DT, A.AMND_VERSION, A.RSNO" ;

        java.util.Hashtable hp = new java.util.Hashtable();
        hp.put(javax.naming.Context.INITIAL_CONTEXT_FACTORY, conf.get("rm.remote.default.contextFactory"));
        hp.put(javax.naming.Context.PROVIDER_URL, "t3://127.0.0.1:1004");
        InitialContext ctx = new InitialContext(hp);
        UserTransaction ut = (UserTransaction)ctx.lookup("javax.transaction.UserTransaction");
        ut.begin();

            con = new RMDataSourceProxy().getConnection();
//            con.setAutoCommit(false);
            stmt = con.prepareStatement(sql);
            stmt.setString(1, applno);
            stmt.setString(2, applno);
            rs = stmt.executeQuery();
            while (rs.next()) {
                HashMap map = new HashMap();
                map.put("APPLNO", rs.getString(1));
                map.put("RSNO", rs.getString(2));
                map.put("RS_DT", rs.getString(3));
                map.put("PROC_STCD", rs.getString(4));
                map.put("CD_DESC", rs.getString(5));
                value.addElement(map);
            }
//            con.commit();
                  ut.commit();
        } catch (Exception e) {
            con.rollback();
            throw new SQLException(e.getMessage());
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (con != null) {
                con.close();
            }
        }
        return value;
    }
0
 
iamjhkangAuthor Commented:
This is not inside EJB. this is used in EJB.
0
 
vzilkaCommented:
Then:
1. You must not perform commit or rollback by hand. The UserTransaction will do it for you.
2. You do not look up UserTransaction when working with EJB. Use the <transaction-type>Container</transaction-type>
tag for your ejb (defined in ejb-jar.xml) and the <container-transaction> tag (also in ejb-jar.xml).
The EJB method should create the transaction (choose the required option) and finish it when you are done. If an exception occurs, you automatically get a rollback for all the connections you recieved.


Note - that you must not commit connections in your EJB application. The transaction engine of the EJB engine is responsible for that.
0
 
iamjhkangAuthor Commented:
Thanks a lot.

I mis-stated that "This is not inside EJB. this is used in EJB."
This is not EJB itself.
The class that contains this method USE EJB proxy. in that;

========== RMDataSourceProxy ===========
public class RMDataSourceProxy {
    private static RMDataSourceProxy singleton = null;

    public RMDataSourceProxy() {
    }

    public Connection getConnection() {
        RMDataSource dataSource = null;
        try {
            synchronized (this) {
                if (dataSource == null) {
                    dataSource = new RMDataSource();
                }
            }
            return dataSource.getConnection();
        } catch (Exception e) {
            return null;
        }
    }

    public static synchronized RMDataSourceProxy getInstance() {
        if(singleton == null) {
            singleton = new RMDataSourceProxy();
        }
        return singleton;
    }
}


============RMDataSource==========
public class RMDataSource {
    public RMDataSource() {
    }

    public Connection getConnection() throws Exception {
        try {
            RMConfiguration conf = RMConfiguration.getInstance();
            java.util.Hashtable hp = new java.util.Hashtable();
            hp.put(javax.naming.Context.INITIAL_CONTEXT_FACTORY, conf.get("rm.remote.default.contextFactory"));
            hp.put(javax.naming.Context.PROVIDER_URL, conf.get("rm.remote.default.providerUrl"));

            InitialContext ctx = new InitialContext(hp);
            DataSource ds = (DataSource)ctx.lookup(conf.get("rm.remote.default.datasource"));
            return ds.getConnection();
        } catch (RMConfigurationException e) {
            RMLog.err.println(this.getClass().getName() + ".getConnection(RMConfigurationException) : " + e.getMessage());
            throw new RMConfigurationException(e.getMessage());
        } catch (NamingException e) {
            RMLog.err.println("Naming Exception : " + e.getMessage());
            throw new NamingException(e.getMessage());
        } catch (SQLException e) {
            RMLog.err.println(this.getClass().getName() + ".getConnection(SQLException) : " + e.getMessage());
            throw new SQLException(e.getMessage());
        }
    }

    public Connection getConnection(String jndi_name) throws Exception {
        try {
            InitialContext ctx = new InitialContext();

            DataSource ds = (DataSource)ctx.lookup(jndi_name);
            return ds.getConnection();
        } catch (NamingException e) {
            RMLog.err.println("Naming Exception : " + e.getMessage());
            throw new NamingException(e.getMessage());
        } catch (SQLException e) {
            RMLog.err.println(this.getClass().getName() + ".getConnection(SQLException) : " + e.getMessage());
            throw new SQLException(e.getMessage());
        }
    }
}

I don't want to tell you that "I need the source corrected" but I can't help.

Again, thanks a million.
0
 
iamjhkangAuthor Commented:
Yes, the tag below is in the ejb-jar.xml file.
<transaction-type>Container</transaction-type>
0
 
vzilkaCommented:
Why use this code? Why not just lookup the DataSource and ask for a getConnection()?
I can't see the use of RMDataSourceProxy, and you can use the location mapping in the ejb-jar.xml to use logical names for data-sources, saving you the need for a configuration file.

But my friend, how is this relevant to the XA thing?
0
 
iamjhkangAuthor Commented:
>Why not just lookup the DataSource and ask for a getConnection()?
how?

the RMDatasourceProxy and RMDataSource is just for your information.

and I accomplished that XA problems with encompassing select and update part whith UserTransaction and UserTransaction's commit method.

and it works.

Do you have some additional materials or articles or something?

I appreciate your efforts.
0
 
vzilkaCommented:
If the code is inside the EJB, or is run from an EJB method, then:

InitialContext ic = new InitialContext();
DataSource ds = (DataSource)ic.lookup("jndiName");
Connection c = ds.getConnection();

For more info on XA transactions - (or the JTA specification - lookup in google for tutorial):
http://java.sun.com/products/jta/
0
 
iamjhkangAuthor Commented:
by the way CLOB is delt with differently?

===========
    String sql = "INSERT INTO SN_CO702 (APPLNO, RCPTNO, ERR_OCCR_ORD1, ERR_OCCR_ORD2, ERR_TPCD, PARA_INFO, " +
                 "ELECT_WRP_FUNC_TPCD, ELECT_WRP_ERR_TPCD, ERR_OCCR_DT, ERR_CONT) VALUES " +
                 "(?, ?, ?, ?, ?, ?, ?, ?, ?, EMPTY_CLOB())";
    String clob_sql = "SELECT ERR_CONT FROM SN_CO702 WHERE APPLNO=? AND RCPTNO=? AND ERR_OCCR_ORD1=? AND ERR_OCCR_ORD2=? for update ";
============

I'll finish this question shortly.
Thanks.
0
 
tyagsCommented:
Well, i don't know if this will solve your problem or not but this looks like a known issue in EJB in Weblogic.  To quote the text as it is from Weblogic documnetation:-

Some XA JDBC drivers do not support local transaction operations, which can cause an error similar to the following when optimistic concurrency is used with such a driver:

SQL operations are not allowed with no global transaction by default for XA drivers.

In other words, the error will occur when SupportsLocalTransactions="true" is specified for the JDBCConnectionPool.

This problem occurs because optimistic concurrency suspends a global transaction and does reads in a local transaction when the database is not Oracle. (When using Oracle, you can avoid this problem by explicitly setting <database-type>Oracle</database-type> in your CMP deployment descriptor.)

Workaround: Use the "RollbackLocalTxUponConnClose" on the JDBCConnection.

Reference: http://e-docs.bea.com/wls/docs81/notes/issues.html#1150533

Hope this helps.
T.
0
 
iamjhkangAuthor Commented:
Thanks.

BTW, is UserTransaction meaningful when used with XADriver?

They are supposed to work with XA and OracleDriver.
0
 
vzilkaCommented:
Yes it is. XA is part of the JTA spec, and so is UserTransaction.
It means that your DB driver can take part in the 2-phase-commit process of the UserTranasction object.
0
 
iamjhkangAuthor Commented:
Then, I have to deploy two different machines one for OracleDriver and the other for XADriver.

1. Is it possible with one source?
2. How can I identify them who is localtransaction and who is globaltransaction?

Thanks.
0
 
vzilkaCommented:
Why do you think you have to deploy in 2 machines? And I am not sure you can identify which is which.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 11
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now