Lookup’s in XI made simpler

[Former Member]

Introduction:

In an XI mapping, it is a common requirement to be able to perform data lookups. Earlier the lookup functionality was achieved using JDBC/JCO API’s in an advanced function to connect to DB/ SAP System respectively. If the lookup were to be performed with any other application system (synchronous) SOAP/ webservice, the API’s used will change accordingly. In such a case, the developer should have the knowledge of how to integration using the API’s specific to the system. Hence the Lookup code becomes System specific.

But with SP13, using Lookup API in XI, the Lookup functionality has changed and now its even simpler. The developer need not worry about to which application system is the lookup call being made.The Lookup API’s are generic in nature which allow u to perform a lookup independent of the system to which the call is being made. Another feature to be noted is, whenever a mapping program is executed on the Integration Server, the lookup API calls the required application system by using the central Adapter Engine.

Scenario:

For the given UserName and Password details, the Role of the user has to picked up from the DB. Below is the screenshot of the DB-Table that has been used in the scenario.



Fig.1 DataBase Table Structure

Sequence of Steps:

In Configuration Time, create an appropriate communication channel to the System with which the lookup is being performed. For example, if the lookup is to be performed with a Database System, then a communication channel for the DB should be created. The Advanced function (in Message Mapping) will use this channel to perform the lookup’s.



Fig.2 Communication Channel used for Lookup

In Design Time, Create all the required design time Objects to perform a Message Mapping. (Datatypes, MessageTypes, MessageMapping).



Fig.3 Message Mapping

Create a new advanced Function to perform a DB lookup.



Fig.4 Creation of a new advanced Function



Fig.4Advanced Function editor

The following is the logical flow of code used in Advanced function that performs DB lookup.

1. Construct your Query String
2. Determine the channel by specifying the Party, Service, Communication channel. Or only System, Communication channel
3. Get a SystemAccessor for the channel.
4. Execute your Query and get the resultset.
5. Loop through the resultset and get the required value. If you want to store more than 1 value, then use container and global container variables for storing them.
6. Close the System Accessor

//write your code hereString Query = " ";Channel channel = null;DataBaseAccessor accessor = null;DataBaseResult resultSet = null;// Build the Query StringQuery = "Select URole from  LoginMaster_1 where UName = '" + UName[0] + "' and UPassword = '" + Pwd[0] +"'";try{//Determine a channel, as created in the Configurationchannel = LookupService.getChannel("DB-SYSTEM-NAME","DB-CHANNEL-NAME"); //Get a system accessor for the channel. As the call is being made to an DB, an DatabaseAccessor is obtained.accessor = LookupService.getDataBaseAccessor(channel);//Execute Query and get the values in resultsetresultSet = accessor.execute(Query);for(Iterator rows = resultSet.getRows();rows.hasNext();){Map rowMap = (Map)rows.next();result.addValue((String)rowMap.get("URole"));}}catch(Exception ex){result.addValue(ex.getMessage())}finally{try{if (accessor!=null) accessor.close();}catch(Exception e){result.addValue(e.getMessage())}}

Code snippet used to perform Loopkup

Result

DB Lookup is performed when the mapping is executed.



Fig.4Output of Message Mapping

Recommended Reading

Mapping Lookups in help.sap.com gives a brief idea of how Lookup’s are performed.