Are there any RFC API examples?

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.

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 java.io.ByteArrayInputStream; import java.io.InputStream; import java.util.Iterator; import java.util.Map; import javax.xml.parsers.DocumentBuilder; import javax.xml.parsers.DocumentBuilderFactory; import org.w3c.dom.*; import com.sap.aii.mapping.api.AbstractTrace; import com.sap.aii.mapping.lookup.*; /** * @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 < nodetboptitem =" nodeTbOpt.appendChild(docReq.createElement(" result =" null;" channel =" LookupService.getChannel(service," accessor =" LookupService.getRfcAccessor(channel);" is =" new" payload =" LookupService.getXmlPayload(is);" result =" accessor.call(payload);" docrsp =" null;" docrsp =" builder.parse(result.getContent());" res = "" res =" docRsp.getElementsByTagName(TAG_RES).item(0).getFirstChild().getNodeValue();" channel_name = "cc_JDBC_Receiver_Lookup" valnotfound = "VALUE_NOT_FOUND" sqlstmt = "SELECT " i =" 0;"> 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) rows.next(); 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.



The code in the function is really simple, of course because all of the needed intelligence it's already written in the java class!



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.



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).



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 ;-)

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).

Enhancements

A couple of things still need to be said.
First, in the LookupHandler class Java code I've put some stuff as constants, which you might like or not. To have the receiver channel named cc__Receiver_Lookup it's a choice. If you don't like it, put it as a parameter.
Secondly, the Service name is passed to the LookupHandler method as a constant in the UDF... Of course here you may find something much smarter, like retrieving the receiver or sender name (based on your message direction) from the Map taken form the Container object... (have a look here). For instance, if you have an IDoc2File scenario, and you're storing lookup values on R/3 tables (to have users confortably editing values with SM30 ;-) you'll wanna get the Service name from the Sender, like this:

String Sender;

java.util.Map map;

// get constant map

map = container.getTransformationParameters();

Sender = (String) map.get(StreamTransformationConstants.SENDER_SERVICE);

BUG FIXED

I realized today (30/03/2006) a stupid bug that could heavily affect the percentage of succesful JDBC lookups!
The bug: you cannot - of course - build the WHERE clause of the SQL statement assuming values are always characters, and thus enclosing them with single quotes!
The solution: the code of the LookupHandler class has been updated; the JDBCLookup method has been overloaded, so that the call in the UDF described here keeps working (but with the above said limitation). If you want to get rid of this problem, you'll have to define another array in the UDF declaration, named keyTypes for instance, where for each field (search criteria) you'll set a constant, either "C" (character, so single-quoted in the WHERE clause) or "N"(numeric, so without single quotes). Always use the makeArray UDF for this purpose. Obviously, keyTypes[] must be passed to the JDBCLookup method which will then properly build the WHERE clause.
I found another minor issue - corrected as well - when retrieving the result value, which was working only for strings, but not e.g. with BigDecimal (typically PACKED table fields).

Conlcusion

Even though I know I won't probably get any feedback, if you feel you have a better idea, or ways to enhance it, please let me know. I will be pleased by helping you bulding something better (as I already did with other SDNers).