SAP XI/PI: Read VARBINARY data (Sender JDBC) and map it to target message – Step by step guide for Beginners

Preface:
Hi friends, It gives me immense joy to publish my first blog post after launch of new SCN. This blog is intended for beginners who want to learn how to work with JDBC adapter and for experienced professionals who have problem with setting up JDBC scenario like the one mentioned below.

Problem Statement:
Before I talk about the solution, let us know the problem first. Recently I came across a scenario where I had to read SQL Server database table using JDBC adapter in SAP PI and then map this data to the target XML. Looks simple, right? No, it was not. The twist here is that the data stored in database table is in VARBINARY format (or commonly known as BLOB format). Fortunately it was not an image or PDF but simple XML data converted to VARBINARY and stored in table. I will explain how to develop such scenario and work with VARBINARY data.

Below diagram will illustrate how the interface will function end-to-end.

I will explain below the steps required to develop this interface. It will work in 2 parts. In the first part, data is read from database table and converted to XML format (This will act as a Source XML). In this example, Source XML will be a Sales Order XML message. In the second part, XML will be mapped to target XML.

Step 1: Data Types and Service Interfaces
We will need ESR objects in the second part of the interface above. Since we do not know the actual structure of the data coming in through JDBC channel, we really cannot create any data type and message type for first part. However, we need to create a DUMMY interface for first part. You can assign any arbitrary message type to this service interface. But keep in mind that you have to create this interface as “Outbound”.

Now, create data type/message type for the second part of the interface. This structure should resemble the XML data structure that you will get after converting the VARBINARY data.

Step 2: Java mapping to convert VARBINARY to XML
Remember that the data stored in database table is actually XML data converted to VARBINARY. Conversion from VARBINARY to XML can be done using a Java Mapping. Java program will do the trick. Please go throught Java code given at the end of this blog. It is not very difficult to understand. Once you create a Java code, you will have to export it as an EAR file and import this EAR file in PI using Imported Archive. If you want to know more about Java Mapping in PI, please go through This Link.

Step 3: Graphical message mapping for “Source XML” and “Target XML”
This is the most simple part of the interface. Create a graphical mapping to map the Source XML fields to Target XML fields as per your mapping specification.

Step 4: Operation mapping:
This is very important step in this interface design. Select DUMMY interface that you created earlier as Source Operation. Under Mapping Program, there will be 2 lines. First one for Java mapping and second one will be Graphical mapping. Output of the first mapping will be passed as an input to the second mapping here. Simple concept, isn’t it?

Step 5: Configuration in Integration Directory
Now that you have created all design objects, it’s time to create configuration objects. The most important of all is the Communication Channel of type “JDBC” (I assume here that required Business Systems are already created in SLD and imported in ID). Create channel and enter details as shown in the screenshot below,

Please note that JDBC Driver string is different for different database vendors. The one I have used here is for MS SQL Server 2005 R2. Replace <DB_Server_Hostname>, <DB_Instance> and <Database_Name> with actual values of your database host name, database instance and database name.

Switch to Processing tab in the channel and specify the SELECT query to read a row from the database table.

Your communication channel is ready. Now configure Sender Agreement, Receiver Determination, Interface Determination and Receiver Agreement and you are ready to test your interface.

Step 6: Testing your interface
Start your JDBC communication channel and it will pick up the data from database table. The message will look like this in SXMB_MONI,

You now have to extract the content of XML_DATA field. Java program will do this for you. We are using SAX Parser here to parse XML document to get the content of a particular field. Check the target XML in SXMB_MONI and Bingo! Your interface is ready.

Conclusion:
The things that click here are,

• Java Mapping
• Multiple Mappings in a singel Operation Mapping

This scenario is in fact quite simple to develop, provided you have some idea about Java programming and basic knowledge of interface development in PI. Below I have given the entire Java code that you will use in Java mapping here. Use any tool of your choice, like Eclipse, to generate EAR file and import it to ESR. The only drawback of this design is that you cannot see the “Source XML”. Intially it remains in VARBINARY format and after mapping you have the target XML directly with you.

Java Source Code:

package scn_jdbc;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;

import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;

import com.sap.aii.mapping.api.AbstractTransformation;
import com.sap.aii.mapping.api.StreamTransformationException;
import com.sap.aii.mapping.api.TransformationInput;
import com.sap.aii.mapping.api.TransformationOutput;


public class VarbinaryTest extends AbstractTransformation {

     /*
      * main method will only be used during testing and never be called in PI
      * mapping
      */
     public static void main(String[] args) {

          try {
               InputStream in = new FileInputStream(new File("C:\\input.xml"));
               OutputStream out = new FileOutputStream(new File(
               "C:\\output.xml"));
               VarbinaryTest myMapping = new VarbinaryTest();
               String outputXML = myMapping.execute(in, out);

               /* Write data to file */
               try {

                    out.write(outputXML.getBytes());
                    // System.out.println(outputXML);
               } catch (Exception e) {
                    e.printStackTrace();
               }

          } catch (Exception e) {
               e.printStackTrace();
          }
     }

     /* Call execute method which will take InputStream as input */
     public void transform(TransformationInput arg0, TransformationOutput arg1)
     throws StreamTransformationException {

          try {
               getTrace().addInfo("Java Mapping Initiated");
               String outputXML = this.execute(arg0.getInputPayload()
                         .getInputStream(), arg1.getOutputPayload()
                         .getOutputStream());
               arg1.getOutputPayload().getOutputStream().write(
                         outputXML.getBytes("UTF-8"));
          } catch (Exception e) {
               e.printStackTrace();
          }
     }

     /* Decode and parse the input data */
     public String execute(InputStream in, OutputStream out)
     throws StreamTransformationException {

          /* inputHex will contain resultset XML with HEX data in XML_DATA field */
          String inputHex = convertStreamToByte(in);

          /* Parse XML to get the HEX representation of SO */
          ByteArrayInputStream ba = new ByteArrayInputStream(inputHex.getBytes());
          SAX_SO spe = new SAX_SO();
          String outputHex = spe.parseDocument(ba);
          return convertHexToString(outputHex);
     }

     /* Convert stream to bytes */
     public String convertStreamToByte(InputStream in) {
          ByteArrayOutputStream ba = new ByteArrayOutputStream();
          try {

               int xmlData;
               while ((xmlData = in.read()) > -1) {
                    ba.write(xmlData);
               }

          } catch (Exception exception) {
          }
          return ba.toString();
     }

     /* Convert Hex to String */
     public String convertHexToString(String hexString) {
          StringBuilder sb = new StringBuilder();
          StringBuilder temp = new StringBuilder();

          // Split into two characters 49, 20, 4c...
          for (int i = 0; i < hexString.length() - 1; i += 2) {

               // grab the hex in pairs
               String output = hexString.substring(i, (i + 2));
               // convert hex to decimal
               int decimal = Integer.parseInt(output, 16);
               // convert the decimal to character
               sb.append((char) decimal);

               temp.append(decimal);
          }

          return sb.toString();
     }
}

/** ************************************************************* **/
/* Class SAX_SO to parse the XML content                           */
/** ************************************************************* **/
class SAX_SO extends DefaultHandler {

     List mySO;
     private StringBuffer sb = new StringBuffer("");
     private String tempVal;

     // to maintain context
     private SalesOrder tempSO;

     public SAX_SO() {
          mySO = new ArrayList();
     }

     public String parseDocument(InputStream in) {

          // get a factory
          SAXParserFactory spf = SAXParserFactory.newInstance();
          try {

               // get a new instance of parser
               SAXParser sp = spf.newSAXParser();

               // parse the file and also register this class for call backs
               // InputSource is = new InputSource(in);
               sp.parse(in, this);

          } catch (SAXException se) {
               se.printStackTrace();
          } catch (ParserConfigurationException pce) {
               pce.printStackTrace();
          } catch (IOException ie) {
               ie.printStackTrace();
          }
          return tempSO.getXML();
     }

     /**
      * Iterate through the list and print the contents
      */
     private void printData() {

          System.out.println("No of Sales Orders '" + mySO.size() + "'.");

          Iterator it = mySO.iterator();

          try {
               while (it.hasNext()) {
                    // System.out.println(it.next().toString());

                    String XML_DATA = it.next().toString();
                    OutputStream out = new FileOutputStream(new File(
                    "C:\\test_SO.xml"));
                    out.write(XML_DATA.getBytes());
               }
          } catch (Exception e) {
          }
     }

     // Event Handlers
     public void startElement(String uri, String localName, String qName,
               Attributes attributes) throws SAXException {
          // reset
          // tempVal = "";
          tempVal = qName;
          if (qName.equalsIgnoreCase("row")) {
               // create a new instance of Sales Order
               tempSO = new SalesOrder();
          }

          System.out.println("startElement for " + qName);
     }

     public void characters(char[] ch, int start, int length)
     throws SAXException {
          if (tempVal.equals("XML_DATA")) {
               sb.append(new String(ch, start, length));
          }
          System.out.println("characters for " + tempVal);
     }

     public void endElement(String uri, String localName, String qName)
     throws SAXException {

          if (qName.equalsIgnoreCase("row")) {
               // add it to the list
               mySO.add(tempSO);
               sb = sb.delete(0, sb.capacity());

          } else if (qName.equalsIgnoreCase("XML_DATA")) {
               tempSO.setXML(sb.toString());
               // System.out.println(sb.toString());
          }

          System.out.println("endElement for " + qName);
          tempVal = qName;
     }
}

/** ************************************************************* */
/* Class for Sales Order object */
/** ************************************************************* */
class SalesOrder {

     private String xml;

     public SalesOrder() {

     }

     public SalesOrder(String xml) {
          this.xml = xml;

     }

     public String getXML() {
          return xml;
     }

     public void setXML(String xml) {
          this.xml = xml;
     }

     public String toString() {

          return getXML();
     }
}