Skip to Content

SAP XI Lookup API: the Killer

Again on the age-old problem of value mapping in SAP XI…  This time I think I found a nice methodology, general enough to cover 95% use cases.    +Written in cooperation with my colleague and friend Matteo Franciolli+.       h3. Introduction Let’s be honest: customers just keep on asking the same things (sometimes also referred to as +requirements+ :), and most of the times architects/developers have common (dumb) answers. Once again I was asked about value mapping in XI, how it can be done, advantages, limitations and so on. But this time I didn’t answer: “Well, in my opinion there’s no good method”, which is really a bad answer, and a thought I still partially share.   I decided to turn the screw, I figure out something smarter.       h3. Literature This is not the first article on the subject… So that fortunately I’m not forced to make everything explicit… At least four pieces deserve to be quoted:     * {code:html}XI Mapping lookups RFC API{code}, by Michal +the-never-sleepin’ +Krawczyk, a good starting point, but a bit too raw for my taste.
* Lookup’s in XI made simpler  (/people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler), by Siva Maranani, dealing with DB lookup, quite a simple one.
* Use this crazy piece for any RFC Mapping Lookups  (Use this crazy piece for any RFC Mapping Lookups!), by my SDN-friend Sravya Talanki, based on RFC_READ_TABLE function module and JCo, very effective and for sure more perfomant than the Lookup API.
* Making CSV File Lookup Possible In SAP XI !!!  (Making CSV File Lookup Possible In SAP XI !!!), by Sundararamaprasad Subbaraman, which I didn’t go through honestly, but the CSV idea sounds good.
  Finally, the Lookup API JavaDoc   ( be your real bible here.     h3. So, what’s new? Given the above links, which seem to already cover any aspect of this vital and out-of-focus matter, you may be wondering what else can be said… Well, my job here is based on the following strong assumption:  +In mappings, when you need to put a converted value in the target document, you need to put one value, and in order to get it you need to provide one or more values either taken from the source document or defaulted, which will serve as search criteria.  +The goal here is to provide a simple method to perform these kind of lookups both for RFC and JDBC sources.+     +h3. The LookupHandler If you read other blogs’o’mine you probably know I’m a big fan of Netweaver Developer Studio, for a lot of reasons, but in this case mainly because I feel more confortable writing Java code in NWDS rather than in UDF editor, where you don’t have syntax check, great context hints and so on. So in NWDS you need to create a simple Java project, a package and a class. Include in your build path the aii_map_api.jar (the biggest one you can find in your XI box file system, my own being about 38 Kb on SP15… make a search!).   The code of my LookupHandler class is below, where you’ll notice I made my best with Java DOM 😉      /*   * Created on 23-mar-2006  *   * To change the template for this generated file go to   * Window>Preferences>Java>Code Generation>Code and Comments  */ package com.sic.nw.xi.mapping.udf;  import; import; import java.util.Iterator; import java.util.Map;  import javax.xml.parsers.DocumentBuilder; import javax.xml.parsers.DocumentBuilderFactory; import org.w3c.dom.*;  import; import*;  /**   * @author Ale  *   * To change the template for this generated type comment go to   * Window>Preferences>Java>Code Generation>Code and Comments  */ public class LookupHandler {       public static String RFCLookup(String table, String[] keyNames, String[] keyValues, String resultFieldName, AbstractTrace trace, String service) {            final String CHANNEL_NAME = “cc_Rfc_Receiver_Lookup”,                VALNOTFOUND = “VALUE_NOT_FOUND”,                SAPRFCNS = “urn:sap-com:document:sap:rfc:functions”,                TAG_FM = “Z_BC_RFCLOOKUP”,                TAG_QTB = “QUERY_TABLE”,                TAG_QFL = “QUERY_FIELD”,                TAG_RES = “DATA_OUT”,                TAG_OPT = “OPTIONS”,                TAG_OPT_N = “NAME”,                TAG_OPT_V = “VALUE”;            DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();           DocumentBuilder builder = null;           factory.setNamespaceAware(false);           factory.setValidating(false);            try {                builder = factory.newDocumentBuilder();           } catch (Exception e) {                trace.addWarning(“Error creating DocumentBuilder – ” + e.getMessage());                return null;           }            Document docReq = null;            try {                // Building up RFC Request Document                docReq = builder.newDocument();                Node root = docReq.appendChild(docReq.createElementNS(SAPRFCNS, TAG_FM));                root.appendChild(docReq.createElement(TAG_QTB)).appendChild(docReq.createTextNode(table));                root.appendChild(docReq.createElement(TAG_QFL)).appendChild(docReq.createTextNode(resultFieldName));                Node nodeTbOpt = root.appendChild(docReq.createElement(TAG_OPT));                for (int i = 0; i<keyNames.length; i++) {  Node nodeTbOptItem = nodeTbOpt.appendChild(docReq.createElement(“item”));  nodeTbOptItem.appendChild(docReq.createElement(TAG_OPT_N)).appendChild(docReq.createTextNode(keyNames[i]));  nodeTbOptItem.appendChild(docReq.createElement(TAG_OPT_V)).appendChild(docReq.createTextNode(keyValues[i]));  }  } catch (Exception e) {  trace.addWarning(“Error while building RFC Request – ” + e);  return null;  }   trace.addInfo(“RFC Request XML: ” + docReq.toString());   // Lookup  Payload result = null;  try {  Channel channel = LookupService.getChannel(service, CHANNEL_NAME);  RfcAccessor accessor = LookupService.getRfcAccessor(channel);  InputStream is = new ByteArrayInputStream(docReq.toString().getBytes());  XmlPayload payload = LookupService.getXmlPayload(is);  result =;  } catch (LookupException e) {  trace.addWarning(“Error during lookup – ” + e);  return null;  }   // Parsing RFC Response Document  Document docRsp = null;  try {  docRsp = builder.parse(result.getContent());  } catch (Exception e) {  trace.addWarning(“Error when parsing RFC Response – ” + e.getMessage());  return null;  }   trace.addInfo(“RFC Response XML: ” + docRsp.toString());   String res = “”;  try {  res = docRsp.getElementsByTagName(TAG_RES).item(0).getFirstChild().getNodeValue();  } catch (Exception e) {  trace.addWarning(“Result value not found in DOM – ” + e);  return VALNOTFOUND;  }   return res;  }   public static String JDBCLookup(String table, String[] keyNames, String[] keyValues, String[] keyTypes, String resultFieldName, AbstractTrace trace, String service) {    final String CHANNEL_NAME = “cc_JDBC_Receiver_Lookup”, VALNOTFOUND = “VALUE_NOT_FOUND”;   // Build of SQL Statement  String sqlStmt = “SELECT ” + resultFieldName + ” FROM ” + table + ” WHERE “;  for (int i = 0; i<keyNames.length; i++) {  if (i > 0)  sqlStmt += ” AND “;  // Keys are not types, so assume they’re all char  if (keyTypes==null) {  sqlStmt += keyNames[i] + “='” + keyValues[i] + “‘”;   } else {  if (keyTypes[i].equalsIgnoreCase(“C”))  sqlStmt += keyNames[i] + “='” + keyValues[i] + “‘”;  else  sqlStmt += keyNames[i] + “=” + keyValues[i];  }    }   trace.addInfo(“SQL Statement: ” + sqlStmt);   // Lookup  DataBaseAccessor accessor = null;  DataBaseResult dbRes = null;  try {  Channel channel = LookupService.getChannel(service, CHANNEL_NAME);  accessor = LookupService.getDataBaseAccessor(channel);  dbRes = accessor.execute(sqlStmt);  } catch (LookupException e) {  trace.addWarning(“Error during lookup – ” + e);  return null;  } finally {  if (accessor != null)  try {  accessor.close();  } catch (LookupException e1) {  trace.addWarning(“Error closing accessor – ” + e1);  }  }   trace.addInfo(“Rows retrieved from DB: ” + dbRes.getRows());   String res = “”;  try {  // Getting reponse (we just want the 1st)  Iterator rows = dbRes.getRows();  Map rowMap = (Map);  trace.addInfo(“Column class – ” + rowMap.get(resultFieldName).getClass().getName());  res = new String(rowMap.get(resultFieldName).toString());  } catch (Exception e) {  trace.addWarning(“Result value not found in resultset or Exception thrown – ” + e);  return VALNOTFOUND;  }   return res;   }    public static String JDBCLookup(String table, String[] keyNames, String[] keyValues, String resultFieldName, AbstractTrace trace, String service) {  return JDBCLookup(table, keyNames, keyValues, null, resultFieldName, trace, service);  }   }       To integrate in XI you just need to export as .jar file (remember to export source also: it’s always good for people comin’ after you to be able to look at source code at a glance!), and import it as Imported Archive in XI Repository.  Define an Advanced User Defined Function (I named it SmartLookup), with a signature as shown in the picture below.   image   The code in the function is really simple, of course because all of the +needed intelligence +it’s already written in the java class!  image   A sample usage in the mapping can be represented by the picture below.   For those of you with a basic knowledge of R/3 tables, you should easily realize what I’m looking for: the name of customer in the Company data of the Customer Master Data, given Z001 as company code and MYTOWN as city (as constants) and the customer code taken from the source document.  image   You’ll notice the use of the MakeArray UDF, whose code follows (btw, if anyone has a smarter idea to get the same result, please let me know!), which is needed to provide multiple names and values for the lookup WHERE clause (if you think this is obscure, see the ABAP function code before you despair).  image   Ok, now we’re just missing the RFC function code (as you have probably guessed from the LookupHandler source code, JDBC handling is much simpler…), which, again, is basic and highly enhanceable (sorry about italian comments 😉       | image |   LOOP AT options.  CONCATENATE ”” options-value ”” INTO options-value.  CONCATENATE clause sep options-name c_eq  options-value INTO clause separated by space.  IF sy-tabix = 1.  sep = and.  ENDIF.  ENDLOOP.   SELECT SINGLE (query_field) INTO data_out  FROM (query_table)  WHERE (clause).    |     image
You must be Logged on to comment or reply to a post.
  • Hi Alessandro,

    really good blog. Indeed i had a similar idea and did develop a wrapper as well, however as it was at a customer, there's no chance to post it her.

    Anyway some of my ideas on what you did:
    - your LookupHandler code is full of assumptions, e.g that you only want the first row within the resultset for JDBC lookup, that there is only one column to be selected etc. To make your code reusable, you should think about these assumptions. Sometimes not all of this assumptions might be valid (e.g. that we want the first row) and you might want the calling application (UDF) to decide on which assumptions are valid.
    - you use checked exceptions, reacting on what ever happened in your code and returning a simple value not found to the caller. However we might again want to have the calling application dealing with the exceptions, as there is a significant difference between a problem in the connection and a value not found, the first one might result in an abortion of the mapping, the later one might result in a default value being set.
    - some values like the channel have been set within the LookupHandler coding. Again we might want this to be defined by the caller application (UDF) to allow more flexibility.

    So these are some of my thoughts. In general your idea is really good, but it seams to come out of a particular project, so the LookupHandler is very tightly coupled to the UDF. In contrast to that, we are only preparing for future projects, developing this stuff in advance so i had to make it quite generic.

    Best regards

    • Hi Chistine,
      And thanks for your accurate (and strict) comments, which I'll go through:
      - it's explicitly said, the assumption is strong
      - exception handling is a good point; I'll figure out something better
      - again, it's said in the weblog "Enhancement" section

      Generally speaking, since I'm not an R&D guy, almost any solution is born on a project, with a clear aim of abstraction that can make it reusable enough, but not tricky.


      • Hi Alessandro,

        yes, i know you had listed some of these things, i just wanted to point out some more thought points for people who want to do something similar.
        I'm not an R&D guy either, i'm just a strong believer in reusability probably because i'm just to lazy to code the same thing twice 😉 And if you think about reusability beyond the scope of a specific project, it really becomes important to clearly separate the layers Handler and UDF and what they are doing. 


  • Hi Alessandro,

    I had the same problem and solved in a similar way. Just a few things that came on my mind when I read yours:

    You use a DOM tree to assemble the RFC call. I thought about it and finally decided to use StringBuffer and simple string operations as this seemed to provide better performance and avoids the overhead of DOM operations in this simple XML case (even though XML puritans may disagree, I found this the pragmatic approach).

    You use a handmade RFC call. I decided to use RFC_READ_TABLE (as described in an older blog using JCo instead of lookup API).

    What I like: Passing the parameters in arrays - have you ever tried to use a user-defined function with 6 or more parameters. It's really hard to hit the correct squares for the parameters ;-).

    keep the good stuff going, regards,

    • Hello Peter, and thanks for your interest.
      Regardin' DOM vs StringBuffer, I honestly feel using DOM is much more reliable, even if less performing, and I don't consider myself a puritan, maybe a DOM purist 🙂
      JCo&RFC_READ_TABLE is great in terms of performance, but JCo can't be consider anymore the solution to value mapping and RFC_READ_TABLE - as Craig stated in quoted Sravya's blog - is not released for customer use.
      The makeArray UDF is a real headache-healer 😉 yes, a good idea coming from my colleague Matteo.

      Once again, thanks for your time.


    • Hi Peter

      The first time you add "Peter & friends" or "1<2" to your document, your code will fail 🙂 Using DOM or a similar API takes care of stuff like that for you. The cost is more verbose and slightly less readable code.


      Morten Wittrock

  • Hi Alessandro,
    We had a similar scenario in one of our projects and this was the same approach that we followed for the RFC Lookup. Good to know that this is one of the better approaches to do an RFC lookup. 🙂

    One drawback of this approach though, is that we have to create the REQUEST XML MESSAGE manually. It would be better, if all we had to do was to pass the input parameters and get the output parameters instead of creating the Request XML message and parsing the reponse message.

    Hopefully, this will be possible sometime in the near future. 🙂


    • Thank you Bhavesh.
      Personally I don't see DOM handling as a big deal. In fact, the alternative is directly using JCo, closer to what you wish to have.

      Best regards,

  • Well done Alessandro,

    only a remark, you need to declare all the possible tables you'll access with the RFC in the top of the function, is right?

    For the rest I think is a good idea to standardize the coding of the lookups.


    • Hello Sergio,
      It's a long time I don't hear from your side... Hope you're doing well.
      Anyway for what concerns your comments: you don't need to declare all looked-up tables because the ABAP code doesn't use table header, but just one-field-select INTO one local variable.
      Standardization is always a good idea, and that's exactly what I tried to do here. Of course when I have applied this technique in real projects I also had a chance to enhance it, for instance by caching lookup values during the same mapping program run, just to avoid several calls for the same lookup.
      Further comments are greatly appreciated!

      Take care,

  • Alessandro,

    We have implemented a similar LookupAPI but are struggling with Caching the lookup values during an interface run.  You mentioned above that you have implemented a Caching scheme, can you give me any additional details on how you accomplished this?  Our issue is that the Lookup value is cached across multiple runs of the interface which is not what we want.  Each new run should begin with an empty Cache which is built up as the interface runs.


    • Hi Brian.<br/>Here's the code I'm using. Note that var names may differ here and there, but the main concept is to store values in the GlobalContainer, whose lifetime is each mapping.<br/><br/>Cheers,<br/>Alex<br/><br/>// Try to get it from cache (key given by: table + field + keyNames + keyValues)<br/>String cacheKey = new String(tb[0]field[0]);<br/>for (int i=0; i<keyNames.length; i+) <br/>    cacheKey += keyNames[i] + keyValues[i];<br/>theValue = (String)container.getParameter(cacheKey);<br/><br/>// Not found in cache, so go to backend<br/>if (theValue==null) {<br/><br/>[...]<br/><br/>}