javax.sql.XADataSource in SAP PI

[park_saeiam]

Dear All,

I have scenario that may need to use jdbcpool query database, i understand that I need to deploy JDBC driver and create JDBC data source with XA datasource type due to two phase commit required, everything is fine from nwa as I created but once I try execute from UDF I got an error as following this detail.

com.sap.engine.services.dbpool.cci.ConnectionFactoryImpl:service:dbpool@com.sap.engine.boot.loader.ResourceMultiParentClassLoader@5e832150@alive incompatible with interface javax.sql.XADataSource

Please refer for this info.

Database: MS SQL Server 2008

JDBC: sqljdbc.jar

SAP: PI 7.1

UDF Code: (which I got guideline from http://scn.sap.com/people/william.li/blog/2007/03/30/using-jdbc-connection-pool-in-xi-message-mappin...)

  String dataSource = "jdbc/SQLServerPI";

  String sqlStmt = "select * from ztest where id <5";

  Connection conn = null;

  XAConnection xaconn = null;

  XADataSource ds = null;

  Statement stmt = null;

  ResultSet rst = null;

  javax.naming.InitialContext ctx = null;

  try {

        ctx = new javax.naming.InitialContext();

  if (ctx == null) {

  value = "Error - No Context";

  return "1:::" + value;

  }

  }

  catch (Exception e) {

  value = e.getMessage();

  return "3:::" + value;

  }

      try{

       ds = (XADataSource) ctx.lookup(dataSource);

  if (ds == null) {

  value = "Error - No dataSource: " + dataSource;

  return "3.5:::" + value;

  }

     }

      catch (Exception e) {

  value = e.getMessage();

  return "3.5:::" + value;

  }

  try {

  xaconn = ds.getXAConnection();

  conn = xaconn.getConnection();

  if (conn != null) {

  stmt = conn.createStatement();

  rst = stmt.executeQuery(sqlStmt);

  while (rst.next()){

  value += rst.getString(1) + "," + rst.getString(2) + "," + rst.getString(3) + "," + rst.getString(4) + "\n" ;

  }

  conn.close();

  }

  }

  catch (Exception e) {

  value = "4:::" + e.getMessage();

  }

as I checked it's error at datasource lookup.

Anyone got any Idea?

Thanks and Regards

Park

[iaki_vila]

Hi Park,

The problem seems a version incompatibility. Which is your version of PI and which is your SAP Netweaver developer studio version?

Regards.

[park_saeiam]

Hi Vila,

My PI is 7.1 and I'm not use NWDS, I coding in UDF in Message Mapping.

Thanks and Regards

Park

[iaki_vila]

Hi Park,

If you check William li's requirements, he said:

JDBC Version: 1.x (no XA support)

In PI 7.1 you have a JDBC lookup yet developed, have you tried to use it?

Regards.

[park_saeiam]

Hi Vila,

Yes I already tried, it's working fine in MM testing but once I test end to end then I got error about two-phase commit as following detail below:-

ResourceException occurred in method ConnectionFactoryImpl.getConnection(): com.sap.engine.services.connector

.exceptions.BaseResourceException: The resource "SQLServerPI1" does not support two-phase commit and cannot b

e enlisted in the current transaction, because another non supporting two-phase commit resource is already en

listed for component "ejbContexts/sap.com/com.sap.xi.services/com.sap.xpi.ibrun.server.jar/SMPP_CALL_JAVA_RUN

TIME3" in application "sap.com/com.sap.xi.services". Only one of the enlisted resources is allowed to be non

two-phase commit compliant.

So I need to create as XA datasource to avoid two-phase commit issue.

Thanks and Regards

Park

[iaki_vila]

Hi Park,

Check Susanne's answer in this thread Data source connection problem in a message map... | SCN

Hope this helps.

Regards.