Skip to Content

Using JDBC Connection Pool in XI Message Mapping

In XI Message Mapping, many time we have needs to lookup values from SQL tables. There are 3 primary ways to execute SQL statements from Message Mapping:

  1. Develop a user-function to connect to a datasource and execute the SQL statement.
  2. Use a Receiver JDBC Communication Channel to connect to a datasource and execute the SQL statement.
  3. Use a J2EE JDBC connection pool to connect to a datasource and execute the SQL statement.

Method 1 is the most inefficient, and the least desirable. For each lookup, a connection to the database will have be made. The resources required to make a database connection maybe greater than executing the SQL statement itself. In addition, userid/password to access the datasource will have to be included in the java source code.

Method 2 provides the most flexibility to the developer, since the developer can create and configure the communication channel in the Integration Directory. However, the number of threads for the JDBC receiver communication channel is limited and may create a performance bottleneck if many scenarios are also using the same communication channel.

Method 3 is the most efficient way and does not require any interface design in the Integration Repository. The connections in the pool are shared by everyone, even non-XI applications. There is no overhead of repeated connections to the datasource. The maximum number of concurrent connections can be dynamically changed. Virtually any SQL statement can be executed. However, some preparation and setup are required.

In this blog, we will go through the procedure is setting up a Message Mapping using method 3. Please note, access to the J2EE Visual Administrator will be required.

This blog will also give 3 datasource configuration examples. One example using SQLServer, and two examples using Oracle.

There are 3 steps involved:

  1. Create/configure the JDBC datasource in the J2EE Visual Administrator.
  2. Develop a generic java class to access any datasource and execute any SQL statement.
  3. Develop a simple user-function in Message Mapping to use the java class from the previous step.

Here are the steps:

  1. Create/configure the JDBC datasource in the Visual Administrator.
    1. Login to the J2EE Visual Administrator and go to: Server 0 -> Services -> JDBC Connector

      image

    2. Add a JDBC driver.

      image

      In this example, I added 2 JDBC drivers: (The vendor-specific JDBC drivers can be downloaded from the vendor’s website. Make sure the driver version corresponds to the database version to be accessed.)

      • Oracle requires ojdbc14.jar
      • SQLServer requires msbase.jar, mssqlserver.jar and msutil.jar
    3. Create a JDBC datasource.

      image

      Following are 3 examples of datasources:

      • Oracle JDBC Version 1.x (no XA support)

        image
        Driver Name: Oracle10 (based on configuration in the previous step)
        JDBC Version: 1.x (no XA support)
        Driver Class: oracle.jdbc.driver.OracleDriver
        Database URL: jdbc:oracle:thin:@localhost:1521:orcl
        Enter valid userid and password for the database

        image

      • Oracle JDBC Version 2.0 (with XA support)

        image
        Driver Name: Oracle10 (based on configuration in the previous step)
        JDBC Version: 2.0 (with XA support)
        Object Factory: oracle.jdbc.pool.OracleDataSourceFactory
        DataSource Type: XADataSource
        XADS Classname: oracle.jdbc.xa.client.OracleXADataSource

        image
        url: jdbc:oracle:thin:@localhost:1521:orcl
        Enter valid userid and password for the database

      • SQLServer JDBC Version 1.x (no XA support)

        image
        Driver Name: SQLServer (based on configuration in the previous step)
        JDBC Version: 1.x (no XA support)
        Driver Class: com.microsoft.jdbc.sqlserver.SQLServerDriver
        Database URL: jdbc:microsoft:sqlserver://localhost:1433;databaseName=xirig
        Enter valid userid and password for the database

        image

  2. Develop a generic java class to access any datasource and execute any SQL statement.

    An example source code is below:

    Create a ZIP or JAR file and import it as an Imported Archive for mapping.

  3. Develop a simple user-function in Message Mapping to use the java class from the previous step.

    image

    With the LookUp user-function

    image

    Using the test tool, we receive the following result:

    image

    The number of rows in “table1” is 2.

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Michal Krawczyk
    Hi William,

    sorry for a dummy question
    about method 2:

    >>>>However, an XML message abiding by the JDBC receiver specification must be used

    so when you use DB lookup from message mapping:

    /people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler

    it creates an XML message that goes to
    the channel ? (just like you need to
    create on your own with RFC API)?

    Regards,
    michal

    (0) 
    1. William Li Post author
      Below is an example of user-function using method 2:
      (Note:  need to include “import com.sap.aii.mapping.lookup.*;”)
      ==================================
      Channel channel = null;
      DataBaseAccessor accessor = null;
      DataBaseResult resultSet = null;

      //Set query
      String query = “select OrderDate from  OrderHeader where OrderID = ‘” + OrderID[0] + “‘”;

      try {

           //Set channel according to Directory config
           channel = LookupService.getChannel(“Orders_Local_MSSQL”, “JDBC_MSSQL_Receiver”);

           //Set database accessor
           accessor = LookupService.getDataBaseAccessor(channel);

           //Execute query and get resultset
           resultSet = accessor.execute(query);

           for (Iterator rows = resultSet.getRows(); rows.hasNext();) {
                Map rowMap = (Map) rows.next();
                result.addValue((String) rowMap.get(“OrderDate”));
           }

      } catch (Exception e1) {
           result.addValue(e1.getMessage());
      } finally {
           try {
                if (accessor != null)
                     accessor.close();
           } catch (Exception e2) {
                result.addValue(e2.getMessage());
           }

      }

      ==========================

      (0) 
  2. Pete erickson
    Using the above blog, I am executing a J2EE JDBC connection pool to connect to a datasource and execute the SQL statement.

    The scenario works fine, but there are some threads that are failing for no reason(I am seeing that in Vis Admin) and the Failed Threads are not at all released?

    What could be the issue? how to overcome that? We are on SP14

    (0) 
  3. Dimitris Lyberopoulos
    Thank you for sharing this blog. I employed this method (DB2, jcc driver, XI3.0SP20), but get the following weird exception, which does not take place in the mapping test (only for real messages):

    Caused by: java.lang.RuntimeException: com.sap.engine.services.dbpool.exceptions.BaseSQLException raised while getting datasource connection: ResourceException in method ConnectionFactoryImpl.getConnection(): com.sap.engine.services.connector.exceptions.BaseResourceException: Cannot open resource(Managed Connection) with LocalTransaction support or add a second one in the same transaction, for component “ejbContexts/sap.com/com.sap.xi.services/SMPP_CALL_JAVA_RUNTIME3” in application “sap.com/com.sap.xi.services

    Have you any experience on this?
    Thank you!

    (0) 

Leave a Reply